Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: VLOOKUP to Access Database
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Posts
    107
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VLOOKUP to Access Database

    Can Excel VLOOKUP reference an Access Database? I have over a million records in Access (way too big for Excel), want to run VLOOKUP in Excel to match against these Access records. Reason I need to do this in Excel is because the users are not at all familar with Access and want to keep using Excel.

    Thanks much.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    No, you'd need to write a query.

  3. #3
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can use the Pivot Table Wizard to get external data from access into a Pivot Table. If you want to then use a Vlookup Formula to get info from the Pivot use the formula item "GetPivotData".

    Or use VBA code to connect to Access and create a ADO or DAO object and user SQL to Querie the object ....
    <MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>

  4. #4
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here's some code that will allow you to see how to modify/download data from Access To Excel.

    Sub UpdateRecordsQuery()
    Dim Db As database
    Dim Qd As QueryDef
    Dim Rs As Recordset
    Dim qdParmQD As QueryDef
    Dim SQL As String
    Dim i As Integer

    ' Set your database object. You may need to change the path to match
    ' where Microsoft Office is installed.
    Set Db = _
    workspaces(0).OpenDatabase("C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb")

    ' 1. Create a PARAMETERS clause string.
    SQL = "PARAMETERS [CountryWanted] TEXT; "

    ' 2. Create a SELECT statement.
    SQL = SQL & "SELECT DISTINCTROW * " & _
    " FROM Customers" & _
    " WHERE (Customers.Country =[CountryWanted]) "

    ' 3. Create a named QueryDef object with your SQL statement.
    Set Qd = Db.CreateQueryDef("Find Customers", SQL)

    ' 4. Set the QueryDef object parameters.
    Set qdParmQD = Db.querydefs("Find Customers")
    qdParmQD("CountryWanted") = "Germany"

    ' 5. Execute the QueryDef.
    Set Rs = qdParmQD.OpenRecordset()

    ' 6. Issue a MoveLast followed by a MoveFirst
    Rs.MoveLast
    Rs.MoveFirst

    ' 7. Set up a loop that will modify each record in the recordset.
    For i = 1 To Rs.RecordCount
    Rs.Edit
    Rs("Region") = "Europe"
    Rs.Update
    Rs.MoveNext
    Next i

    ' At this point, the database has been modified. The rest of this
    ' code displays the data on a worksheet. This is not necessary to
    ' complete the operation.

    ' 8. Collect field names.
    For i = 0 To Rs.Fields.Count - 1
    Sheets("Sheet1").Cells(1, i + 1).Value = Rs.Fields(i).Name
    Next i
    Sheets("Sheet1").Range(Sheets("Sheet1").Cells(1, 1), _
    Sheets("Sheet1").Cells(1, Rs.Fields.Count)).Font.Bold = True

    ' 9. Issue a MoveFirst to move to the beginning of the recordset.
    Rs.MoveFirst

    ' 10. Use CopyFromRecordset to move the data onto the worksheet
    Sheets("Sheet1").Range("A2").CopyFromRecordset Rs

    ' 11. Select the sheet that data was written to and autofit the
    ' column widths.
    Sheets("Sheet1").Select
    Selection.CurrentRegion.Select
    Selection.Columns.AutoFit

    ' Select cell A1.
    Range("A1").Select

    ' 12. Clean up and delete the QueryDef that was just created. This
    ' removes it from the database. Then close the objects.
    Db.querydefs.Delete "Find Customers"
    Qd.Close
    Rs.Close
    Db.Close

    End Sub


    You may also want to check out this info from MicroSoft :http://support.microsoft.com/default.../excel/dao.asp
    <MARQUEE>...........Never be afraid to try something new. Remember, amateurs built the ark, professionals built the Titanic...............The easiest thing to find is fault, don't be easy !.. --Anonymous--...</marquee>

  5. #5
    Board Regular
    Join Date
    Dec 2003
    Posts
    154
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi this worked GREAT when I ran it with the sample.

    But now when I put in a Database I am getting a:

    Run Time error '3012'
    Object 'Find R106YTDC' already exists

    Any help!!??

    Here's my File (Up to step 4)

    'Get the Input Data from Excel Inputs
    Path = "H:\Actuarial\CropHail\ActuarialDataMaster\ADMmaster.mdb"
    Set Db = _
    Workspaces(0).OpenDatabase(Path)

    'HERE WE CHOOSE A TABLE AND WHAT TO PULL IN
    'BEFORE FINISHING THIS SHOULD JUST POINT TO WHAT DATA IS NEEDED
    'AND HAVE SEPARATE MACROS FOR EACH ONE

    'Get the Key Field Indicators
    Crop_Year0 = 2006

    ' 1. Create a PARAMETERS clause string.
    SQL = "PARAMETERS [CropYear] integer; "
    '[State-Code] Long Integer; [County-Code] Long Integer; [Crop-Code] Text; [Insurance-Plan-ID] Long Integer; [Practice-Code] Text; [Type-Code] Text"

    ' 2. Create a SELECT statement.
    SQL = SQL & "SELECT DISTINCTROW * " & _
    " FROM R106YTDC" & _
    " WHERE (R106YTDC.CropYear =[CropYear1])"
    '; R106YTD_C.State-Code =[State_Code1]; R106YTD_C.Couty-Code =[County_Code1]; R106YTD_C.Crop-Code =[Crop_Code1]; R106YTD_C.Insurance-Plan-ID =[Insurance-Plan-Id1]; R106YTD_C.Practice-Code =[Practice_Code1]; R106YTD_C.Type-Code =[Type_Code1]"

    ' 3. Create a named QueryDef object with your SQL statement.
    Set Qd = Db.CreateQueryDef("Find R106YTDC", SQL)

    ' 4. Set the QueryDef object parameters.
    Set qdParmQD = Db.QueryDefs("Find R106YTDC")
    qdParmQD("CropYear1") = Crop_Year0

    The bolded area is where I am getting the error.

  6. #6
    MrExcel MVP
    Moderator
    Inactive
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,705
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default

    Hi

    I presume that you managed to execute this without errors the first time you tried it? If so, it is likely that you did not delete the querydef following execution, which means there is a query in your Access database with that name already - hence the error given. You need to modify your code to what Nimrod originally posted, so that the query def is removed at the end of the macro run. In the meantime, simply opening your Access Db and deleting the relevant query should allow you to run the code again (but you need to make the modifications to your code so you don't run into the same problem again).

    I hope this helps you out!

    Richard
    Richard Schollar

    Using xl2013

  7. #7
    Board Regular
    Join Date
    Dec 2003
    Posts
    154
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank You!

    It is deleted but because it's deleted at the end of the macro it wasn't getting that far.

    One more question if I may?

    How do I change the code to select two (Or More) fields?
    Say I want ONLY the records where
    Country = Germany AND
    City = Berlin

    Thanks Again!!

  8. #8
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello,

    Not sure I would create a QueryDef, why not just open a Recordset and take a peek?

    E.g.,

    http://www.mrexcel.com/board2/viewto...=577124#577124

  9. #9
    Board Regular
    Join Date
    Dec 2003
    Posts
    154
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Overall project:

    A user enters pricing inputs into Excel.

    Then based on those inputs (There are 9 or 10) I need Excel to go out to Access and pull in the data needed for the pricing factors.

    Then back in Excel I will use the factors to price what is needed.

    For example:
    A user might input (fake stuff)
    CarColor
    CarType
    Doors
    TireSize
    blah
    blah
    blah

    Then in Access there a databases that have what is needed for inputs.
    So it goes into a database (There are about 50) and pulls in the data needed to figure out pricing.

    So here it might go into ExtrasDB
    That DB will have factors for certain extras BASED ON the 'key criteria' above. (i.e. AC on a car thats Blue, Coupe, 2 door, with 16 inch wheels may have a different cost than one that Red, Coupe, 2 Dorr, 16 inch wheels)

    I also need to be able to do it and understand the coding.

    Any suggestions on the best way to do this are appreciated. I can do it using the get-external-data route but that would be unbelievably painful and slow. This way I can set the macros to only run only if they contain needed data (based on inputs), instead of having to look at all 50 db's everytime.

    ((Oh yeah and I added a question above)) -- How do I run this for several parameters at once?
    - Match field A and field B - Then pull field C

  10. #10
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Create the query in Access, to test, then simply grab the SQL and use that in your VBA routine.

    Which part of my example did you not follow, specifically?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •