Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Copy table from backend to different db

Status
Not open for further replies.
annsolomon

annsolomon

MIS
Oct 24, 2002
512
US
I want to be able to copy (e.g., backup) a few tables from my back end db by clicking a button on a form in my front end. The backend is on the server (g:\files\ml401k\401k_be.mdb) and I want to copy tblHistory to another db on the server (j:\everyone\mis projects\401kbackup.mdb).

I just can't seem to get the code right. I tried to get my code started by creating an update query and looking at the SQL behind it but when I put it behind the command button I keep getting a message saying the input query must contain at least one table or query.

Can some kind soul please help me out here? I've been playing with this off and on for a couple of weeks and can't seem to find the right help searching the Internet or Access Help. Ann
 
Sort by date Sort by votes
Hi

Suggest you make some "Make Table" queries.

If you have tblA and tblB then make two Make Table queries showing all the fields of each table. The target db will be your remote one.

On a click event: with docmd, set warnings off, run each of the queries, set warnings on, endwith.

HTH

Telephoto
 
Upvote 0 Downvote
I am doing the same as you except my database is backed up each time it's closed. But presumably you can just easily put the same code behind a command button as well. Here is the complete procedure (it's a bit long!!):

Private Sub Form_Close()

Dim Maindb As String
Dim MaindbName As String
Dim Backupdb As String
Dim BackupdbName As String
Dim LastBackup As Date
Dim LastUpdate As Date
Dim ConnectString As String
Dim updateSQL As String
Dim dbs As Database
Dim Rst As Recordset
Dim Choice As Integer
Dim Complete As Boolean

'Only run backup procedure if anything has changed
LastBackup = DLookup("LastBackUp", "tblLibSysData")
LastUpdate = DLookup("LastUpdated", "tblLibSysData")

If LastBackup < LastUpdate Then

'button to instigate backup of database
On Error GoTo ErrorHandler

'get path of Main DB and Backup DB
Set dbs = CurrentDb
Set Rst = dbs.OpenRecordset(&quot;tblLibSysData&quot;, dbOpenDynaset)
With Rst
MaindbName = !MainDataName
Maindb = !MainDataPath & &quot;\&quot; & MaindbName
BackupdbName = !BackupName
Backupdb = !BackupPath & &quot;\&quot; & BackupdbName
.Close
End With
Set Rst = Nothing
Set dbs = Nothing

'build connect property for recreating links
ConnectString = &quot;;Database=&quot; & Maindb

'set up progress meter to show completion of backup operation
Dim BackupMeter
Dim MeterFull As Integer
Dim MeterCount As Integer
MeterFull = 78
BackupMeter = SysCmd(acSysCmdInitMeter, &quot;Performing Backup Operation&quot;, MeterFull)

'mark as complete
Complete = True

'start copying tables to Backup and update progress meter
DoCmd.TransferDatabase acExport, &quot;Microsoft Access&quot;, _
Backupdb, acTable, &quot;tblLibNew&quot;, _
&quot;tblLibNew&quot;

'update progress meter
MeterCount = MeterCount + 3
BackupMeter = SysCmd(acSysCmdUpdateMeter, MeterCount)

DoCmd.TransferDatabase acExport, &quot;Microsoft Access&quot;, _
Backupdb, acTable, &quot;tblLibServer&quot;, _
&quot;tblLibServer&quot;

'update progress meter
MeterCount = MeterCount + 3
BackupMeter = SysCmd(acSysCmdUpdateMeter, MeterCount)

DoCmd.TransferDatabase acExport, &quot;Microsoft Access&quot;, _
Backupdb, acTable, &quot;tblLibData&quot;, _
&quot;tblLibData&quot;

'update progress meter
MeterCount = MeterCount + 3
BackupMeter = SysCmd(acSysCmdUpdateMeter, MeterCount)

DoCmd.TransferDatabase acExport, &quot;Microsoft Access&quot;, _
Backupdb, acTable, &quot;tblLibEft&quot;, _
&quot;tblLibEft&quot;

'update progress meter
MeterCount = MeterCount + 3
BackupMeter = SysCmd(acSysCmdUpdateMeter, MeterCount)

DoCmd.TransferDatabase acExport, &quot;Microsoft Access&quot;, _
Backupdb, acTable, &quot;tblLibOther&quot;, _
&quot;tblLibOther&quot;

'update progress meter
MeterCount = MeterCount + 3
BackupMeter = SysCmd(acSysCmdUpdateMeter, MeterCount)

DoCmd.TransferDatabase acExport, &quot;Microsoft Access&quot;, _
Backupdb, acTable, &quot;tblLibRestore&quot;, _
&quot;tblLibRestore&quot;

'update progress meter
MeterCount = MeterCount + 3
BackupMeter = SysCmd(acSysCmdUpdateMeter, MeterCount)

DoCmd.TransferDatabase acExport, &quot;Microsoft Access&quot;, _
Backupdb, acTable, &quot;tblLibSave&quot;, _
&quot;tblLibSave&quot;

'update progress meter
MeterCount = MeterCount + 3
BackupMeter = SysCmd(acSysCmdUpdateMeter, MeterCount)

DoCmd.TransferDatabase acExport, &quot;Microsoft Access&quot;, _
Backupdb, acTable, &quot;tblAuthName&quot;, _
&quot;tblAuthName&quot;

'update progress meter
MeterCount = MeterCount + 3
BackupMeter = SysCmd(acSysCmdUpdateMeter, MeterCount)

DoCmd.TransferDatabase acExport, &quot;Microsoft Access&quot;, _
Backupdb, acTable, &quot;tblIcbsVersion&quot;, _
&quot;tblIcbsVersion&quot;

'update progress meter
MeterCount = MeterCount + 3
BackupMeter = SysCmd(acSysCmdUpdateMeter, MeterCount)

DoCmd.TransferDatabase acExport, &quot;Microsoft Access&quot;, _
Backupdb, acTable, &quot;tblLibSysData&quot;, _
&quot;tblLibSysData&quot;

'update progress meter
MeterCount = MeterCount + 3
BackupMeter = SysCmd(acSysCmdUpdateMeter, MeterCount)

DoCmd.TransferDatabase acExport, &quot;Microsoft Access&quot;, _
Backupdb, acTable, &quot;tblMediaPeriod&quot;, _
&quot;tblMediaPeriod&quot;

'update progress meter
MeterCount = MeterCount + 3
BackupMeter = SysCmd(acSysCmdUpdateMeter, MeterCount)

DoCmd.TransferDatabase acExport, &quot;Microsoft Access&quot;, _
Backupdb, acTable, &quot;tblMediaType&quot;, _
&quot;tblMediaType&quot;

'update progress meter
MeterCount = MeterCount + 3
BackupMeter = SysCmd(acSysCmdUpdateMeter, MeterCount)

DoCmd.TransferDatabase acExport, &quot;Microsoft Access&quot;, _
Backupdb, acTable, &quot;tblOsRelease&quot;, _
&quot;tblOsRelease&quot;

'update progress meter
MeterCount = MeterCount + 3
BackupMeter = SysCmd(acSysCmdUpdateMeter, MeterCount)

DoCmd.TransferDatabase acExport, &quot;Microsoft Access&quot;, _
Backupdb, acTable, &quot;tblOsVersion&quot;, _
&quot;tblOsVersion&quot;

'update progress meter
MeterCount = MeterCount + 3
BackupMeter = SysCmd(acSysCmdUpdateMeter, MeterCount)

DoCmd.TransferDatabase acExport, &quot;Microsoft Access&quot;, _
Backupdb, acTable, &quot;tblProjectName&quot;, _
&quot;tblProjectName&quot;

'update progress meter
MeterCount = MeterCount + 3
BackupMeter = SysCmd(acSysCmdUpdateMeter, MeterCount)

DoCmd.TransferDatabase acExport, &quot;Microsoft Access&quot;, _
Backupdb, acTable, &quot;tblSecLib&quot;, _
&quot;tblSecLib&quot;

'update progress meter
MeterCount = MeterCount + 3
BackupMeter = SysCmd(acSysCmdUpdateMeter, MeterCount)

DoCmd.TransferDatabase acExport, &quot;Microsoft Access&quot;, _
Backupdb, acTable, &quot;tblSystemName&quot;, _
&quot;tblSystemName&quot;

'update progress meter
MeterCount = MeterCount + 3
BackupMeter = SysCmd(acSysCmdUpdateMeter, MeterCount)

DoCmd.TransferDatabase acExport, &quot;Microsoft Access&quot;, _
Backupdb, acTable, &quot;tblPortNoFSVUKDV1&quot;, _
&quot;tblPortNoFSVUKDV1&quot;

'update progress meter
MeterCount = MeterCount + 3
BackupMeter = SysCmd(acSysCmdUpdateMeter, MeterCount)

DoCmd.TransferDatabase acExport, &quot;Microsoft Access&quot;, _
Backupdb, acTable, &quot;tblPortNoFSVUKAS2&quot;, _
&quot;tblPortNoFSVUKAS2&quot;

'update progress meter
MeterCount = MeterCount + 3
BackupMeter = SysCmd(acSysCmdUpdateMeter, MeterCount)

DoCmd.TransferDatabase acExport, &quot;Microsoft Access&quot;, _
Backupdb, acTable, &quot;tblPortNoFSVUKAS3&quot;, _
&quot;tblPortNoFSVUKAS3&quot;

'update progress meter
MeterCount = MeterCount + 3
BackupMeter = SysCmd(acSysCmdUpdateMeter, MeterCount)

DoCmd.TransferDatabase acExport, &quot;Microsoft Access&quot;, _
Backupdb, acTable, &quot;tblPortNoFSVUKAS4&quot;, _
&quot;tblPortNoFSVUKAS4&quot;

'update progress meter
MeterCount = MeterCount + 3
BackupMeter = SysCmd(acSysCmdUpdateMeter, MeterCount)

DoCmd.TransferDatabase acExport, &quot;Microsoft Access&quot;, _
Backupdb, acTable, &quot;tblPortNoFSVUKAS5&quot;, _
&quot;tblPortNoFSVUKAS5&quot;

'update progress meter
MeterCount = MeterCount + 3
BackupMeter = SysCmd(acSysCmdUpdateMeter, MeterCount)

DoCmd.TransferDatabase acExport, &quot;Microsoft Access&quot;, _
Backupdb, acTable, &quot;tblPortNoFSVUKAS6&quot;, _
&quot;tblPortNoFSVUKAS6&quot;

'update progress meter
MeterCount = MeterCount + 3
BackupMeter = SysCmd(acSysCmdUpdateMeter, MeterCount)

DoCmd.TransferDatabase acExport, &quot;Microsoft Access&quot;, _
Backupdb, acTable, &quot;tblAppLib&quot;, _
&quot;tblAppLib&quot;

'update progress meter
MeterCount = MeterCount + 3
BackupMeter = SysCmd(acSysCmdUpdateMeter, MeterCount)

DoCmd.TransferDatabase acExport, &quot;Microsoft Access&quot;, _
Backupdb, acTable, &quot;tblDbType&quot;, _
&quot;tblDbType&quot;

'update progress meter
MeterCount = MeterCount + 3
BackupMeter = SysCmd(acSysCmdUpdateMeter, MeterCount)

DoCmd.TransferDatabase acExport, &quot;Microsoft Access&quot;, _
Backupdb, acTable, &quot;tblSysInfo&quot;, _
&quot;tblSysInfo&quot;

'confirm completion of backup
If Complete = True Then 'only if backup not exited by user
'update date of last backup
Set dbs = CurrentDb
Set Rst = dbs.OpenRecordset(&quot;tblLibSysData&quot;, dbOpenDynaset)
Rst.Edit
Rst!LastBackup = Date
Rst.Update
Rst.Close
Set Rst = Nothing
Set dbs = Nothing
End If

Exit_BackupButton:
'remove progress meter
BackupMeter = SysCmd(acSysCmdRemoveMeter)

End If

Exit Sub

ErrorHandler:
'Dim Choice
Choice = 0
'determine error number
Select Case Err.Number
Case 3044 'invalid directory path entry
MsgBox &quot;Backup operation has been unsuccessful. The directory path entered was not valid.&quot; & _
&quot; Establish the correct path for the backup save and retry.&quot;, vbInformation, &quot;Backup Failure&quot;
'GoTo Exit_BackupButton 'exit routine
Exit Sub

Case 70 'unable to access time data tables
MsgBox &quot;Backup operation has been unsuccessful. The data storage database is locked and &quot; & _
&quot;cannot be copied. Remove the lock and retry the backup operation&quot;, vbInformation, &quot;Backupfailure&quot;
Complete = False
Resume Next 'rebuild all linked tables

'report failure of backup on ocurrence of any other error
Case Else
Dim ErrorText As String
MsgBox Err.Description, _
vbExclamation, &quot;Backup Failure&quot;
Complete = False
Resume Next
Exit Sub
End Select

End Sub
 
Upvote 0 Downvote
madhouse, this is WAY more than what I want to do. Here's the statement I used initially but it simply backed up the link to the be table: DoCmd.TransferDatabase acExport, &quot;Microsoft Access&quot;, _
&quot;J:\everyone\mis projects\401k backup\401kbackup.mdb&quot;, _
acTable, &quot;tblHistory&quot;, &quot;tblHistory&quot;, False

I tried entering the full path to tblHistory in the be but just can't get it to work. Ann
 
Upvote 0 Downvote
I ended up creating a link in my FE to the tables in the backup db then added code to delete records in the BE tables then run an append query.

Not exactly what I was trying to do but the end result works for me.

Thanks for your input. Ann
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Moss100
  • Locked
  • Question
Transfer data from external table to identical local table - urgent please
2
Replies
27
Views
837
Andrzejek
Andrzejek
Viswa.R
  • Locked
  • Question
Dimension import from Autocad to Excel
Replies
0
Views
412
Viswa.R
Viswa.R
BFroeschlKC
  • Locked
  • Question
Publisher Automation from Access Frustration
Replies
3
Views
2K
dhookom
dhookom
Moss100
  • Locked
  • Question
Add multiple contacts to outlook - from access table
Replies
12
Views
517
Moss100
Moss100
btamsgn
  • Locked
  • Question
How to add lines from textbox to table 1
Replies
8
Views
322
MattGreer
MattGreer

Part and Inventory Search

Sponsor

Back
Top