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. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
WORD XP mail-merge FAILS using ACCESS Query | SueMackay | Mailmerge | 1 | November 23rd, 2004 01: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 |