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.
 

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]
 

Forum statistics

Threads
1,081,748
Messages
5,361,057
Members
400,611
Latest member
ThebigG

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top