Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Passing parameter to Access Querydef ( MDB Path)

Posted on 2003-03-03
5
Medium Priority
?
738 Views
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.

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
0
Comment
Question by:shsiva
  • 2
  • 2
5 Comments
 
LVL 41

Accepted Solution

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

Author Comment

by:shsiva
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.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
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.
0
 

Author Comment

by:shsiva
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.
0
 
LVL 12

Expert Comment

by:nexusnation
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.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

nexusnation
EE Cleanup Volunteer for Microsoft Access
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

580 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