Passing parameter to Access Querydef ( MDB Path)

Posted on 2003-03-03
Medium Priority
Last Modified: 2012-06-21
Hi All,

   I have a querydef which pulls data from another MDB. So my query will pull some data from local MDB and some from external MDB. When I pass the external MDB path to a select query my query seems to be working fine. But I have pass the external MDB path as a paramter. Any one had any idea how can I pass the path as a paramter to IN clause of Select query.
Note: I can pass parameter to where clause but not IN clause.


Select * from table In "C:\Test\Access.MDB" Where col =  Param

should be
Select * from table In MDB_PATH Where col =  Param

Thanks in Advance
Question by:shsiva
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 41

Accepted Solution

shanesuebsahakarn earned 100 total points
ID: 8058094
I would use a linked table in this case. You can use some code like this to create a link to the table before running your query:

DoCmd.DeleteObject acTable,"MyTable"
DoCmd.TransferDatabase acLink, "Microsoft Access", strMDB, acTable, "MyExternalTable", "MyTable"
DoCmd.OpenQuery "MyQuery"

where strMDB is the mdb from which you want to import the data.

Your query would then just read:
SELECT * FROM MyTable WHERE col = Param

Another option would be to alter the SQL of the querydef directly by retrieving the .SQL property and modifying it to include the path of the table.

Author Comment

ID: 8058532
In fact I am supposed to execute the QueryDef from ADO.net using command object.

According to you I can aswell have link table but i need to refresh the link table for every new path. Is there a way to refresh the link table dynamically using ADO.NET. I know we can do this using DAO using table object.

I appreciate your advice.
LVL 41

Expert Comment

ID: 8059598
I'm not familiar with ADO.NET. However, I've recently read that in Access 2000 and later, it is advisable to delete and re-create the link rather than refreshing it, since more information is cached, and simply refreshing the link does not always delete the cached information. Whether or not it applies in this particular situation, I don't know.

Author Comment

ID: 8059718
You r right, thats when u used DAO to access MS Access database you can create or refresh link tables. As the table or querydef objects are not available in ADO.Net you can not create a link table from ADO.net.

I am still looking for a anwer to my question.
LVL 12

Expert Comment

ID: 8784108
Hi shsiva,
This question has been abandoned and needs to be finalized (112 days since last comment).

   You can accept an answer, split the points, or get a refund. Go to
   http://www.cityofangels.com/Experts/Closing.htm for information and options.


EE Cleanup Volunteer for Microsoft Access

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question