pass parameter to access query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

souris - 23 Nov 2004 03:21 GMT

I have a external database to link MS Access Query.
I can run the query from Excel, but I need pass the start date and end date
to the query.

Are there any way to pass MS Access Query parameter from Excel Spreadsheet?

Any information is great appreciated.

Inung

Reply to this Message

Bill Manville - 23 Nov 2004 10:04 GMT

Souris wrote:

> Are there any way to pass MS Access Query parameter from Excel Spreadsheet?

Yes, but it is a bit tricky to do.

You would be best to use a database query created from within Excel rather
than a link to the Access database query's results, since I don't know of a
way to pass parameters to Access directly.

I would record a macro while using Data / Import External Data / New Database
Query to extract some data from the database (doesn't matter what).

Then stop the recorder.

In Access, use View / SQL in query design mode to see the SQL text of your
query and replace any newlines within it by spaces.

In Excel, Tools / Macro / Macros / Macro1 (or the newly recorded macro's
name) / Edit

The recorded code will look horrible, something like

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access
Database;DBQ=C:\SomeDir\MyDB.mdb;DefaultDir=C:\SomeDir;DriverId=25;FIL=MS
Acce" _
), Array("ss;MaxBufferSize=2048;PageTimeout=5;")),
Destination:=Range("A1"))
.CommandText = Array( _
"SELECT zqParamtest.IdentNr, Sum(zqParamtest.StoredVolume) AS 'Sum of
StoredVolume', Sum(zqParamtest.ConditionedVolume) AS 'Sum of
ConditionedVolume', Sum(zqParamtest.PackagedVolume) AS 'Sum of Package" _
, _
"dVolume'" & Chr(13) & "" & Chr(10) & "FROM
`C:\SomeDir\MyDB.mdb`.zqParamtest zqParamtest" & Chr(13) & "" & Chr(10) &
"GROUP BY zqParamtest.IdentNr" _
)
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

Delete the lines starting with '.CommandText = ' down to the line above the
one beginning '.Name = '. Replace them with
.CommandText = ""
Then paste your Access query text between the ""

Go to a new worksheet and rerun the macro.
It should ask you for the parameters and then execute the query, returning
the data into the worksheet.

Then, you can refresh the query specifying different parameters by Data /
Refresh Data (with the cursor in the query results).

Using Data / Import External Data / Parameters you can specify that you want
the parameters to be taken from worksheet cells if you wish.

Hope you make it through all this.
If you have trouble, post the text of the query you are trying to run.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Reply to this Message

souris - 24 Nov 2004 04:55 GMT

Thanks for the information,

Inung Huang

> Souris wrote:
>> Are there any way to pass MS Access Query parameter from Excel
[quoted text clipped - 76 lines]
> MVP - Microsoft Excel, Oxford, England
> No email replies please - respond to newsgroup

Reply to this Message

Jamie Collins - 24 Nov 2004 10:04 GMT

> > Are there any way to pass MS Access Query parameter from Excel Spreadsheet?
>
[quoted text clipped - 3 lines]
> than a link to the Access database query's results, since I don't know of a
> way to pass parameters to Access directly.

Create a stored procedure (parameterized stored Query) in the database e.g.

CREATE PROCEDURE BillsProc
(arg_StoredVolume INTEGER)
AS
SELECT
IdentNr,
Sum(StoredVolume) AS 'Sum of StoredVolume',
Sum(ConditionedVolume) AS 'Sum of ConditionedVolume',
Sum(PackagedVolume) AS 'Sum of PackagedVolume
FROM
zqParamtest
WHERE StoredVolume >= arg_StoredVolume
GROUP BY IdentNr
;

Call the procedure using ODBC's CALL syntax e.g.

{CALL BillsProc 1}

Jamie.

--

Reply to this Message

Bill Manville - 24 Nov 2004 16:51 GMT

> Create a stored procedure (parameterized stored Query) in the database

In an Access database?

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Reply to this Message

Jamie Collins - 25 Nov 2004 09:07 GMT

> > Create a stored procedure (parameterized stored Query) in the database
>
> In an Access database?

Indeed. The animal is known to the world via the ANSI SQL standards as
a stored PROCEDURE. MS Access users seem to shun the standard terms; I
don't speak their idiolect myself so 'parameterized stored Query' is
my best attempt. The PROCEDURE DDL syntax was added to the latest
version of Jet being Jet 4.0 and has been the native MS Access Jet
format since Access2000. However, while they didn't call it a
PROCEDURE, the animal *did* exist in earlier Jet versions e.g.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/office97/html/o
utput/F1/D2/S5A319.asp

You can even execute the Jet 4.0 DDL syntax via MSQuery, assuming your
.mdb file format is Jet 4.0 (Access2000 or above) and you are using a
corresponding driver e.g. via Excel2000 and above. Give it a try <g>.

Jamie.

--

Reply to this Message

Bill Manville - 25 Nov 2004 10:55 GMT

> Create a stored procedure (parameterized stored Query) in the database

Thanks for sharing that; I hadn't realised that Access had a PROCEDURE
syntax.

I couldn't get the syntax you gave to work in the Access query design
SQL window, or via DAO using CurrentDB.Execute, or in the MSQuery SQL
window (syntax error in CREATE TABLE statement, in each case).

With the help of the msdn article you pointed me to I found the
following worked in the Access query design SQL window:

PROCEDURE MyProc [argSiteID] INTEGER;
SELECT * FROM Sites WHERE SiteID=argSiteID

However, this same SQL didn't work in the MSQuery SQL window
(Too few parameters, Expected 1).

Nor did SELECT * FROM zzProcTest work via MSQuery
(zzPtocTest being the name I saved the above procedure with)
- same error.

Perhaps I am missing something?

I am using an Access 2002-format database from Access 2002 and Excel
2002.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Reply to this Message

Jamie Collins - 25 Nov 2004 16:10 GMT

> Thanks for sharing that; I hadn't realised that Access had a PROCEDURE
> syntax.
>
> I couldn't get the syntax you gave to work in the Access query design
> SQL window, or via DAO using CurrentDB.Execute, or in the MSQuery SQL
> window (syntax error in CREATE TABLE statement, in each case).

DAO (all versions) is too 'retro' for the syntax. Access2003 is the
first version to be able to use the syntax natively but only when
using an Access2003 format 'ANSI mode' database. Now I think about it,
the PROCEDURE syntax was never going to work in MS Query because it
uses ODBC drivers; apologies for the red herring. That leaves ADO:

Sub test()
Dim cat As Object
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Bill.mdb"
With .ActiveConnection
.Execute "CREATE TABLE Test (A INTEGER NOT NULL, B INTEGER NOT
NULL);"
.Execute "INSERT INTO Test VALUES (1,11);"
.Execute "INSERT INTO Test VALUES (2,22);"
.Execute "CREATE PROCEDURE TestProc (C INTEGER) AS" & _
" SELECT B FROM Test WHERE A=C;"
.Execute "CREATE PROCEDURE TestProc2 (C INTEGER, D INTEGER) AS" &
_
" UPDATE Test SET B=D WHERE A=C;"
Dim rs As Object
Set rs = .Execute("EXEC TestProc 1")
MsgBox rs.GetString
.Execute ("EXEC TestProc2 1,55")
Set rs = .Execute("EXEC TestProc 1")
MsgBox rs.GetString
End With
.ActiveConnection = Nothing
End With
End Sub

> SELECT * FROM zzProcTest

Wrong syntax. A procedure must be called/executed. A procedure is not
a query as such; it does not necessarily return a rowset e.g. see
TestProc2 above. From MS Query, you can either use ODBC's CALL syntax
which MS Query recognizes, or use Jet's EXECUTE syntax which MSQuery
won't recognize so will pass to the database to handle.

Jamie.

--

Reply to this Message

Bill Manville - 25 Nov 2004 17:09 GMT

Thanks for the further clarification.

The original poster didn't specify what Office version - probably not
2003, but it is useful to know that this is an option in Access 2003.

Even then, if I understand correctly, there is no way via Excel's query
mechanism to execute a stored procedure on the Access database so code
would be needed. My original solution used a throw-away macro for the
purpose of getting the QueryTable set up but thereafter refreshing the
query would be code-free.

So I think my original answer stands, but thanks for the educational
journey!

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Reply to this Message

Jamie Collins - 29 Nov 2004 08:38 GMT

> if I understand correctly, there is no way via Excel's query mechanism
> to execute a stored procedure on the Access database so code
> would be needed.

No, that is incorrect (apologies for this point being lost in
discussion of *creating* procedures).

You can most definitely *execute* a Jet (MS Access) stored procedure
(stored Query) using MS Query in Excel without VBA code.

As I said in my last post, there are two ways to execute the
procedure:

1) Use ODBC syntax. In the MS Query SQL window, type:

{CALL TestProc2 1,55}

MS Query is based on ODBC (e.g. the wizard only gives you a list of
ODBC drivers) so it will recognise the statement within the curly
brackets and call the procedure.

2) Use Jet's EXECUTE syntax. In the MS Query SQL window, type:

EXEC TestProc2 1,55

MSQuery won't recognize the syntax so it won't try and interpret it on
the client side. Instead, it will simply send the statement to the
database and see what happens. In this case, the database recognises
the syntax and calls the procedure.

Jamie.

--

Reply to this Message

Bill Manville - 29 Nov 2004 14:58 GMT

> You can most definitely *execute* a Jet (MS Access) stored procedure
> (stored Query) using MS Query in Excel without VBA code.

OK.
Thanks for the clarification.
I must give it a whirl.
Pity my clients are all 2002 at latest....but one day...

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Reply to this Message

Jamie Collins - 30 Nov 2004 08:57 GMT

Bill Manville <Bill-Manville@msn.com> wrote in message

> Pity my clients are all 2002 at latest

Why?

You can *create* the procedure within MS Access using the PROCEDURE
syntax e.g. I just tested your example

PROCEDURE MyProc [argSiteID] INTEGER;
SELECT * FROM Sites WHERE Sites=argSiteID

in Access95 and it works fine and will work for all subsequent
versions of MS Access.

You can *call* the procedure within MS Query using ODBC's call syntax.
Again, tested fine in Excel95 and above.

Jamie.

--

Reply to this Message