Linked table no longer linked table

Topic

New Reply

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!

Reply | Quote

Replies

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

Reply | Quote

thanks – that did it.

Reply | Quote