I can't figure out how to query a table from VBA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Terry Olsen - 28 Sep 2007 15:40 GMT

I have googled for 2 days and tried every example I can find to use
ADODB to query a table from VBA. I'm using Access 2003. In all cases,
the error I get is something to the tune of "you left a required
parameter blank". I even tried plugging in all the Optional parameters,
I still get the same error.

I opened up another database and it had a totally different way of
querying a table. That's what I currently have coded in my module. And
I'm still getting a similar error: "Too few parameters. Expected: 2"
Here's my code:

-------------------------------
Dim db As Database
Set db = CurrentDb
Dim qry As QueryDef
Dim rs As Recordset

Dim qryStr As String
qryStr = "SELECT * FROM [tblImportedOrders],[Addresses] " & _
"WHERE [Addresses].[Last Name] =
[tblImportedOrders].[CustLastName] " & _
"AND [Addresses].[First Name] =
[tblImportedOrders].[CustFirstname] " & _
"AND [tblImportedOrders].[Amount] <> '0.01'"

Set qry = db.CreateQueryDef("", qryStr)
---------------------------------

The error happens on the Set qry line. I'm hoping someone can steer me
in the right direction.
Set rs = qry.OpenRecordset

Reply to this Message

'69 Camaro - 28 Sep 2007 16:01 GMT

Hi, Terry.

> I'm still getting a similar error: "Too few parameters. Expected: 2"

Assuming "Amount" is a numerical data type, and FKey is the column name of
the foreign key in the tblImportedOrders table, and PKey is the column name
of the primary key in the Addresses table (replace them with your own), try:

Dim db As Database
Dim qry As QueryDef
Dim rs As ADODB.Recordset
Dim qryStr As String

Set db = CurrentDb
qryStr = "SELECT * FROM [tblImportedOrders] " & _
"INNER JOIN [Addresses] ON " & _
"tblImportedOrders.FKey = Addresses.PKey " & _
"WHERE [Addresses].[Last Name] =
[tblImportedOrders].[CustLastName] " & _
"AND [Addresses].[First Name] =
[tblImportedOrders].[CustFirstname] " & _
"AND [tblImportedOrders].[Amount] <> 0.01"
Set qry = db.CreateQueryDef("", qryStr)

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

Reply to this Message

Salad - 28 Sep 2007 16:05 GMT

> I have googled for 2 days and tried every example I can find to use
> ADODB to query a table from VBA. I'm using Access 2003. In all cases,
[quoted text clipped - 29 lines]
>
> *** Sent via Developersdex http://www.developersdex.com ***

Do you have Access? If so, why not build the query in the query builder
(Query/New/Design (add your Tables)/Cancel. Now drag the * from both
tables and set your where conditions. Then from the menu select
View/SQL and copy/paste that SQL to your code window and build qrySTR
from it.

Reply to this Message

Terry Olsen - 28 Sep 2007 16:54 GMT

Thanks. That's what I ended up doing, after I figured out how. I've
never been good at using the query designer and I have done stuff like
this extensively with VBScript. I thought VBA would be the same.

So after getting the query to work, here's what Access came up with for
a query string:

SELECT Addresses1.FirstName, Addresses1.LastName,
Addresses1.ShippingAddress, Addresses1.ShippingAddress2,
Addresses1.ShippingCity, Addresses1.ShippingState,
Addresses1.ShippingZip, Addresses1.CountryRegion,
Addresses1.EmailAddress, Addresses1.EmailFlag, Addresses1.PhoneNum,
Addresses1.BillingOption, Addresses1.PackageType,
Addresses1.ServiceType, Addresses1.PackageWeight,
tblImportedOrders.TransactionID
FROM tblImportedOrders INNER JOIN Addresses1 ON
(tblImportedOrders.CustFirstName = Addresses1.FirstName) AND
(tblImportedOrders.CustLastName = Addresses1.LastName)
WHERE (((Addresses1.ShippingAddress)<>'Will Call') AND
((tblImportedOrders.Amount)<>'0.01'));

Reply to this Message

Larry Linson - 28 Sep 2007 22:05 GMT

Idle question: It appears you are accessing a Jet database from Access and
want to do so programmatically. If that is so, why would you want to use
anything other than DAO, the native language of Jet?

Larry Linson
Microsoft Access MVP

Reply to this Message