MS Access 2007 Remote Query - Can we pass in the folder path?

Hi,

I have several remote queries based on another Access database on another folder.
Is there a way to pass the path  to a remote Query instead of hard code the path?

Thanks,
Luchuan
luchuancAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Chuck WoodConnect With a Mentor Commented:
Luchuan,
Put the snippet function in a code module (other than a form module) and then put this in a field in your query:

Database: DbPath()
Public Function DbPath()
  DbPath = "I:\test\testdb.accdb"
  ' OR
  'DbPath = Forms!frmMyForm.txtDebPath
End Function

Open in new window

0
 
Chuck WoodConnect With a Mentor Commented:
Do you mean you are querying a linked table (in another database) using a query in your database?
0
 
luchuancAuthor Commented:
Hi cwood-wm-com:

Yes, I have the following sql

select ..... from tblPtVisit in "I:\test\testdb.accdb"

and I:\test\testdb.accdb is another MS Access Database.

Thanks,

Luchuan
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Chuck WoodConnect With a Mentor Commented:
I think this is what you need:
Private Function CreateSQL(ByRef strSQL As String, ByVal strDbPath As String)
  strSQL = "SELECT * FROM tblPtVisit IN " & strDbPath
End Function

Open in new window

0
 
luchuancAuthor Commented:
Hi cwood-wm-com:

Good idea, is there a way to have the query call this function?
(It seems the Query tool does not recognize the VBA function, is this true)

Thanks,

Luchuan
0
 
luchuancAuthor Commented:
Thanks a lot cwood-wm-com.
I finally got time to try your idea, super!


Luchuan
0
 
Chuck WoodCommented:
You are welcome. I am glad it worked for you. Good luck on your project.
0
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.

All Courses

From novice to tech pro — start learning today.