Target database as variable in make table query?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I use a make table query to make a backup of serveral databases. The path
where the backup will be created can be changed by the user. Is there a way
to make the query put the table in the path the user has entered (so the
target database is a variable). I tryed this by setting a functionreference
in the 'target database' field like 'Targetdir("BackupDatabaseName")' but
then I get a error that the target database / path doesn't exist.
 
You can either prompt the user for the table name (probably not what you want
to do) or you will have to programmatically modify the query.
All queries are stored as SQL strings, so you can read a query's SQL into a
string variable, modify it, and save it back.
My usual way to do this is have template query that is always the same, so I
can load it, make the changes, and save it to a different name that I will
actually use.

Dim strSQL As String
Dim qdf As QueryDef
Dim qdfXl As QueryDef
Dim dbf As DAO.Database
Dim qdfs As QueryDefs

Set dbf = CurrentDb
Set qdfs = dbf.QueryDefs
'Get the template query
Set qdfXl = CurrentDb.QueryDefs(strXlQuery)
strSQL = qdfXl.SQL

'Delete the old query in case an error left it hanging
For Each qdf In qdfs
If qdf.Name = "_BPOTemp" Then
qdfs.Delete qdf.Name
Exit For
End If
Next qdf
'Here you will need to know what to replace in the template
strSQL = Replace(strSQL, "FROM DummyName", "FROM " & strRealTableName)

Set qdf = dbf.CreateQueryDef("_TempQry", strSQL)

' Now _TempQry can be used like any stored query.
 
This is not exactly what I meant, but it is a good alternative I am going to
try.

Thanks Klatuu
 
An other question Klatuu. How can I prompt the user for a table name? Because
when I can prompt the user I should also could past a table new throught a
variable

Thanks again.
 
Back
Top