Query SQL Server Database

[Conor]

I am attempting to query an Sql server and to copy the
results of the query into a table in the current access
database programmatically using ADO. The SQL database is
opened using its DSN.

I have no problem opening or connecting to the SQL
database, I simply do not know how to transfer the
recordset resulting from the query into a table in the
current access database. I have tried

INSERT INTO [Access_Table] SELECT * FROM
[Sql_database_table]

however this does not work. I would be grateful for any
help.

Thanking you in advance

 

[SA]

Conor:

With ADO, you need to use what is called a Command Object to do this, rather
than using a Recordset object. Here's how it would look:

Function SimpleCommand()
Dim objCommand As New ADODB.Command
With objCommand
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdText
.CommandText = "INSERT INTO tblCustomers SELECT dbo_Customers.* FROM
dbo_Customers"
.Prepared = True
.Execute , , adExecuteNoRecords
End With
End Function

 

[Randy Harris]

Steve, I'm trying to understand this as well. Perhaps you could provide a
bit more explanation? In the example you provided, how is it determined
that dbo_Customers is in the SQL Server DB, not the local one?

Conor:

With ADO, you need to use what is called a Command Object to do this, rather
than using a Recordset object. Here's how it would look:

Function SimpleCommand()
Dim objCommand As New ADODB.Command
With objCommand
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdText
.CommandText = "INSERT INTO tblCustomers SELECT dbo_Customers.* FROM
dbo_Customers"
.Prepared = True
.Execute , , adExecuteNoRecords
End With
End Function

---------
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

I am attempting to query an Sql server and to copy the
results of the query into a table in the current access
database programmatically using ADO. The SQL database is
opened using its DSN.

I have no problem opening or connecting to the SQL
database, I simply do not know how to transfer the
recordset resulting from the query into a table in the
current access database. I have tried

INSERT INTO [Access_Table] SELECT * FROM
[Sql_database_table]

however this does not work. I would be grateful for any
help.

Thanking you in advance