access querries in excel

[Guest]

hi everybody

what is the best way to return a query result in access to excel worksheet
from excel (not from Access). I also would like to ask if i can use an excel
form to define criteria for access parameter querries.

Thank you

 

[peregenem]

what is the best way to return a query result in access to excel worksheet
from excel (not from Access).

An ADO recordset with Excel's CopyFromRecordset is a popular choice.

I also would like to ask if i can use an excel
form to define criteria for access parameter querries.

The ADOX Command method is useful because it creates the correct
Parameter objects; you just have to supply the parameter values, which
can come from a UserForm or anywhere. This example uses Northwind

Sub tester()

Dim Cat As Object
Set Cat = CreateObject("ADOX.Catalog")
With Cat
.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data source=C:\Program Files\Microsoft Visual
Studio\VB98\NWIND.mdb"

Dim cmd As Object
Set cmd = _
.Procedures("Employee Sales by Country").Command
End With

With cmd
.Parameters("Beginning Date").Value = _
DateSerial(1994, 8, 1)
.Parameters("Ending Date").Value = _
DateSerial(1994, 8, 15)

Dim rs As Object
Set rs = .Execute

ThisWorkbook.Worksheets(1).Range("A1").CopyFromRecordset rs

End With

End Sub

 

[Duane Hookom]

I use MSQuery to pull records. You can set up criteria based on values in
cells. There is absolutely no code required with this method.

 

[peregenem]

I use MSQuery to pull records. You can set up criteria based on values in
cells. There is absolutely no code required with this method.

For 'parameter queries'? How, for example, do you pass paramter values
to 'Employee Sales by Country' in Northwind?

 

[Duane Hookom]

MSQuery allows you to set criteria. This isn't done in Access. The criteria
can reference a cell in the worksheet.

 

[peregenem]

MSQuery allows you to set criteria. This isn't done in Access. The criteria
can reference a cell in the worksheet.

Did you test e.g. using 'Employee Sales by Country' in the Access/Jet
Northwind test database?

In my experience, 'Criteria' will not help you run a Access/Jet
Parameter Query (big Q) from MSQuery in Excel.

Here's what I tried

1. Open a workbook using the Excel app.
2. Choose: Data, Import External Data, New Database Query.
3. Choose a connection to Northwind (I'm using a DSN).
4. MSQuery starts with its 'Add Tables' dialog displayed.
5. Hit the Options and ensure 'Views' is checked then hit OK.
6. Back in the 'Add Tables' dialog, select the Parameter Query
'Employee Sales by Country', hit 'Add' then 'Close'.
7. In the 'Tables' pane of the main MSQuery window, choose one of the
columns (or * for all columns) by double clicking.
8. See the error message, 'Too few parameters. Expected 2'.

The problem here is that 'Employee Sales by Country' is a PROCEDURE,
rather than a VIEW. So rather than use SELECT, which is all MSQuery
'knows', you must EXECUTE the proc

9. From the MSQuery menu, choose: View, SQL.
10. Type (or paste) the following SQL (Jet syntax)

EXECUTE [Employee Sales by Country] #1994-08-01#, #1994-08-15#

11. Hit OK when warned 'SQL Query can't be represented graphically'.
12. The resultset of the proc call is shown in the main MSQuery window.

The 'can't be represented graphically' is significant because when you
see this you have confirmation that 'Criteria' support has been lost.
This means that to change the parameters to reference a worksheet,
UserForm, etc you must use code.

 

[Duane Hookom]

I don't generally use dynamic criteria (parameters) in my queries. This
suggests that I would not get the error message in #8 below. This method
also allows me to set the criteria as I want from within Excel.

--
Duane Hookom
MS Access MVP

MSQuery allows you to set criteria. This isn't done in Access. The
criteria
can reference a cell in the worksheet.

Did you test e.g. using 'Employee Sales by Country' in the Access/Jet
Northwind test database?

In my experience, 'Criteria' will not help you run a Access/Jet
Parameter Query (big Q) from MSQuery in Excel.

Here's what I tried

1. Open a workbook using the Excel app.
2. Choose: Data, Import External Data, New Database Query.
3. Choose a connection to Northwind (I'm using a DSN).
4. MSQuery starts with its 'Add Tables' dialog displayed.
5. Hit the Options and ensure 'Views' is checked then hit OK.
6. Back in the 'Add Tables' dialog, select the Parameter Query
'Employee Sales by Country', hit 'Add' then 'Close'.
7. In the 'Tables' pane of the main MSQuery window, choose one of the
columns (or * for all columns) by double clicking.
8. See the error message, 'Too few parameters. Expected 2'.

The problem here is that 'Employee Sales by Country' is a PROCEDURE,
rather than a VIEW. So rather than use SELECT, which is all MSQuery
'knows', you must EXECUTE the proc

9. From the MSQuery menu, choose: View, SQL.
10. Type (or paste) the following SQL (Jet syntax)

EXECUTE [Employee Sales by Country] #1994-08-01#, #1994-08-15#

11. Hit OK when warned 'SQL Query can't be represented graphically'.
12. The resultset of the proc call is shown in the main MSQuery window.

The 'can't be represented graphically' is significant because when you
see this you have confirmation that 'Criteria' support has been lost.
This means that to change the parameters to reference a worksheet,
UserForm, etc you must use code.

 

[peregenem]

I don't generally use dynamic criteria (parameters) in my queries. This
suggests that I would not get the error message in #8 below. This method
also allows me to set the criteria as I want from within Excel.

OK, so for the OP's benefit, going back to my earlier question, "Do you
recommend MSQuery in Excel for Access parameter queries?", have you now
revised your answer to "No"?

 

[Duane Hookom]

My opinion is to not use parameter queries. If you do use parameter queries
and want to pull from the parameter query then you can use a load of code as
you suggested.

I'm not sure why any would just not remove the parameters which maybe
shouldn't be there in the first place and then use MSQuery and Excel
functionality without any code.

I rarely use criteria in Access queries whether the criteria is a parameter
prompt (shudder) or a reference to a control on a form (slightly less
shudder)..

 

[peregenem]

My opinion is to not use parameter queries.

Van T. Dinh went slient on me on this issue, so I'll try you. Bearing
in mind that a 'parameter Query' in Access/Jet is known as a 'stored
procedure' in the wider SQL world (you can even use the standard SQL
CREATE PROCEDURE to create a 'parameter Query' using the Jet OLE DB
provider), why is it that while the rest of the SQL world actively
promote the use of stored procs the regulars here go, 'Shudder'?

 

[Duane Hookom]

I certainly promote stored procedures with parameters in SQL Server. You can
call an SQL Server stored procedures with the parameters like:
Exec spMyStoredProcedure '4/1/2005' , '12/31/2005', 'Female',4
You can't do the same with an Access query.

 

[peregenem]

I certainly promote stored procedures with parameters in SQL Server. You can
call an SQL Server stored procedures with the parameters like:
Exec spMyStoredProcedure '4/1/2005' , '12/31/2005', 'Female',4
You can't do the same with an Access query.

You are mistaken. I posted an example earlier in this thread which uses
the EXEC (or EXECUTE) syntax to call a Access/Jet parameter Query (a
Jet PROCEDURE by another name) e.g.

EXEC [Employee Sales by Country] #1994-08-01#, #1994-08-15#

Your misstatement aside, don't you find it curious that a
syntax/approach approved of for SQL Server is not similarly advocated
for Access?

 

[Duane Hookom]

peregenem,
Thanks for the enlightenment. I just went back through your example and
learned something new. I'm not sure how useful this would be since it would
require code to modify the SQL. A saved query with no parameters would allow
the developer to setup criteria however they pleased with no coding
required.

The difference between promotion of this approach between Access and SQL
Server doesn't surprise me.

--
Duane Hookom
MS Access MVP

I certainly promote stored procedures with parameters in SQL Server. You
can
call an SQL Server stored procedures with the parameters like:
Exec spMyStoredProcedure '4/1/2005' , '12/31/2005', 'Female',4
You can't do the same with an Access query.

You are mistaken. I posted an example earlier in this thread which uses
the EXEC (or EXECUTE) syntax to call a Access/Jet parameter Query (a
Jet PROCEDURE by another name) e.g.

EXEC [Employee Sales by Country] #1994-08-01#, #1994-08-15#

Your misstatement aside, don't you find it curious that a
syntax/approach approved of for SQL Server is not similarly advocated
for Access?

 

[peregenem]

I'm not sure how useful this would be since it would
require code to modify the SQL.

'Parameter Queries' are a powerful way of implementing business logic
in the database.

For example, I often see posts about ensuring an edited_date column is
kept updated with the current date/time.

One approach is to revoke UPDATE permissions on the table, create a
'parameter Query' (i.e. stored proc) with appropriate parameters
containing a generic UPDATE statement, including the DATE() or NOW()
function to maintain the current date/time and WITH OWNERACCESS OPTION
for the required permissions on the table, then grant SELECT
permissions on the 'parameter Query'. In other words, ensure the only
way the *application* developer may update the table is by calling the
'parameter Query' for which the *database* developer has control over
how the edited_date column is updated.

The trouble is, I often see replies about how to embed the UPDATE SQL
with the date/time function in the front end Access Forms application
with no constraints in the database. Then I see posts asking how to
disable MSQuery in Excel so that users cannot update the table without
using the Access form which is *supposed* to be controlling the
edited_date column...

A saved query with no parameters would allow
the developer to setup criteria however they pleased with no coding
required.

Such a Query is know as a VIEW as has similar usage i.e. controlling
the way users may see the data. I often (but perhaps not as often as I
should <g>) design databases where users have no permissions on the
base tables; instead they must use procs to manipulate the data and
procs/views to view the data. Yes, this may be a bar to Excel users who
cannot call the procs in MSQuery however they can query the views and
typically means they have the ability to 'read' but not to 'write',
which may not be such a bad thing in practise.

 

[Duane Hookom]

Great information!