VLOOKUP to Access Database

MNpoker

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

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
If you got it to work, then what's your SQL string?

Flip to SQL View in the Query Design mode. :)
 

MNpoker

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

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
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'"
 

MNpoker

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

Richard Schollar

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

MNpoker

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

phuongdoan

New Member
Joined
Jul 5, 2019
Messages
1
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]
 

Watch MrExcel Video

Forum statistics

Threads
1,096,432
Messages
5,450,393
Members
405,608
Latest member
griffindor2020

This Week's Hot Topics

Top