none
Interrogating Acces DB from Excel RRS feed

  • Question

  • Using Excel 2016 and Access 2016 with Windows 10

    I am running a procedure in Excel VBA to extract from an Access Database certain items of data for use within Excel.  Using the Access keyword DLookUp from within Excel produces an error that I have been unable to sort out.  The following (much reduced) procedure illustrates my problem and produces the error.  The code compiles OK when I check it via Debug. 

    Sub DataFromAccess2()

     

    Dim Con As New ADODB.Connection

    Dim rstPurchaseItems As New ADODB.Recordset

    Dim rstCompanies As New ADODB.Recordset

    Dim strSQLPurch As String

     

    Dim strCompany As String

     

        Con.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source = C:\Data\Coltar Recycling Accounts FE v6Develop.accdb"

        rstPurchaseItems.Open "SELECT ColtarRef, CostTypeID, CompanyID, PaymentDue, PaymentDate, CostGross, CurrencyID FROM qryPurchaseItems WHERE PassedToFinancial =False;", _

            Con, adOpenForwardOnly, adLockOptimistic

        rstCompanies.Open "SELECT CompanyID, CompanyName FROM tblCompanies;", Con, adOpenForwardOnly, adLockOptimistic

       

        strCompany = Con.DLookup("[CompanyName]", rstCompanies, "[CompanyID] = 7")

     

    TidyUp:

        Set Con = Nothing

        rstPurchaseItems.Close

        Set rstPurchaseItems = Nothing

        rstCompanies.Close

        Set rstCompanies = Nothing

       

    End Sub

     

    The error at the line above TidyUp

    strCompany = Con.DLookup("[CompanyName]", rstCompanies, "[CompanyID] = 7")

     is:

     

    “Run-time error ‘3001’:

    Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.”

     

    By looking at the Locals window I see that the rstCompanies fields contain the two items as expected – CompanyName an CompanyID, but that their DataFormat is set to Nothing and is shown as <Unsupported object type>.  However, in tblCompanies CompanyName is ShortText and CompanyID is AutoNumber

     

    I have tried changing the variable type to Variant without success, and I have tried other ways of telling the Excel code that I am using Access,  I have set the References to include Microsoft Access16.0 Object Library and Microsoft ActiveX Data Objects 6.1 Library. 

     

    I’d be grateful for advice on what I am doing wrong


    Saturday, December 8, 2018 9:47 PM

Answers

  • Hi Andy,

    >> So My error seems to lie in the way I have referenced the Access DB from Excel.  But I cannot work out what that error is!

    First, about that reference you mentioned we should add a reference to the Microsoft ActiveX Data Object x.x library.

    And for more information about retrieve data from Access-database to Excel, please review the following link:

    Retrieve data from Access-database to Excel with ADO

    Hopefully it helps you.

    Best Regards,

    Yuki


    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.

    • Marked as answer by AndyColRomsey Tuesday, December 11, 2018 9:26 PM
    Tuesday, December 11, 2018 7:26 AM
    Moderator

All replies

  • Hi Andy,

    >> The error at the line above TidyUp

    strCompany = Con.DLookup("[CompanyName]", rstCompanies, "[CompanyID] = 7") is:

    “Run-time error ‘3001’:

    Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.”

    For more information about that error, please review the following links:

    recordset.open gives runtime error 3001 "Arguments are of the wrong type.."

    Runtime error 3001 'Arguments are of the wrong type or out of acceptable range…'

    Hopefully it helps you.

    Best Regards,

    Yuki


    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.

    Monday, December 10, 2018 3:01 AM
    Moderator
  • Yuki Lou

    Thanks you for looking at this.  I have seen both the references you gave.  The problem seems to lie in the fact that I am running Access code from Excel.  When I put similar code directly into Access VBA it runs OK.  So My error seems to lie in the way I have referenced the Access DB from Excel.  But I cannot work out what that error is!

    Andy C

    Monday, December 10, 2018 11:23 AM
  • Hi Andy,

    >> So My error seems to lie in the way I have referenced the Access DB from Excel.  But I cannot work out what that error is!

    First, about that reference you mentioned we should add a reference to the Microsoft ActiveX Data Object x.x library.

    And for more information about retrieve data from Access-database to Excel, please review the following link:

    Retrieve data from Access-database to Excel with ADO

    Hopefully it helps you.

    Best Regards,

    Yuki


    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.

    • Marked as answer by AndyColRomsey Tuesday, December 11, 2018 9:26 PM
    Tuesday, December 11, 2018 7:26 AM
    Moderator
  • Yuki-Lou

    Thanks for the new guidance.  That looks like what I need, and should give me the proper connection to Access.  I shall need to play with it further, but it is the answer I was seeking.

    With thanks

    Andy C

    Tuesday, December 11, 2018 9:26 PM