can you simplify this code?

[mondo3]

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

 

[Ripley]

using adodb thats about as simple as you can get it

 

[ByteMyzer]

I might suggest the following amended code, using two ADODB.Recordset objects:

Dim connString As String
Dim dbsConn As ADODB.Connection
Dim rS As ADODB.Recordset
Dim rD As ADODB.Recordset
Dim strSQL As String
Dim cnn As ADODB.Connection

Set cnn = New ADODB.Connection
cnn.ConnectionString = CurrentProject.Connection
cnn.Open

Set dbsConn = New ADODB.Connection

connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
    & strSource & "; Persist Security Info=false"
dbsConn.Open connString

strSQL = "SELECT * From [Formulation Components]"

Set rS = New ADODB.Recordset
rS.Open strSQL, dbsConn, adOpenForwardOnly, adLockReadOnly

Set rD = New ADODB.Recordset
rD.Open "FormulationComponentsTemporary", _
    CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic, _
    adCmdTableDirect

Do While Not rS.EOF
    rD.AddNew
    rD.Fields("FormulaID") = rS.Fields("FormulaID")
    rD.Fields("Name") = rS.Fields("Name")
    rD.Fields("DateSaved") = rS.Fields("DateSaved")
    rD.Update
    rS.MoveNext
Loop

rD.Close
Set rD = Nothing

rS.Close
Set rS = Nothing

dbsConn.Close
Set dbsConn = Nothing

 

[mondo3]

appreciate the effort, but this code runs in exactly the same amount of time as the earlier code.

 

[KeithG]

Try using a DAO recordset. I have heard they are more efficent if you are just use JET.

 

[ByteMyzer]

Better still, try this single VBA statement to execute this single query:

CurrentDb.Execute "INSERT INTO FormulationComponentsTemporary" _
    & " SELECT T1.FormulaID, T1.Name, T1.DateSaved" _
    & " FROM [MS Access;Database=" & strSource _
    & ";].[Formulation Components] T1"