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.Connection
Dim cnnMSA As ADODB.Connection
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.Connection

strServer = "northwind"
strDB = "pubs"

Set cnnSQL = New ADODB.Connection
Set rstSQL = New ADODB.Recordset
Set cnnMSA = CurrentProject.Connection
Set rstMSA = New ADODB.Recordset
cnnSQL.Open "Provider=sqloledb;Data Source='" & strServer & "';Initial
Catalog='" & strDB & "';Integrated Security=SSPI"
strSQL = "select * from customers"

Set rstMSA = cnnSQL.Execute(strSQL)
Do While Not rstMSA.EOF
For i = 0 To rstMSA.Fields.Count - 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=sqloledb;Data Source='northwind';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.google.c om...

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.Connection
Dim cnnMSA As ADODB.Connection
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.Connection

strServer = "northwind"
strDB = "pubs"

Set cnnSQL = New ADODB.Connection
Set rstSQL = New ADODB.Recordset
Set cnnMSA = CurrentProject.Connection
Set rstMSA = New ADODB.Recordset
cnnSQL.Open "Provider=sqloledb;Data Source='" & strServer & "';Initial
Catalog='" & strDB & "';Integrated Security=SSPI"
strSQL = "select * from customers"

Set rstMSA = cnnSQL.Execute(strSQL)
Do While Not rstMSA.EOF
For i = 0 To rstMSA.Fields.Count - 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=sqloledb;Data
Source='northwind';Initial Catalog='pubs';Integrated
Security=SSPI].customers AS C1;"

With cmd
.ActiveConnection = 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!