Excel exporting to Access, via VBA, is causing instability

Question

I have to create over 170 named ranges in Excel which I am trying to load into an Access table. Below is my code.

Sub Load_To_ALLL_TSD()

Dim strDatabasePath As String
Dim oApp As Access.Application
Dim PathOfworkbook As String

PathToDB = ThisWorkbook.Path
strDatabasePath = PathToDB & "\RAROC.accdb"

Set oApp = CreateObject("Access.Application")
'Set db = Application.CurrentProject
oApp.Visible = True

oApp.OpenCurrentDatabase strDatabasePath

Set db = CurrentDb()
Set rs = db.OpenRecordset("ALLL_TSD", dbOpenTable)

    With oApp
            With rs
                .AddNew ' create a new record
                ' add values to each field in the record
                .Fields("TSD_Base_Rate_Received") = Range("TSD_Base_Rate_Received").Value
                .Fields("TSD_Base_Rate_Received_Input") = Range("TSD_Base_Rate_Received_Input").Value
                .Fields("TSD_Calculated_RAROC") = Range("TSD_Calculated_RAROC").Value
                .Fields("TSD_Capital_Factor") = Range("TSD_Capital_Factor").Value 

                ' etc, etc, lot more fields and named ranges here

                ' add more fields if necessary...
                .Update ' stores the new record
            End With
    End With
    
Set oApp = Nothing
MsgBox ("Done!  All Data saved to RAROC database!!")
 
End Sub

I'm getting some weird errors! If I run the code using F8, it works fine. If I click a button to fire the code, sometimes it works and sometimes it doesn't work. I has errored out on several different lines.

Once it threw an error here:

Set rs = db.OpenRecordset("ALLL_TSD", dbOpenTable)

Error reads 'object variable or with block not set'

Once it said 'Microsoft Access has stopped working' and it threw an error on this line.

.Fields("TSD_Base_Rate_Received_Input") = Range("TSD_Base_Rate_Received_Input").Value

I've seen some other weird things too.

I have a reference set to both:

Microsoft DAO 3.6 Object Library
Microsoft Access 14.0 Object Library

It almost seems like I'm establishing a connection to Access and then almost immediately I lost the connection, somehow.

Finally, I have no Forms or Reports, and the DB is not split. I have just one single table in there now, which I am trying to write to. What can I try to resolve this?

`Set db = oApp.CurrentDb()` You don't need to automate Access in order to load data into a table though: you can do it more simply using ADO. E.g. https://stackoverflow.com/questions/32821618/insert-full-ado-recordset-into-existing-access-table-without-loop — Tim Williams, Jul 06 '17 at 21:27
Whoa. Good catch. I tried that and it still doesn't work. When the code fails I put this in the Immediate Window: ?db.name I get 'Run time error 462: The remote server machine does not exist or is unavailable' — ASH, Jul 06 '17 at 21:34

Tim Williams · Accepted Answer · 2017-07-07T18:59:09.950

Here's a basic example without using Access.

Needs a reference to Microsoft ActiveX Data Objects 2.x Library

Sub Tester()

    Dim con As New ADODB.Connection, rs As New ADODB.Recordset

    con.Open "Provider=Microsoft.ACE.OLEDB.12.0;" _
             & "Data Source = " & ThisWorkbook.Path & "\RAROC.accdb"

    'get an empty recordset to add new records to
    rs.Open "select * from [ALLL_TSD] where false", con, _
             adOpenDynamic, adLockBatchOptimistic

    With rs
        .AddNew
        .Fields("TSD_Base_Rate_Received") = Range("TSD_Base_Rate_Received").Value
        .Fields("TSD_Base_Rate_Received_Input") = Range("TSD_Base_Rate_Received_Input").Value
        .Fields("TSD_Calculated_RAROC") = Range("TSD_Calculated_RAROC").Value
        .Fields("TSD_Capital_Factor") = Range("TSD_Capital_Factor").Value
        'etc...
        .UpdateBatch '<< EDIT
        .Close
    End With

    con.Close
End Sub

Thanks Tim. That looks like it should work, but it doesn't actually do anything. — ASH, Jul 07 '17 at 13:34
Sorry my mistake should have been `UpdateBatch` not `Update` — Tim Williams, Jul 07 '17 at 18:59

Excel exporting to Access, via VBA, is causing instability

1 Answers1

Linked