Hi All.
What is the fastest method to push a local access table to an sql server table. I am programming this application for global users. I tried looping thru each recordset (code below) and also tried a local access append query to a linked sql server table. Both methods seem to run at the same speed - slow. For the VBA method, I looped thru with a DAO local recordset and ado sql server recordset. Both tables have the same record fields.
Code:
'progress bar
SysCmd acSysCmdInitMeter, "Writing to Server...", rs_dao.RecordCount
j = 1
While Not rs_dao.EOF
rs_ado.AddNew
For i = 0 To rs_ado.Fields.Count - 1
Select Case rs_ado(i).name
Case "ElementID"
'do nothing this is a auto number / primary key created on sql server
Case "Publish Date"
rs_ado(i) = Now()
Case Else
rs_ado(i) = rs_dao(rs_ado(i).name)
End Select
Next i
rs_dao.MoveNext
j = j + 1
If j Mod 10 = 0 Then
SysCmd acSysCmdUpdateMeter, j
DoEvents
End If
Wend
SysCmd acSysCmdRemoveMeter
rs_ado.UpdateBatch
rs_dao.Close
Set rs_dao = Nothing
rs_ado.Close
Set rs_ado = Nothing
Thanks for the help.
Steve
Harrisburg, PA