Brandon M said:
I have a database which pulls information from an external database. This
external database is not always in the same place. I have tried everything I
can think of to get the FROM field in a SQL query to refer to a vb constant,
a value in a table, etc. and I can not get it to work. Anybody know how this
is done?
Hi Brandon,
I wrote this module for "Spammaster" a while back
(he did not reply back so I assume it works). Maybe
it will help.
The name of the table and path to the remote db
were in text boxes on a form, but you should be
able to adapt to your situation I believe.
Private Sub cmdOpenQueryToRemote_Click()
On Error GoTo Err_cmdOpenQueryToRemote_Click
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strQryName As String
Dim varTable As Variant
Dim varPath As Variant
'get table and path to remote db
varTable = Me!txtTable
varPath = Me!txtPath
'check that user has defined table and path
If Len(Trim(varTable & "")) > 0 Then
If Len(Trim(varPath & "")) > 0 Then
'check that path exists
If Nz(Dir(varPath)) <> "" Then
'have a table name and valid path
Else
MsgBox "Please check your path."
Exit Sub
End If
Else
MsgBox "Please enter a path to remote db."
Exit Sub
End If
Else
MsgBox "Please enter a name for the table."
Exit Sub
End If
'name of your stored query
strQryName = "qryTemp"
'create new SQL for your stored query
strSQL = "SELECT * FROM " & varTable _
& " IN '" & varPath & "';"
'Debug.Print strSQL
'redefine query
Set qdf = CurrentDb.QueryDefs(strQryName)
qdf.SQL = strSQL
qdf.Close
'now open your stored query with new SQL
DoCmd.OpenQuery strQryName, acViewNormal, acEdit
Exit_cmdOpenQueryToRemote_Click:
Set qdf = Nothing
Exit Sub
Err_cmdOpenQueryToRemote_Click:
MsgBox Err.Description
Resume Exit_cmdOpenQueryToRemote_Click
End Sub
Please respond back if something is not clear.
Good luck,
Gary Walter
