A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Links and Linking
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

pass parameter to access query



 
 
Thread Tools Display Modes
  #1  
Old November 23rd, 2004, 04:21 AM
souris
external usenet poster
 
Posts: n/a
Default pass parameter to access query

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


  #2  
Old November 23rd, 2004, 11:04 AM
Bill Manville
external usenet poster
 
Posts: n/a
Default

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(Arra y( _
"ODBC;DSN=MS Access
Database;DBQ=C:\SomeDir\MyDB.mdb;DefaultDir=C:\Som eDir;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

  #3  
Old November 24th, 2004, 05:55 AM
souris
external usenet poster
 
Posts: n/a
Default

Thanks for the information,

Inung Huang

"Bill Manville" wrote in message
...
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(Arra y( _
"ODBC;DSN=MS Access
Database;DBQ=C:\SomeDir\MyDB.mdb;DefaultDir=C:\Som eDir;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



  #4  
Old November 24th, 2004, 11:04 AM
Jamie Collins
external usenet poster
 
Posts: n/a
Default

Bill Manville 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.


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.

--
  #5  
Old November 24th, 2004, 05:51 PM
Bill Manville
external usenet poster
 
Posts: n/a
Default

Jamie Collins wrote:
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

  #6  
Old November 25th, 2004, 10:07 AM
Jamie Collins
external usenet poster
 
Posts: n/a
Default

Bill Manville wrote ...

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/de.../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.

--
  #7  
Old November 25th, 2004, 11:55 AM
Bill Manville
external usenet poster
 
Posts: n/a
Default

Jamie Collins wrote:
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

  #8  
Old November 25th, 2004, 05:10 PM
Jamie Collins
external usenet poster
 
Posts: n/a
Default

Bill Manville wrote ...

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.

--
  #9  
Old November 25th, 2004, 06:09 PM
Bill Manville
external usenet poster
 
Posts: n/a
Default

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

  #10  
Old November 29th, 2004, 09:38 AM
Jamie Collins
external usenet poster
 
Posts: n/a
Default

Bill Manville wrote ...

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
procedu

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.

--
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
WORD XP mail-merge FAILS using ACCESS Query SueMackay Mailmerge 1 November 23rd, 2004 02:03 PM
Here's a shocker Mike Labosh General Discussion 2 October 26th, 2004 05:04 PM
Merging with an Access 2002 parameter query Trevor Running & Setting Up Queries 4 August 31st, 2004 01:25 PM
parameter query from data access page Alice Dawson New Users 0 August 12th, 2004 12:37 AM
Display Parameter from Form on Report sara Setting Up & Running Reports 10 July 19th, 2004 04:54 PM


All times are GMT +1. The time now is 11:27 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.