To Paul Clement

[Jason]

THANK YOU!!! You responded to my post yesterday regarding getting data from
SQL Server to Access...worked like a charm, *exactly* what I was looking
for, 3 or 4 lines of code! Perfect!

Can you point me toward any links where I can explore that further? I don't
even know what you call it; it's almost like a dynamic linked server.

Thanks again.

Here's what you posted:

Not sure whether you are working with an existing Access table but the
following uses SQL to import
into a new table:

Function ImportSQLServerToAccess() As Boolean

Dim AccessConn As New
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;"
& _
"Data Source=e:\My Documents\db1.mdb")

AccessConn.Open()

Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT *
INTO Orders FROM [Orders]
IN '' [ODBC;Driver={SQL
Server};Server=(local);Database=Northwind;Trusted_Connection=yes];",
AccessConn)

AccessCommand.ExecuteNonQuery()
AccessConn.Close()

End Function

 

[Paul Clement]

¤ THANK YOU!!! You responded to my post yesterday regarding getting data from
¤ SQL Server to Access...worked like a charm, *exactly* what I was looking
¤ for, 3 or 4 lines of code! Perfect!
¤
¤ Can you point me toward any links where I can explore that further? I don't
¤ even know what you call it; it's almost like a dynamic linked server.
¤

I stumbled across the 'IN' keyword accidentally while trying to create import/export code between
different data sources without using an Access database. There isn't a specific resource (that I'm
aware of) that documents the syntax. In any event, much of it is based upon the connection strings
for various data sources. For the posted code I just grabbed the ODBC DSN-less connection string for
SQL Server that used a trusted connection.

http://www.able-consulting.com/ADO_Conn.htm

If you have a specific question concerning syntax, let me know and I'll try to answer the question.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)