link/unlink table (SQL Server in Access) in VBA

Topic

New Reply

I’ve been looking around for this and thought i’d ask the lounge. basically, i want to do strSQL = “SELECT * INTO dbo_SQLSERVERTABLE FROM ACCESSTABLE;” so as to send all records at one go to SQL Server. i don’t want to maintain ODBC on the target machines or have a linked table that exists any longer than necessary. so, is there a way to do this in VBA?

1. link into a SQL Server table (without relying on a local ODBC file)
2. run the SQL
3. drop the link

i’ve seen various solutions using tabledefs or even OPENROWSET but i got a ‘cannot find ISAM’ error on one attempt. I could do something tedious like a loop and multiple insert statements but yuck. anyhow, i’m pretty sure this is doable but a bit foggy on the road to take.

TIA

Reply | Quote

Replies

Bottom line answer is no – the only way to link to a SQL Server table is to use ODBC. You would be better off to create an import package in SQL Server and pull the data in that way. And with that approach you can schedule it to occur at specific dates and times. Of course if the Access database is on a laptop and is only connected at random times, that approach won’t work reliably. I suspect that you will need to use the approach Andrew suggests in his reply.

Reply | Quote

I know of No way to Run an Insert Into Query where the Source or the Target is SQL Server directly from Access.
You could do it with loops.

Alternatively you could create a temporay ODBC link to the SQL Server table.
Run the query against the local table and then drop the linked SQL table.

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

If you wanted you could also set the Hidden Property of the New Local ODBC table so it would not be seen.
Another way to make it not visible is to call it UsysTableName (unless Hidden Objects are on permanent view.

Reply | Quote

I know of No way to Run an Insert Into Query where the Source or the Target is SQL Server directly from Access.
You could do it with loops.

Alternatively you could create a temporay ODBC link to the SQL Server table.
Run the query against the local table and then drop the linked SQL table.

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

If you wanted you could also set the Hidden Property of the New Local ODBC table so it would not be seen.
Another way to make it not visible is to call it UsysTableName (unless Hidden Objects are on permanent view.

you can definately run insert into from Access to SQL Server when the table you are doing the INSERT INTO is linked:
strSQL = “SELECT * INTO FROM dbo_ WHERE OWNERSHIP_VENDOR_NO = ‘” & VendorNumber & “‘ AND LVL_BEG_DT BETWEEN #” & FromDate & “# AND #” & ToDate & “#;”
DoCmd.RunSQL strSQL

A very nice thing as you don’t need to loop. It’s a set-based operation. I hope the temporary ODBC link/unlink provides the same functionality – will try it.

Thanks!

Reply | Quote