Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


Q: how to get data from MS Access with Excel VBA code

Hello all; I would like to open an MS Access database with an Excel function and read data from tables (and later from queries).

Can anyone share some VBA code that I could hack to write my own functions to do this (and eventually compile into .xla files)?

I really just need some examples of code to get me going.

Any & all help much appreciated.




  • : Hello all; I would like to open an MS Access database with an Excel function and read data from tables (and later from queries).

    Hi - I'm new here - a first timer - I had a tough time with that and found DLookup() would do the trick - although there are other ways.

    If you make a control like a text box on the Acces page and then make the value of the object (the text box) = to whatever you can get
    from a DLookup function you can use and store that value in further code by referencing the current value of that object.


    --------below info copied from Access or AccessVB help --------

    The DLookup function returns a single field value based on the information specified in criteria. Although criteria is an optional argument, if you don't supply a value for criteria, the DLookup function returns a random value in the domain.

    If no record satisfies criteria or if domain contains no records, the DLookup function returns a Null.

    If more than one field meets criteria, the DLookup function returns the first occurrence. You should specify criteria that will ensure that the field value returned by the DLookup function is unique. You may want to use a primary key value for your criteria, such as [EmployeeID] in the following example, to ensure that the DLookup function returns a unique value:

    DLookup(expr, domain[, criteria])

    Dim varX As Variant

    varX = DLookup("[LastName]", "Employees", "[EmployeeID] = 1")

    Whether you use the DLookup function in a macro or module, a query expression, or a calculated control, you must construct the criteria argument carefully to ensure that it will be evaluated correctly.

    You can use the DLookup function to specify criteria in the Criteria row of a query, within a calculated field expression in a query, or in the Update To row in an update query.

    sample *************************

    DLookup Function Example

    The following example returns name information from the CompanyName field of the record satisfying criteria. The domain is a Shippers table. The criteria argument restricts the resulting set of records to those for which ShipperID equals 1.

    Dim varX As Variant
    varX = DLookup("[CompanyName]", "Shippers", "[ShipperID] = 1")


    The next example from the Shippers table uses the form control ShipperID to provide criteria for the DLookup function. Note that the reference to the control isn't included in the quotation marks that denote the strings. This ensures that each time the DLookup function is called, Microsoft Access will obtain the current value from the control.

    Dim varX As Variant
    varX = DLookup("[CompanyName]", "Shippers", "[ShipperID] = " _
    & Forms!Shippers!ShipperID)


    The next example uses a variable, intSearch, to get the value.

    Dim intSearch As Integer, varX As Variant
    intSearch = 1
    varX = DLookup("[CompanyName]", "Shippers", _
    "[ShipperID] = " & intSearch)

  • StannyBoyStannyBoy Posts: 115
    I think you need something like this

    First, add Microsoft DAO to the referenced libraries (Tools->References)

    Then the following code

    Sub fillsheet()
    Dim dbDatabase As Database
    Dim rsData As Recordset
    Set dbDatabase = OpenDatabase("c:db1.mdb")
    Set rsData = dbDatabase.OpenRecordset("table1")
    arrData = rsData.GetRows(1000)
    For x = 0 To UBound(arrData, 2)
    Sheets(1).Cells(x + 1, 1).Value = arrData(0, x)
    ' Sheets(1).Cells(x + 1, 2).Value = arrData(1, x)
    x = 1
    Do Until rsData.EOF
    Sheets(1).Cells(x, 2).Value = rsData.Fields(1)
    x = x + 1
    End Sub

    In this example, "table1" is a simple table with two fields. The commented-out line in the "for" loop does the same thing as the second loop, I just put that in there to show you another example.
Sign In or Register to comment.