Passing parameter to Access Querydef ( MDB Path)

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.

Example:

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
Shsiva
shsivaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

shanesuebsahakarnCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shsivaAuthor Commented:
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.
0
shanesuebsahakarnCommented:
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.
0
shsivaAuthor Commented:
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.
0
nexusnationCommented:
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.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

nexusnation
EE Cleanup Volunteer for Microsoft Access
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.