creating a backup table in a linked database using a command button on a form

  • Thread starter Thread starter Kennedy
  • Start date Start date
K

Kennedy

I am trying to create a button on a form that will make a
backup copy of a table whose location is supposed to be in
a different dbase(db_data). I need help! currently the
table is being backedup in the "root" dbase (db_form)

Private Sub Command69_Click()
'test why this won't backup properly!!!
Dim stDocName_data As String
Dim stDocName_NewTable As String
Dim stDocName_xxxTable As String
Dim strPath As String
Dim db_form As Database
Dim db_data As Database
Dim sqlstr2 As String, sqlstr22 As String
Dim myquery2 As QueryDef

'Set db_form = CurrentDb
Set db_data = DBEngine.Workspaces(0).OpenDatabase
("C:\biochem\BC_results_UserData.mdb")
Set db_form = DBEngine.Workspaces(0).OpenDatabase
("W:\Shared\00000_BIOCHEM_FAQ\BC_results_Form.mdb")

'the original data text file
stDocName_data = "c:\biochem\unzipped\BC_" & Me!Query_num
& "_data.txt"
' the final backup table name
stDocName_NewTable = "BC_UserQuery_" & Me!Query_num
& "_data"

stDocName_xxxTable = "BC_UserQuery_xxxx_data"
strPath = "c:\biochem\BC_results_UserData.mdb"



'sqlstr2 = "SELECT * INTO " & stDocName_NewTable _
' & " IN " & strPath _
' & " FROM " & stDocName_xxxTable & ";"

sqlstr2 = "SELECT * INTO " & stDocName_NewTable _
& " FROM " & stDocName_xxxTable & ";"

'Set myquery2 = db_form.CreateQueryDef("0026_BackupData")
'Set db_data = CurrentDb
Set myquery2 = db_form.QueryDefs("0026_BackupData")
myquery2.SQL = sqlstr2
DoCmd.OpenQuery ("0026_BackupData")
End Sub
 
The main problem is that your query is missing the "In" clause which
specifies the path of the target db for the append query:

sqlstr2 = "SELECT " & stDocName_xxxTable & ".* Into " _
& stDocName_NewTable & " In """ & strPath _
& """ FROM " & stDocName_xxxTable & ";"

I added the extra quotes around strPath simply because my test path included
the "My Documents" directory and so the path must have quotes - you could
take these out but they don't hurt.

Other problems/comments:

- You don't need to open a database object on the UserData.mdb. The query
will take care of it for you.

- You don't need to create a query for this, just use inline SQL with
db.execute:

sqlstr2 = "SELECT " & stDocName_xxxTable & ".* Into " _
& stDocName_NewTable & " In """ & strPath _
& """ FROM " & stDocName_xxxTable & ";"
db_data.Execute sqlstr2

- Don't forget to destroy the database object:

Set db_data = Nothing

- You've got a bunch of unused/commented out code - don't forget to get rid
of it.
 
Back
Top