none
Excel Exporting Named Ranges to Access. Using VBA and Having Instability Issues!! RRS feed

  • 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 = oApp.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.

    Can someone help me out here?

    Thanks!


    MY BOOK

    Friday, July 7, 2017 11:57 AM

All replies

  • Instead of the DAO 3.6 Object Library (which is for .mdb databases), set a reference to the Microsoft Office 14.0 Access database engine Object Library (which is for .accdb AND .mdb).

    I see no need to open the Access application; you can simply open a DAO database and recordset and use that.

    Sub Load_To_ALLL_TSD()
        Dim strDatabasePath As String
        Dim PathToDB As String
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
    
        PathToDB = ThisWorkbook.Path
        strDatabasePath = PathToDB & "\RAROC.accdb"
    
        Set db = DAO.DBEngine.OpenDatabase(strDatabasePath)
        Set rs = db.OpenRecordset("ALLL_TSD", dbOpenDynaset)
    
        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
            .Update ' stores the new record
        End With
    
        rs.Close
        db.Close
    
        MsgBox "Done!  All Data saved to RAROC database!!"
    End Sub

    If you want to export ALL named ranges and if there is a field with exactly the same name for each, you could loop through the names:

    Sub Load_To_ALLL_TSD()
        Dim strDatabasePath As String
        Dim PathToDB As String
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim nm As Name
    
        PathToDB = ThisWorkbook.Path
        strDatabasePath = PathToDB & "\RAROC.accdb"
    
        Set db = DAO.DBEngine.OpenDatabase(strDatabasePath)
        Set rs = db.OpenRecordset("ALLL_TSD", dbOpenDynaset)
    
        With rs
            .AddNew ' create a new record
            ' add values to each field in the record
            For Each nm In ThisWorkbook.Names
                .Fields(nm.Name) = nm.Value
            Next nm
            .Update ' stores the new record
        End With
    
        rs.Close
        db.Close
    
        MsgBox "Done!  All Data saved to RAROC database!!"
    End Sub

    By the way, I would structure the table differently: I would use a RangeName field and a RangeValue field, and add a new record for each defined name. Store its name in the RangeName field and its value in the RangeValue field.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, July 7, 2017 12:18 PM
  • Sorry for missing the point here, but neither of those solutions work for me.  Both fail on this line.

    Set db = DAO.DBEngine.OpenDatabase(strDatabasePath)

    Run time error: 3343

    Unrecognized database format.

    Also, why would you use this?

    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    You already said 'DAO 3.6 Object Library (which is for .mdb databases), set a reference to the Microsoft Office 14.0 Access database engine Object Library'. 

    Thanks.


    MY BOOK

    Friday, July 7, 2017 1:31 PM
  • The "parent" object in the Microsoft Office 14.0 Access database engine Object Library is named DAO, just as in the DAO 3.6 Object Library. So you can use code written for the older library with the new one without changing it.

    Are you sure that RAROC.accdb is a real .accdb database, and not a .mdb database whose extension has been changed to .accdb?


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, July 7, 2017 2:56 PM
  • I'm getting this error when I uncheck the reference to DAO 3.6.

    I thought the Microsoft Office 14.0 Access database engine Object Library would handle that.  If I add in the DAO 3.6 reference, I get an error on this line.

    Set db = DAO.DBEngine.OpenDatabase(strDatabasePath)

    Error message:

    Run time error: 3343

    Unrecognized database format.

    Yes, it is an original .accdb extension.


    MY BOOK

    Friday, July 7, 2017 4:42 PM
  • To make sure, I have just now tested the essential parts of your code, referring to a .accdb database on my hard disk. The code ran without error: it opened the database, opened the recordset, then closed them again.

    If you get "User-defined type not defined" for DAO.Database, there must be something wrong with your reference. Access 2010 itself uses a reference to the 'Microsoft Office 14.0 Access database engine Object Library' to provide DAO functionality. Try clearing the reference, then setting it again.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, July 7, 2017 5:07 PM
  • Ok, well, maybe there is a problem with some DLL somewhere, or maybe something is messed up in my registry.  Although, I tried the code on a colleague's computer and got the same result.  I'll try it on my personal laptop when I get home tonight.

    Thanks for trying to help me!


    MY BOOK

    Friday, July 7, 2017 6:37 PM
  • On my machine at home, if I set a reference to Access, I get an error message about user defined type not defined.  This is the problem with the DAO 3.6 being unchecked.

    Now, if I do set a reference to DAO 3.6, I get a message about the unrecognized database format, which makes sense to me, because it's looking for the older MDB.   If I switch to the MDB database, everything works fine for me, because I'm running a slightly different kind of script.

    Thanks.


    MY BOOK

    Monday, July 10, 2017 1:14 AM
  • I suggested setting a reference to the Microsoft Office Access n.0 database engine Object Library (the "new" DAO library), not to the Microsoft Access n.0 Object Library (the Access application library).

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, July 10, 2017 5:21 AM
  • It works now!  Thanks a lot!!

    I never even knew about that library...


    MY BOOK

    Monday, July 10, 2017 7:38 PM
  • It works now!  Thanks a lot!!

    I never even knew about that library...


    MY BOOK

    Hello,

    I suggest you mark helpful post as answer to close this thread.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 11, 2017 8:16 AM
    Moderator