I feel really stupid, but Access/ADO question
I have an import routine for an Access table written in Access VBA. It should just take the table of a specified file and run its data through ADO, update and voila.
I feel really dumb, but shouldn't I be able to get an updatable RS from one DB, change the ActiveConnection, call UpdateBatch and have it save to the new DB? I've tried a bunch of different ways and nothing. No update. Even though there's a recordcount that's the same. What am I missing.
VB Code:
Private Sub ImportTable_Generic(ByVal ImportPath As String, ByVal TableName As String)
' DoCmd.runsql "Delete * from " & TableName & ";", True
' DoCmd.TransferDatabase acImport, "Microsoft Access", _
' ImportPath, acTable, TableName, _
' TableName, False
On Error GoTo err_ImportTable_Generic
Dim adoRSSource As New ADODB.Recordset
Dim adoRSDest As New ADODB.Recordset
Dim adoConSource As New ADODB.Connection
Dim strSQL As String
strSQL = "SELECT * FROM " & TableName
adoConSource.CursorLocation = adUseClient
adoConSource.CommandTimeout = 300
adoConSource.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ImportPath
adoRSSource.CursorLocation = adUseClient
adoRSSource.Open strSQL, adoConSource, adOpenForwardOnly, adLockBatchOptimistic, adCmdText
adoRSDest.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.Path & "\" & CurrentProject.Name
'adoRSDest.ActiveConnection = CurrentProject.Connection
adoRSDest.Open adoRSSource
adoRSDest.UpdateBatch
DoCmd.Beep
exit_ImportTable_Generic:
If Not (adoRSSource Is Nothing) Then Set adoRSSource = Nothing
If Not (adoRSDest Is Nothing) Then Set adoRSDest = Nothing
If Not (adoConSource Is Nothing) Then Set adoConSource = Nothing
Exit Sub
err_ImportTable_Generic:
Debug.Print Err.Number; Err.Description
MsgBox Err.Number & vbCrLf & Err.Description
Resume exit_ImportTable_Generic
End Sub
Watch. It'll be something really obvious, too.
