G
Geezer via AccessMonster.com
I'm working on a split DB that allows some users to create personal queries.
We've established a convention of naming user created queries as "P_somename"
Currently, when I update to a new FE I manually import all of these P_
queries from the replaced version into the new version. I'm trying to come
up with a way to automate the process. We always save the replaced FE in a
specific directory on the network and ammend the name of the file with the
date replaced, I.E. FrontEnd-100507. FrontEnd will always be part of the
name followed by a date. I have a combo for selecting the path to the folder,
one for the network path a work and one for the path on my home computer, and
some code I found that then lists all .mdb files in the selected folder. A
textbox then concatenates the path and selected file name. What I'd like is
the OnClick event of a command button to cycle through the queries in the
selected path/file and import all queries that begin with "P_". The code
below is what I've been trying to adjust to work. It doesn't work. If I
remove everything and use just the DoCmd.TransferDatabase line with a hard
coded query name, that part works.
Can anyone suggest how to cycle through all queries in the selected file and
import all the are named "P_"*"? Thanks for any insight.
Dim dbs As Database, qdf As QueryDef
Set dbs = DBEngine(0)(0)
For Each qdf In dbs.QueryDefs
If Left(qdf.Name, 2) = "P_" Then
DoCmd.TransferDatabase acImport, "Microsoft Access", Forms!
ZTempFrm!TxtFullPath, acQuery, qdf.Name, qdf.Name
End If
Next qdf
Set dbs = Nothing
Watch for word wrap on the DoCmd line, it's actually all on one line.
We've established a convention of naming user created queries as "P_somename"
Currently, when I update to a new FE I manually import all of these P_
queries from the replaced version into the new version. I'm trying to come
up with a way to automate the process. We always save the replaced FE in a
specific directory on the network and ammend the name of the file with the
date replaced, I.E. FrontEnd-100507. FrontEnd will always be part of the
name followed by a date. I have a combo for selecting the path to the folder,
one for the network path a work and one for the path on my home computer, and
some code I found that then lists all .mdb files in the selected folder. A
textbox then concatenates the path and selected file name. What I'd like is
the OnClick event of a command button to cycle through the queries in the
selected path/file and import all queries that begin with "P_". The code
below is what I've been trying to adjust to work. It doesn't work. If I
remove everything and use just the DoCmd.TransferDatabase line with a hard
coded query name, that part works.
Can anyone suggest how to cycle through all queries in the selected file and
import all the are named "P_"*"? Thanks for any insight.
Dim dbs As Database, qdf As QueryDef
Set dbs = DBEngine(0)(0)
For Each qdf In dbs.QueryDefs
If Left(qdf.Name, 2) = "P_" Then
DoCmd.TransferDatabase acImport, "Microsoft Access", Forms!
ZTempFrm!TxtFullPath, acQuery, qdf.Name, qdf.Name
End If
Next qdf
Set dbs = Nothing
Watch for word wrap on the DoCmd line, it's actually all on one line.