Can you simplify this code?

[mondo3a]

I have a linked table and a non-linked table, both of which have the same structure. I want to copy all of the records from the non-linked table to the linked table. The non-linked table can not become a linked table, as the user has to browse to it (there are many databases in many directories, and I have the user select the appropriate one for the job). The following code works, but is really slow, as it reads and writes 1 record at a time. is there a better way to do this?

Dim connString As String
Dim dbsConn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim cnn As ADODB.Connection

Set cnn = New ADODB.Connection
cnn.ConnectionString = CurrentProject.Connection
cnn.Open
'
' Create connection.
'
Set dbsConn = New ADODB.Connection
'
' Set and open connection string.
' strSource is the user selected database.
'
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strSource & "; Persist Security Info=false"
dbsConn.Open connString
'
Set cmd = New ADODB.Command

' Select all formulation components. (the linked table)
strSQL = "SELECT * From [Formulation Components]"

Set rst = New ADODB.Recordset
rst.Open strSQL, dbsConn, adOpenKeyset, adLockOptimistic
'
While rst.EOF = False
'
' Save the formulation components records into the temporary table.
'
strSQL = "INSERT INTO FormulationComponentsTemporary VALUES('" & rst!FormulaID & ",'" & rst!Name & "', #" & rst!DateSaved & "#, " & rst![Batch Quantity] & ")"
cnn.Execute strSQL

rst.MoveNext
Wend

 

[traingamer]

Why not simply:

INSERT INTO FormulationComponentsTemporary 
SELECT * From [Formulation Components];

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[mondo3a]

I had tried that, and it comes up with an error like "unknown database". I think that's because the 2 databases are in different locations and connected to with two different ADODB.Connections. now if there's a way to write that query to use a full path name, then it might work, but i dont know if thats possible?

 

[traingamer]

Just use DoCmd.TransferDatabase to create a link to the remote table, then run the insert query, then delete the link.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Remou]

It is possible to use IN. For example:

[tt]SELECT tblT.ID, tblT.FieldX INTO tmpTest IN 'c:\docs\tek-tips.mdb'
FROM tblT;[/tt]

 

[mondo3a]

The TransferDatabase command worked perfectly and solved the problem. Thanks!