By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,894 Members | 2,161 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,894 IT Pros & Developers. It's quick & easy.

dsn-less connection copy data

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Nov 13 '05 #2

P: n/a
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!
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.