Learn Parameters for Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Learn Parameters for Query

Author

Topic

iwrk4dedpr
Starting Member

4 Posts

Posted - 2007-10-11 : 09:27:26

SQL Team,

Not sure where this should go but the databases are Access. I front end my databases from Excel. Using VBA I run queries to pull data from various databases.

Many of my queries have parameters so that I can pass values and alter the data that is returned. I'm really wanting to create a master routine that will perform all my querying.

My thoughts on this are as follows. The routine is passed a connection to a database, the name of a query, and a string/array of key/value combinations. I suppose that if I know to pass the values that I all ready know what the query is doing. However, I'd like to be able to verify that I've sent in enough values to cover all the parameters.

So from Excel, using ADO objects and VBA, is it possible to identify the required parameters to a query?

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2007-10-12 : 00:26:21

Hi

assuming you're talking about a JET database, not sure how you do it in ADO, but there may be away.

However, if you can include the DAO 3.x library in your Excel project, then you can do it as follows:

Dim db as Database
Dim qdf as Querydef
Dim p as Parameter

Set db = OpenDatabase( path bla bla bla )
Set qdf = db.Querydefs( name of query with parameters )

For each p in qdf.Parameters
   Debug.Print p.Name
Next

Set p = Nothing
Set qdf = Nothing
Set db = Nothing

--
I'm not schooled in the science of human factors, but I suspect that surprise is not an element of a robust user interface.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources