Absolute Newbie Biting Off More Than Can Chew

[gbaker]

I'm trying to write a very simple data migration program.
I have a SQL2000 Database on one machine and an Access Database on another. I'm trying to move the SQL data to Access.
What I've done is:
Set Conn1 = CreateObject("ADODB.Connection&quot
Conn1.Open "DSN", "User", "Pwd"
Set Conn2 = CreateObject("ADODB.Connection&quot
Conn2.Open "DSN", "User", "Pwd"
Set Rst1 = Conn1.Execute(" Select * from Table1&quot
Do While Not Rst1.EOF
Conn2.Execute (" Insert into Telnumbers(Field1,Field2,Field3) Values(Rst1!Value1, Rst1!Value2, Rst1!Value3)&quot
Rst1.MoveNext
Loop

Which, of course does not insert the values, because SQL doesn't recognize the variables. Is there a simple way to get this done?
Thanks.

 

[zemp]

You have the concept correct but I think that you should change your syntax a bit. The first thing to try would be to change your insert statement so that it properly inserts the variables. You have to use string concatenetion for this,

"Insert into Telnumbers(Field1,Field2,Field3) Values(" & rs1![Value1] & "," & rs1![Value2] & "," & rs1![Value3] & ""

If your field vaules are string values then your insert statement should lok like this.

"Insert into Telnumbers(Field1,Field2,Field3) Values('" & rs1![Value1] & "','" & rs1![Value2] & "','" & rs1![Value3] "');"

If it still does not work, this is what I would try,

Dim Conn1 As New ADODB.Connection
Dim Conn2 As New ADODB.Connection
Dim rs1 As New ADODB.Recordset

Set Conn1 = New ADODB.Connection
Conn1.ConnectionString = "DSN=myDSN1"
Conn1.Open

Set Conn2 = New ADODB.Connection
Conn2.ConnectionString = "DSN=myDSN2"
Conn2.Open

rs1 = Conn1.Execute("SELECT * FROM Table1;&quot
Do While Not rs1.EOF
rs2.open "Insert into Telnumbers(Field1,Field2,Field3) Values(" & rs1![Value1] & "," & rs1![Value2] & "," & rs1![Value3] & "",Conn2, adOpenStatic, adLockOptimistic
rs1.MoveNext
set rs2 = nothing
Loop

'myDSN1' is the valid DSN connection for the first connection, SQL in your case. and 'myDSN2' is the DSN connection to Access. I used the DSN connections because you did. However it is not the most efficient method. It has the added overhead of the odbc drivers. However for your purposes the extra overhead will not be noticed.
Thanks and Good Luck!

zemp

 

[SemperFiDownUnda]

Ummm Why don't you use the Data Transformation Services (DTS) that comes with SQL Server?

 

[gbaker]

Thanks zemp, worked fine.