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.
 
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:

OK I got it to work!

Now I need to be able to query more than one field (Both in the key for the lookup and fields to return).

For example
Access Table
Field A, B, C, D
10, 1, 100, 1000
10, 2, 999, 999
20, 1, 50, 40
20, 2, 25, 25

Input Field A and Field B.

Output Field C and Field D in the record where Field A = Input A and Field B = Input B.
 
Upvote 0

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
If you got it to work, then what's your SQL string?

Flip to SQL View in the Query Design mode. :)
 
Upvote 0
Here is the FULL Query in SQL view:
SELECT R106YTDCnew.CropYear, R106YTDCnew.StateCode, R106YTDCnew.CountyCode, R106YTDCnew.CropCode, R106YTDCnew.InsurancePlanID, R106YTDCnew.PracticeCode, R106YTDCnew.TypeCode, R106YTDCnew.ReferenceYield, R106YTDCnew.ReferenceRate, R106YTDCnew.Exponent, R106YTDCnew.ExponentSign, R106YTDCnew.FixedRateLoad, R106YTDCnew.PriorYrHighYield1, R106YTDCnew.PriorYearBasePremRate1, R106YTDCnew.PriorYrHighYield2, R106YTDCnew.PriorYearBasePremRate2, R106YTDCnew.PriorYrHighYield3, R106YTDCnew.PriorYearBasePremRate3, R106YTDCnew.PriorYrHighYield4, R106YTDCnew.PriorYearBasePremRate4, R106YTDCnew.PriorYrHighYield5, R106YTDCnew.PriorYearBasePremRate5, R106YTDCnew.PriorYrHighYield6, R106YTDCnew.PriorYearBasePremRate6, R106YTDCnew.PriorYrHighYield7, R106YTDCnew.PriorYearBasePremRate7, R106YTDCnew.PriorYrHighYield8, R106YTDCnew.PriorYearBasePremRate8, R106YTDCnew.PriorYrHighYield9, R106YTDCnew.PriorYearBasePremRate9, R106YTDCnew.PriorYrHighYield10, R106YTDCnew.PriorYearBasePremRate10, R106YTDCnew.PriorYrHighYield11, R106YTDCnew.PriorYearBasePremRate11, R106YTDCnew.PriorYrHighYield12, R106YTDCnew.PriorYearBasePremRate12, R106YTDCnew.PriorYrHighYield13, R106YTDCnew.PriorYearBasePremRate13, R106YTDCnew.PriorYearReferenceYield, R106YTDCnew.PriorYearReferenceRate, R106YTDCnew.PriorYearExponent, R106YTDCnew.PriorYearExponentSign, R106YTDCnew.PriorYrFixedRateLoad
FROM R106YTDCnew
WHERE (((R106YTDCnew.CropYear)=2006) AND ((R106YTDCnew.StateCode)=19) AND ((R106YTDCnew.CountyCode)=105) AND ((R106YTDCnew.CropCode)="0041") AND ((R106YTDCnew.InsurancePlanID)=44) AND ((R106YTDCnew.PracticeCode)="002") AND ((R106YTDCnew.TypeCode)="016"));

And here is how I tried to use it in your code. (I need more than just Reference Yield but I couldn't get this part to work so thought I'd start here.

With rs
Set .ActiveConnection = cn
.Source = "SELECT [ReferenceYield], FROM R106YTDC Where (((CropYear)=2006) AND ((StateCode)=19) AND ((CountyCode)=105) AND ((CropCode)=0041) AND ((InsurancePlanID)=44) AND ((PracticeCode)=002) AND ((TypeCode)=016));"
.Open , , 3, 3 '.Open , , adOpenStatic, adLockOptimistic
If Not .EOF Then Let myReturn = .Fields(0)
.Close
End With

Still getting an error.

The Select Statement Includes a reserved word ....

(Note I will need to pull more fields than just Reference Yield.) Baby Steps?
 
Upvote 0
How about this?

Code:
.Source = "SELECT [ReferenceYield] FROM R106YTDC Where CropYear=2006 AND StateCode=19 AND " & _
    "CountyCode=105 AND CropCode='0041' AND InsurancePlanID=44 AND PracticeCode='002' " & _
    "AND TypeCode='016'"
 
Upvote 0
That works!

Can I get several fields at once?

Or do I need to repeat that code for each field I want to pull?

I really appreciate all your help on this.

-----------------------------------------------------------

And might as well ask this now also (then I think I'm done)
-- If the qualifiers are not unique Can I get data from multiple records?
 
Upvote 0
No, just include the field headings after the SELECT, separated by commas:

Code:
.Source = "SELECT [ReferenceYield], ReferenceRate, Exponent  FROM R106YTDC Where CropYear=2006 AND StateCode=19 AND " & _ 
    "CountyCode=105 AND CropCode='0041' AND InsurancePlanID=44 AND PracticeCode='002' " & _ 
    "AND TypeCode='016'"

etc etc

Richard
 
Upvote 0
WOW that was quick!

Worked thanks!

-- If there is more than 1 record that matches the qualifiers
Can I get data from multiple records?

An example would be: I want to pull all the data from the same records as above but now I want it regardless of the State
-- So I take out the StateCode part of the query.

Can I get the factors for all 50 states? Based on my other qualifiers?

--- Officially my last question on this ---
 
Upvote 0
Hi Nate,

My following question is not exactly the same problem you guys were discussing but it's related, because I'm trying to write codes to vlookup data from Access to Excel and got this error message "Not a valid password". Can you help guide me how to connect to this password-protected Access database?

The tool I'm referencing to is Microsoft Office 15.0 Access database engine Object library.

Below are my codes:

Sub ddd()
Dim wj As Workspace
Dim db As Database
Dim rs As Recordset

Set wj = CreateWorkspace("", "admin", "", dbUseJet)
Set db = wj.OpenDatabase("R:\Auction\Price Trend.accdb")

For Each ce In Range("X3:X" & Cells(Rows.Count, 1).End(xlUp).Row)
Set rs = db.OpenRecordset("select [Auction results].[Total Cost in : USD], [Auction results].[unit price], [Auction results].[Winner] from [Auction results] where [Lot Name] = '" & ce.Value & "'")
ce.Offset(0, 6).Value = rs.Fields("Winning Lot Bid")
ce.Offset(0, 8).Value = rs.Fields("Winning Unit Bid")
ce.Offset(0, 7).Value = rs.Fields("Winning Bidder")
Set rs = Nothing

Next ce

Set db = Nothing
Set wj = Nothing


End Sub

[QUOTE
=NateO;1048461]Yes, the entire Recordset is returned, use CopyFromRecordset.

See the following:

http://support.microsoft.com/kb/246335/

And search the board for 'CopyFromRecordset'. ;)[/QUOTE]
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top