Manipulating multiple databases at the same time 1

[Woody666]

How do you have more than one database open at a time using ='dbengine.workspaces(0).databases(0)' and using the '=Currentdb' without having to close one and open the other.

 

[UncleJack]

It's no problem having two (or more) databases open at once. It just depends on what you want to do with them.

Here's a routine I wrote to backup four tables to a floppy. (Yes, the tables are always small which is why they're written to a floppy.) It shows how to create the second database and how to refer to it in code. The backup uses the current date as part of the name of the backup file.

Uncle Jack

Private Sub btnBackup_Click()
'Create database on drive A: and transfer critical files to it
On Error GoTo Err_btnBackup:

'Turn on hourglass and show status bar message
DoCmd.Hourglass True
DoCmd.SetWarnings True
X = SysCmd(acSysCmdSetStatus, "Creating backup on floppy disk&quot

Dim WKS As Workspace
Dim dbsNew As Database
Dim strDate As String
Dim strDBNew As String

' Get default Workspace.
Set WKS = DBEngine.Workspaces(0)

' Create backup DB name
strDate = Date
For X = 1 To Len(strDate)
If MID$(strDate, X, 1) = "/" Then
strDate = Left$(strDate, X - 1) & Right$(strDate, Len(strDate) - X)
End If
Next X

strDBNew = "A:\Back" & strDate & ".mdb"

'Make sure there isn't already a file with the name of the new database
If Dir(strDBNew) <> &quot;&quot; Then Kill strDBNew

'Create and close the backup database
Set dbsNew = WKS.CreateDatabase(strDBNew, dbLangGeneral)
dbsNew.Close

'Dump data from make table queries
DoCmd.RunSQL &quot;SELECT Table1.* INTO Table1 IN '&quot; & strDBNew & &quot;' FROM Table1;&quot;
DoCmd.RunSQL &quot;SELECT Table2.* INTO Table2 IN '&quot; & strDBNew & &quot;' FROM Table2;&quot;
DoCmd.RunSQL &quot;SELECT Table3.* INTO Table3 IN '&quot; & strDBNew & &quot;' FROM Table3;&quot;
DoCmd.RunSQL &quot;SELECT Table4.* INTO Table4 IN '&quot; & strDBNew & &quot;' FROM Table4;&quot;

MsgBox &quot;Database backup successful&quot;, vbInformation + vbOKOnly, ProgramName

Done_btnBackup:
'Clear status bar and turn off hourglass
X = SysCmd(acSysCmdClearStatus)
DoCmd.Hourglass False
Exit Sub

Err_btnBackup:
Select Case Err
Case Is = 71
strMsg = &quot;The floppy disk or drive is not ready&quot;
Case Is = 61, 2285, 2302, 2303, 2304, 2686, 2741, 2742
strMsg = &quot;The disk is full or unable to hold the files. Compact the database &quot;
strMsg = strMsg & &quot;and then try the backup again.&quot;
Case Is = 3051
strMsg = &quot;The disk is write protected.&quot;
Case Is = 3040
strMsg = &quot;A Disk I/O error has occurred.&quot;
Case Is = 3043
strMsg = &quot;An unspecified disk or network error has occurred.&quot;
Case Else
strMsg = &quot;An undefined error has occurred.&quot;
End Select
MsgBox strMsg, vbOKOnly + vbCritical, ProgramName
Resume Done_btnBackup

End Sub