VLOOKUP to Access Database

Pinball

Board Regular
Joined
Apr 18, 2002
Messages
107
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.
 

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
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 ....
 

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
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.aspx?scid=/support/excel/dao.asp
 

MNpoker

Board Regular
Joined
Dec 15, 2003
Messages
154
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.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
 

MNpoker

Board Regular
Joined
Dec 15, 2003
Messages
154
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!!
 

MNpoker

Board Regular
Joined
Dec 15, 2003
Messages
154
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
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
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? :confused:
 

Forum statistics

Threads
1,078,364
Messages
5,339,760
Members
399,321
Latest member
ladeko

Some videos you may like

This Week's Hot Topics

Top