Make Table Query 1

[matrec]

I have all my tables in DB.mdb and my reports, queries and forms in MP.mdb .
I have a Make Table query qryMT in MP.mdb and want the Another Database File Name, where the new table will be created, to be obtained from a field fileName in tblArea in DB.mdb . Are there any suggestions how to achieve this. I have tried a few modifications in the SQL View of the query but was unsuccesful.
Rgds

 

[VBslammer]

Try linking tblArea to the MP database, then you can look up the filename locally with DLookup():

Function MakeForeignTable(ByVal SrcTable As String, ByVal DestTable As String, _
                          Optional ByVal Pwd As String = "") As Boolean
On Error GoTo ErrHandler
  Dim strSQL As String
  Dim strPath As String

  strPath = Nz(DLookup("[fileName]", "tblArea"), "")
  strSQL = "SELECT * INTO [MS Access;Database={%1};{%2}].[{%3}] FROM [{%4}];"
  
  If Len(Pwd) > 0 Then
    Pwd = "PWD=" & Pwd & ";"
  End If

  If Len(strPath) > 0 Then
    If dir(strPath) <> "" Then
      strSQL = Replace(strSQL, "{%1}", strPath)
      strSQL = Replace(strSQL, "{%2}", Pwd)
      strSQL = Replace(strSQL, "{%3}", DestTable)
      strSQL = Replace(strSQL, "{%4}", SrcTable)
      DoCmd.RunSQL strSQL
      MakeForeignTable = True
    End If
  End If
ExitHere:
  Exit Function
ErrHandler:
  If Err = 3031 Then 'invalid password
    MsgBox "The password you supplied was invalid", vbCritical
  Else
    Debug.Print Err, Err.Description
    Resume ExitHere
  End If
End Function

If you call this function with this:
[tt]
MakeForeignTable("Cities", "Cities")
[/tt]
The SQL looks like this (path depends on DLookup function):
[tt]
SELECT * INTO [MS Access;Database=G:\Databases\db2.mdb;].[Cities] FROM [Cities];
[/tt]

VBSlammer

"You just have to know which screws to turn." - Professor Bob