S
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
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