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. stackoverflow.com/questions/32821618/… — Tim Williams
– Tim Williams, Commented Jul 6, 2017 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
– ASH, Commented Jul 6, 2017 at 21:34

Tim Williams · Accepted Answer · 2017-07-07 18:59:09Z

3

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

edited Jul 7, 2017 at 18:59

answered Jul 6, 2017 at 22:06

Tim Williams

169k8 gold badges104 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ASH Over a year ago

Thanks Tim. That looks like it should work, but it doesn't actually do anything.

Tim Williams Over a year ago

Sorry my mistake should have been UpdateBatch not Update

Collectives™ on Stack Overflow

Excel exporting to Access, via VBA, is causing instability

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related