• Linked table no longer linked table

    Author
    Topic
    #463901

    I need to run a update of records from Access to SQL server, and this code appears to work:

    Code:
    Function LinkSQLRS()
    
    Dim tdf As DAO.TableDef
    
    Const CONSQLSVR = "ODBC;Driver=SQL SERVER;SERVER=YourServerName;UID=YourUserID;PWD=YourPassword;Database=YourSQLDatabase;"
    
    Set tdf = CurrentDb.CreateTableDef("tmpLocalTableName")
    tdf.Connect = CONSQLSVR
    tdf.SourceTableName = "SQLTableName"
    CurrentDb.TableDefs.Append tdf
    
    'Do You SQL Here
    'Using the Local ODBC Table Name
    'etc etc........
    
    
    'Kill the temporary linked SQL ODBC Table
    CurrentDb.TableDefs.Delete ("tmpLocalTableName")
    
    End Function
    

    However, when I do this:

    Code:
    strSQL = "SELECT * INTO  FROM ;"
    DoCmd.RunSQL strSQL

    the table that was displaying as Linked gets converted to a local table. Doing it the other way (insert into local Access table from linked SQL Server table) doesn’t do that. I have a PK/not null column specified in the linked table in SQL Server.

    i suppose there’s something about SELECT INTO that is causing this to happen, will research, but thought I’d put this out there for any comments, etc.

    Thanks!

    Viewing 1 reply thread
    Author
    Replies
    • #1185665

      SELECT INTO is the SQL for a make-table query. It will create a new table, replacing an existing one of the same name; this new table will be a local table, of course.

      To add records to the linked table that you created, use the SQL for an append query:

      strSQL = “INSERT INTO linkedtable SELECT * FROM localtable”

      If you want to make sure that the linked table is empty, precede this by executing a delete query:

      strSQL = “DELETE * FROM linkedtable”
      DoCmd.RunSQL strSQL

    • #1185671

      thanks – that did it.

    Viewing 1 reply thread
    Reply To: Reply #1185671 in Linked table no longer linked table

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




    Cancel