Running custom Excel query for Access

[squeekybobo]

Hello,

I'm using Excel to bring back info held in an access DB - I need the
user to be able to define any one of about 4 parameters to bring back
data. I have been using MS-Query, but it won't let me use Nz (so if a
parameter is left blank, it looks for blanks grr)...

After trawling on these groups I found the following:

Sub GetQueryDef()

Dim Db As Database
Dim Qd As QueryDef
Dim Rs As Recordset
Dim Ws As Object
Dim Path As String

Path = "C:\database.mdb"

'Set Ws
Set Ws = Sheets("Sheet1")

'Set the Database and QueryDef. This QueryDef exists in the database.
Set Db = Workspaces(0).OpenDatabase(Path, ReadOnly:=True)
Set Qd = Db.QueryDefs("Test")
Qd.Parameters(Country) = UK
Qd.Parameters("DataRun") = "2004"
Qd("Sector") = "Chemicals"

This comes up with an "Item not found" error when run. The parameters
in the Access query "Test" are [Country],[DataRun] and [Sector]
respectively..

If anybody can let me know why this doesn't work, and how to fix it -
I would be vry grateful! I'm fairly experienced in normal Excel VBA,
but all this databasey stuff is very new to me...

many many thanks!
Tom

 

[Brendan Reynolds]

That would be ...

Qd.Parameters("[Country]") = "UK"
Qd.Parameters("[DataRun]") = "2004"
Qd.Parameters("[Sector]") = "Chemicals"

Note that the parameter names are in quotes and include the square brackets.
This also assumes that the data type of all three parameters is Text. If the
[DataRun] parameter is numeric, leave out the quotes around "2004".

BTW: The NZ() function can only be used within Access, but you can get the
same result using the IIf() function, which will work when the query is used
outside of Access, e.g. IIf([SomeField] Is Null, "", [SomeField]).

 

[David Lloyd]

Tom:

I don't know the data types of your parameters, however, I do have a couple
comments. Regarding the line:

Qd.Parameters(Country) = UK

You should put quotes around "Country" as you do when referencing the other
parameters. Also, if Country is a text field you should probably put "UK"
in quotes as well, again as you did with you other parameter values.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Hello,

I'm using Excel to bring back info held in an access DB - I need the
user to be able to define any one of about 4 parameters to bring back
data. I have been using MS-Query, but it won't let me use Nz (so if a
parameter is left blank, it looks for blanks grr)...

After trawling on these groups I found the following:

Sub GetQueryDef()

Dim Db As Database
Dim Qd As QueryDef
Dim Rs As Recordset
Dim Ws As Object
Dim Path As String

Path = "C:\database.mdb"

'Set Ws
Set Ws = Sheets("Sheet1")

'Set the Database and QueryDef. This QueryDef exists in the database.
Set Db = Workspaces(0).OpenDatabase(Path, ReadOnly:=True)
Set Qd = Db.QueryDefs("Test")
Qd.Parameters(Country) = UK
Qd.Parameters("DataRun") = "2004"
Qd("Sector") = "Chemicals"

This comes up with an "Item not found" error when run. The parameters
in the Access query "Test" are [Country],[DataRun] and [Sector]
respectively..

If anybody can let me know why this doesn't work, and how to fix it -
I would be vry grateful! I'm fairly experienced in normal Excel VBA,
but all this databasey stuff is very new to me...

many many thanks!
Tom

 

[squeekybobo]

Thanks for the tip about the quotes - it's still coming back with the
"Item not found in this collection" error. Does the "Country" ref in
the parameter statement refer to the name of the field, or the Text
within the [] brackets on the query?

Qd.Parameters("Country") = "UK"

Cheers,

Tom

 

[David Lloyd]

Tom:

If I understand you correctly, it is generally the text within the []
brackets. You can right-click in the query designer and select "Parameters"
(also available from the Query menu) to define your parameters for the
query. Whatever name you give the parameters in this dialog box is the name
you should use to reference them both in the query itself and in the VBA
code.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Thanks for the tip about the quotes - it's still coming back with the
"Item not found in this collection" error. Does the "Country" ref in
the parameter statement refer to the name of the field, or the Text
within the [] brackets on the query?

Qd.Parameters("Country") = "UK"

Cheers,

Tom

 

[squeekybobo]

Thanks David! Got it working now, the qd.parameters text does refer to
the text in []....
Cheers,

Tom