Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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 <linked table from SQL server> FROM <Local Access table>;"
    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!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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


  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thanks - that did it.

Posting Permissions

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