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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 379
  • Last Modified:

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
0
luchuanc
Asked:
luchuanc
  • 4
  • 3
3 Solutions
 
Chuck WoodCommented:
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
 
Chuck WoodCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
Chuck WoodCommented:
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
 
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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now