I feel really stupid, but Access/ADO question

[WALDO]

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.

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.

[mendhak]

http://beta.experts-exchange.com/Pro..._20285708.html


http://www.experts-exchange.com/Prog..._10105621.html

[WALDO]

I'm a schmack! We figured out what it was. The rows we were attempting to update had autonumbers and were already deleted before we tried to import the tbl. It updated perfectly, just had no rows to show for it.

Oh well.

[mendhak]

Well smack. :doh: