dsn-less connection copy data

[Ken]

How can I copy data from SQL to MS Access using dsn-less connection
and not using SQL DTS? I have this but am stuck. I just want to do
an export but can't figure out how to do a "select into". It pastes
the data in SQL instead of access. The below code will cycle but I
will have to create a table to place the data first. Is there an
easier way.

Sub getTables()
Dim cnnSQL As ADODB.Connectio n
Dim cnnMSA As ADODB.Connectio n
Dim strSQL As String
Dim rstSQL As ADODB.Recordset
Dim rstMSA As ADODB.Recordset
Dim strServer As String
Dim strDB As String
Dim i As Long
Dim cnn As ADODB.Connectio n

strServer = "northwind"
strDB = "pubs"

Set cnnSQL = New ADODB.Connectio n
Set rstSQL = New ADODB.Recordset
Set cnnMSA = CurrentProject. Connection
Set rstMSA = New ADODB.Recordset
cnnSQL.Open "Provider=sqlol edb;Data Source='" & strServer & "';Initial
Catalog='" & strDB & "';Integrat ed Security=SSPI"
strSQL = "select * from customers"

Set rstMSA = cnnSQL.Execute( strSQL)
Do While Not rstMSA.EOF
For i = 0 To rstMSA.Fields.C ount - 1
rstmsa.
'OutPuts Name and Value of each field
Debug.Print rstMSA.Fields(i ).Name & ": " & _
rstMSA.Fields(i ).Value
Next
rstMSA.MoveNext
Loop
Set cnnSQL = Nothing
Set rstSQL = Nothing

End Sub

[ByteMyzer]

The following Make-Table query should make a table in your local access
database named "Customers" and import the data from the table "customers" in
catalog "pubs" in datasource "northwind" :

SELECT C1.* INTO Customers
FROM [Provider=sqlole db;Data Source='northwi nd';Initial
Catalog='pubs'; Integrated Security=SSPI].customers AS C1;
See if this works for you.

"Ken" <ni************ @hotmail.com> wrote in message
news:6a******** *************** ***@posting.goo gle.com...

How can I copy data from SQL to MS Access using dsn-less connection
and not using SQL DTS? I have this but am stuck. I just want to do
an export but can't figure out how to do a "select into". It pastes
the data in SQL instead of access. The below code will cycle but I
will have to create a table to place the data first. Is there an
easier way.

Sub getTables()
Dim cnnSQL As ADODB.Connectio n
Dim cnnMSA As ADODB.Connectio n
Dim strSQL As String
Dim rstSQL As ADODB.Recordset
Dim rstMSA As ADODB.Recordset
Dim strServer As String
Dim strDB As String
Dim i As Long
Dim cnn As ADODB.Connectio n

strServer = "northwind"
strDB = "pubs"

Set cnnSQL = New ADODB.Connectio n
Set rstSQL = New ADODB.Recordset
Set cnnMSA = CurrentProject. Connection
Set rstMSA = New ADODB.Recordset
cnnSQL.Open "Provider=sqlol edb;Data Source='" & strServer & "';Initial
Catalog='" & strDB & "';Integrat ed Security=SSPI"
strSQL = "select * from customers"

Set rstMSA = cnnSQL.Execute( strSQL)
Do While Not rstMSA.EOF
For i = 0 To rstMSA.Fields.C ount - 1
rstmsa.
'OutPuts Name and Value of each field
Debug.Print rstMSA.Fields(i ).Name & ": " & _
rstMSA.Fields(i ).Value
Next
rstMSA.MoveNext
Loop
Set cnnSQL = Nothing
Set rstSQL = Nothing

End Sub

[Ken Johnson]

Sorry for posting late but it does not work. Any other ideas.

Sub subtest()
Dim strsql As String
Dim cmd As New ADODB.Command

strsql = "SELECT C1.* INTO Customers FROM [Provider=sqlole db;Data
Source='northwi nd';Initial Catalog='pubs'; Integrated
Security=SSPI].customers AS C1;"

With cmd
.ActiveConnecti on = CurrentProject. Connection
.CommandText = strsql
.CommandType = adCmdText
.Execute
End With

cmd.Close
Set cmd = Nothing
Debug.Print "done"
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!