VB SQL INSERT Statement (Help!)

[ranshe]

I am trying to open two databases and read from a table in one and insert it's data into a table in the other.
The database I am inserting into is in the current database (CurrentDB) and the databaase I am reading from (RemoteDB) is in another directory.
If I have the table in RemoteDB linked in CurrentDB, so that RemoteDB table is resident in CurrentDB, the following code works just fine:


Dim [COLOR=green yellow]CDB As New ADODB.Connection
Dim CRS As New ADODB.Recordset

Dim DataTo As String
Dim DataFrom As String
DataTo = "Inset_Field_X, Inset_Field_Y"
DataFrom = "FieldNameX = 'X' AND FieldNameY = 'Y'"

[COLOR=green yellow]CDB[/color].Open "CurrentDB"

CRS.Open _
"INSERT INTO tbl_Input_In_CBD (" & DataTo & ") SELECT * FROM tbl_Output_From_DB _
WHERE " & DataFrom & " ", _
[COLOR=green yellow]CDB[/color], adOpenDynamic, adLockOptimistic
[/color]

But, if I don't link the table in CurrentDB but try to open RemoteDB directly, I cannot make it work.
I need help on the INSERT syntax (I haven't changed the INSERT in the example below. All I have done is added the two bolded lines).
Here is what I have done so far (it blows-up at the CRS.Open “INSERT....... Statement):


Dim [COLOR=green yellow]CDB As New ADODB.Connection
Dim CRS As New ADODB.Recordset
Dim [COLOR=green yellow]RDB[/color] As New ADODB.Connection

Dim DataTo As String
Dim DataFrom As String
DataTo = "Inset_Field_X, Inset_Field_Y"
DataFrom = "FieldNameX = 'X' AND FieldNameY = 'Y'"

[COLOR=green yellow]CDB[/color].Open "CurrentDB"
[COLOR=green yellow]RDB[/color].Open "RemoteDB"

CRS.Open _
"INSERT INTO tbl_Input_In_CBD (" & DataTo & ") SELECT * FROM tbl_Output_From_DB _
WHERE " & DataFrom & " ", _
[COLOR=green yellow]CDB[/color], adOpenDynamic, adLockOptimistic
[/color]

Any help would be appreciated!
Thanks.

 

[dmksas]

hi,

CRS.Open _
"INSERT INTO tbl_Input_In_CBD (" & DataTo & ") SELECT * FROM tbl_Output_From_DB _
WHERE " & DataFrom & " ", _
CDB, adOpenDynamic, adLockOptimistic

Try

CRS.Open _
"INSERT INTO tbl_Input_In_CBD (" & DataTo & ") IN " & RemoteDb Location &" SELECT * FROM tbl_Output_From_DB _
WHERE " & DataFrom & " ", _
CDB, adOpenDynamic, adLockOptimistic

Danny

Never Think Impossible

 

[BobRodes]

Now, I don't understand why you're opening a recordset for an action query. Do you need records returned into crs? I've never tried opening a recordset to insert records into a database, so I don't know if it works or not, but I don't see where it's efficient to have a recordset running around when you don't need one.

Here's how I do this:

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim DataTo As String
Dim DataFrom As String

set cn = New ADODB.Connection
Set cmd = New ADODB.Command

DataTo = "Inset_Field_X, Inset_Field_Y"
DataFrom = "FieldNameX = 'X' AND FieldNameY = 'Y'"

cn.Open "CurrentDB"
With cmd
   .ActiveConnection = cn
   .CommandText = "INSERT INTO tbl_Input_In_CBD (" & _
                  DataTo & ") SELECT * FROM tbl_Output_From_DB _
                  "WHERE " & DataFrom
   .Execute
End With

Command objects are intended to run action queries and stored procedures. Also, you'll notice that I removed the "New" keyword from your object declarations, instantiating the object on another line. This is in nearly all cases considered best practice in VB6. This is because VB6 doesn't actually instantiate the object when you declare it with the new keyword (as, say, VB.Net or C# do). Rather, it goes through some hoops each time a method or property of the object is referenced to find out if the object has been instantiated yet, and instantiates it if not. This process of checking is inefficient, since it has to be done every time the object is referenced.

HTH

Bob