Write ADO recordset into Access table 1

[pedrox]

I'm trying to get the results of a query on a SQL database to write into a table in my local Access database.

In the past, I've got by with slightly kludgy macros using "send-keys" to rewrite pass-through queries on the fly, but this is proving less than robust in Citrix environments, forcing me to start learning VBA/ADO:

So far, I've figured out how to use ADO to set the connection to SQL, and can create a table in the SQL database with the results I want in it... but struggling to find a way to use the recordset to create a table locally in my Access database.

 

[HarleyQuinn]

Hi,

Assuming that you have some knowledge of SQL statements then you can use them (in conjunction with the ADODB Connection's .Execute method) to achieve what you are after.

You can use a CREATE TABLE statement to create the access table and then you could use an INSERT statement, loop through the recordset you returned from the SQL Database (you can probably skip the creating table part on the SQL database unless you specifically need that table created).

It would take a while to fully explain the processes involved here so I would recommend doing a search on this forum for:
ADO CREATE TABLE , ADO INSERT and ADO .Execute
That turns up several well documented examples to get you on the way with this.

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[pedrox]

Thanks for your help
It was a struggle but I got there in the end
(Not sure if this is the best way, but seems to work)

'Define and open connection to SQL
Dim conSQL As ADODB.Connection
Set conSQL = New ADODB.Connection
conSQL.Open "Driver=SQL Server; Server=xxxxxx; Database=xxxxxx; UserID=xxxxxx; Password=xxxxxx"

'Define SQL command
Dim cmdSQL As ADODB.Command
Set cmdSQL = New ADODB.Command
cmdSQL.ActiveConnection = conSQL
cmdSQL.CommandText = "SELECT comp_id, cust_id, val_trans, trans_date FROM trans(nolock) WHERE cust_id =" & Me!txtCustomer
cmdSQL.CommandType = adCmdText

'Define and open recordset returned from SQL
Dim rsSQL As ADODB.Recordset
Set rsSQL = New ADODB.Recordset
rsSQL.Open cmdSQL

'Define connection to the local Access Database
Dim conLocal As ADODB.Connection
Set conLocal = CurrentProject.Connection

'Define Local command 01 and execute it - clear down table1
Dim cmdLocal01 As ADODB.Command
Set cmdLocal01 = New ADODB.Command
cmdLocal01.ActiveConnection = conLocal
cmdLocal01.CommandText = "Delete from Table1"
cmdLocal01.CommandType = adCmdText
cmdLocal01.Execute

'Define Local command 02 and execute it - create empty local recordset
Dim cmdLocal02 As ADODB.Command
Set cmdLocal02 = New ADODB.Command
cmdLocal02.ActiveConnection = conLocal
cmdLocal02.CommandText = "select * from Table1"
Dim rsLocal As ADODB.Recordset
Set rsLocal = New ADODB.Recordset
rsLocal.Open cmdLocal02, , adOpenDynamic, adLockOptimistic

' Loop around the SQL recordset to populate the local recordset and update Table1
Dim intCount As Integer
Do While Not rsSQL.EOF
rsLocal.AddNew
    For intCount = 0 To 3
        rsLocal.Fields(intCount) = rsSQL.Fields(intCount)
    Next intCount
    rsLocal.Update
    rsSQL.MoveNext
Loop

'Clean up
Set conSQL = Nothing
Set conLocal = Nothing
Set cmdSQL = Nothing
Set cmdLocal01 = Nothing
Set cmdLocal02 = Nothing
Set rsSQL = Nothing
Set rsLocal = Nothing

 

[hwkranger]

Well.. now that you're finished...

I'll say you could have just done a make table...

Or -- If you're skilled with EM, you could have done a DTS Package

This looping through records to transfer data is for the birds..........

There's a reason there's a INSERT statment in SQL. It's meant to handle not just 1 record, but if you need it to -- an entire table.

Your fix obviously works....... But now you have my 2 cents

Randall Vollen
National City Bank Corp.

 

[pedrox]

Dunno...You may be right, but then again not sure, it may be I didn't explain my problem too well

I would have no problem with using INSERT to create a table in the main SQL database, but actually only want to SELECT some records from it, and INSERT them into a local Access table.

So the issue I had was that the SELECT part of the process needed one ADO connection and the INSERT part a different one - I don't see how you get past this without looping the recordset.

(I'm familiar with SQL - it's the ADO stuff that's new for me: looping through recordsets is a bit alien to my way of thinking too)

your 2 cents very welcome by the way (though i need to check the exchange rate)

 

[cmmrfrds]

There are a couple of Functions in SQL Server where you can combine other databases with sql server tables.
See. OpenRowset and OpenQuery

Here is an example from the Northwind database of select from sql server and inserting into an Access Table. I made another table definition in Northwind.mdb for shippers called newshippers and then inserted the records from the sql server northwind database. You should be able to run this code on your system if you have northwind installed on both locations. This is just an example of 1 way, you can also do across platform joins with multiple ADO connections.

Public Function rowsetInsert()
'-- set reference to ADO library
'-- Microsoft ActiveX data objects 2.6 library also needed for ADO

Dim cn As New ADODB.Connection, sqlstring As String
Dim rs As New ADODB.Recordset, connString As String
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=northwind;" & _
"Data Source=localhost;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString

sqlstring = "Insert Into "
sqlstring = sqlstring & "OPENROWSET('Microsoft.Jet.OLEDB.4.0', "
sqlstring = sqlstring & "'c:\program files\microsoft office\office11\Samples\northwind.mdb';'admin';, NewShippers) "
sqlstring = sqlstring & "(CompanyName, Phone) "
sqlstring = sqlstring & "Select CompanyName, Phone From Shippers"

rs.Open sqlstring, cn, adOpenForwardOnly, adLockReadOnly

End Function