Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2012
    Posts
    1

    Insert from Ms Access to SQL Server

    I'm trying to insert records into an SQL Server table that is linked in MS Access. Nothing happens, no error and no record inserted. Any idea what I'm doing wrong?

    Dim db As Database
    Set db = CurrentDb

    db.Execute "INSERT INTO [ODBC;DRIVER=SQL Server;SERVER=10.0.0.5;DATABASE=dev_Test].LOCATIONS ( Location,Loc_Name ) SELECT Location,Loc_Name FROM LOCATIONS Where Location = 'TEST'"

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,569
    Lots of things could be wrong.
    Your query could be incorrect.
    Your ODBC connection could be wired to a different server than you think.
    There are other possibilities as well.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    May 2008
    Posts
    97
    Can you just export the data as CSV then import that into MSSQL?

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,275
    What I do is
    The long way

    Create a new msaccess file

    Link the SQL into new msaccess (make shour you seeing right data)

    Then link msaccess tables to the new msaccess (make shour you seeing right data)

    Then you can write the up query in there
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE

  5. #5
    Join Date
    May 2010
    Posts
    52
    is this a one of thing? if yes use the import wizard to import the data from Access to SQL.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •