Insert data from SQL table into Access table

[OldEnough]

New to SQL Server (running SQL Server Express 2005) would like to pull data
from server table to local table. Databases are not linked.

latest attempt below

GetSQLServerName() and GetMainSQLdb() functions identify server and
database from a config file

Dim DB As DAO.Database
Set DB = CurrentDb
Dim oCmd As Command
Dim Mystr as string
Mystr = CurrentDb.Name
Dim oRS As New adodb.Recordset
Dim cn As New adodb.Connection
Dim strSQl As String
Dim connectStr As String

connectStr = "Provider=SQLNCLI10;Server=" & GetSQLServerName() &
";Database=" & GetMainSQLdb() & ";Trusted_Connection=yes;"

cn.Open connectStr

strSQl = "INSERT INTO tblEmployees IN '" & Mystr & "' SELECT
tblEmployees.* FROM tblEmployees;"

Set oCmd = New Command

With oCmd
.CommandText = strSQl
.CommandType = adCmdText
.ActiveConnection = cn
Set oRS = .Execute
End With

With access provider and connection string - between two access databases
this works. With SQL provider it gives an "Error near keyword IN'

All suggestions will be gratefully accepted.

 

[Bob Barrows]

New to SQL Server (running SQL Server Express 2005) would like to
pull data from server table to local table. Databases are not linked.

latest attempt below

GetSQLServerName() and GetMainSQLdb() functions identify server and
database from a config file

Dim DB As DAO.Database
Set DB = CurrentDb
Dim oCmd As Command
Dim Mystr as string
Mystr = CurrentDb.Name
Dim oRS As New adodb.Recordset
Dim cn As New adodb.Connection
Dim strSQl As String
Dim connectStr As String

connectStr = "Provider=SQLNCLI10;Server=" & GetSQLServerName() &
";Database=" & GetMainSQLdb() & ";Trusted_Connection=yes;"

cn.Open connectStr

strSQl = "INSERT INTO tblEmployees IN '" & Mystr & "' SELECT
tblEmployees.* FROM tblEmployees;"

Set oCmd = New Command

With oCmd
.CommandText = strSQl
.CommandType = adCmdText
.ActiveConnection = cn
Set oRS = .Execute
End With

With access provider and connection string - between two access
databases this works. With SQL provider it gives an "Error near
keyword IN'

That "IN <database>" syntax is ONLY understood by the Jet database
engine, so, now that you know that fact, it should be obvious that you
can't expect the SQL Server provider to accept it. You need to connect
to the Access database and modify the statement to do

insert into localtable
select from remotetable in <details to connect to remote database>

It seems to me that you are going to a lot of trouble to achieve what
DoCmd.TransferDatabase will do for you far more efficiently, and with a
single command to boot ...

DoCmd.TransferDatabase acImport,"ODBC Database", _
"ODBC;DSN=DataSource1;UID=User2;PWD=www;" _
DATABASE=pubs", acTable, "tblEmployees", "tblEmployees"

 

[OldEnough]

Thanks for your prompt answer. I am sorry if my question might be foolish,
but the change of syntax from

"INSERT INTO tblEmployees IN mylocaldb SELECT tblEmployees.* FROM
tblEmployees;"

using SQL privovider for connection string

to

insert into localtable select from remotetable in <details to connect to
remote database>

will be recognized by SQL provider? or is this a workable approach for using
access provider to connect to SQL database? In second case not sure of
correct syntax for 'details to connect to remote database'. SQL provider
requires name of server and name of database but access requires db location
and filename. These things are no doubt self-evident to you, but confusing to
me.

The transferdatabase approach does seem more efficient in specific instance.
Thank you. I will look carefully at this.

 

[Bob Barrows]

Thanks for your prompt answer. I am sorry if my question might be
foolish, but the change of syntax from

"INSERT INTO tblEmployees IN mylocaldb SELECT tblEmployees.* FROM
tblEmployees;"

using SQL privovider for connection string

to

insert into localtable select from remotetable in <details to connect
to remote database>

will be recognized by SQL provider?

No, of course not. It's a JetSQL-only construct. That is why I said you
needed to connect to the Access database in order to use it.

or is this a workable approach
for using access provider to connect to SQL database? In second case
not sure of correct syntax for 'details to connect to remote
database'. SQL provider requires name of server and name of database
but access requires db location and filename. These things are no
doubt self-evident to you, but confusing to me.

The best way to figure it out is to create a linked table to the SQL
database. Then use a DAO tabledef to look at the Connect property.

The transferdatabase approach does seem more efficient in specific
instance. Thank you. I will look carefully at this.

Yeah, I really wouldn't waste any more time with the IN <remote database>
approach.