How do I change path 'on the fly'?

  • Thread starter Thread starter Ed
  • Start date Start date
E

Ed

I have a query (below) that I want to change the path from code:

SELECT tblOne.Contract
FROM tblOne IN 'D:\Database.mdb'

Ed
 
You can use DAO code
CurrentDb.QueryDefs("qselYourQueryName").SQL = "SELECT ... FROM tblOne IN '"
& strDbName & "';"
 
The database has a 'password'. How do I add that?
Duane Hookom said:
You can use DAO code
CurrentDb.QueryDefs("qselYourQueryName").SQL = "SELECT ... FROM tblOne IN '"
& strDbName & "';"
Click to expand...
 
Hi Ed,

This is probably more than you want to know.....

so short answer (query to remote db w/pwd):

SELECT tblOne.Contract
FROM tblOne
IN '' <----this is 2 single quotes
[MS Access;PWD=x;DATABASE=D:\Database.mdb];

replace "x" with your password

//////////////////////////////////////
long answer (general sub to open remote db query):

Public Sub fOpenQueryToRemote(pQryName, _
pTable, _
pPath, _
Optional pPwd)
On Error GoTo Err_fOpenQueryToRemote
Dim qdf As DAO.QueryDef
Dim strSQL As String

'check that user has defined queryname, table and path
If Len(Trim(pQryName & "")) > 0 Then
If Len(Trim(pTable & "")) > 0 Then
If Len(Trim(pPath & "")) > 0 Then
'check that path exists
If Nz(Dir(pPath)) <> "" Then
'have a query name, table name and valid path
Else
MsgBox "Please check your path."
Exit Sub
End If
Else
MsgBox "Please provide a path to remote db."
Exit Sub
End If
Else
MsgBox "Please provide a name for the table."
Exit Sub
End If
Else
MsgBox "Please provide a name for the stored query."
Exit Sub
End If

'create new SQL for your stored query

'does remote db have pwd
If IsMissing(pPwd) = True Then
strSQL = "SELECT * FROM " & pTable _
& " IN '" & pPath & "';"
Debug.Print strSQL
Else
'have a db password
'NOTE: 2 single quotes between IN and [
strSQL = "SELECT * FROM " & pTable _
& " IN '' [MS Access;PWD=" & pPwd _
& ";DATABASE=" & pPath & "];"
Debug.Print strSQL
End If
'redefine query
Set qdf = CurrentDb.QueryDefs(pQryName)
qdf.SQL = strSQL
qdf.Close

'now open your stored query with new SQL
'(or what ever you want to do with redefined query
' -- maybe you just want to redefine the SQL)
DoCmd.OpenQuery pQryName, acViewNormal, acEdit


Exit_fOpenQueryToRemote:
Set qdf = Nothing
Exit Sub

Err_fOpenQueryToRemote:
MsgBox Err.Description
Resume Exit_fOpenQueryToRemote
End Sub

Example use:

no pwd:
fopenquerytoremote "qryRemote","tblSource","C:\test2.mdb"

resulting SQL:
SELECT * FROM tblSource IN 'C:\test2.mdb';
--------------
db pwd:
fopenquerytoremote "qryRemote","tblTemp","C:\My Documents\test.mdb","fred"

resulting SQL:
SELECT * FROM tblTemp
IN ''
[MS Access;PWD=fred;DATABASE=C:\My Documents\test.mdb];

Please respond back if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter
 
Back
Top