[RESOLVED] Access 2003: Creating new instance of current database

[RiceRocket]

Hi,

I would like the user to be able to create a new instance of my Access app which would basically replicate the current database and clear the contents of all of the tables. It is similiar to simply doing File -> Save As... then clearing all of the tables. But, for some reason, i can't get the FileDialog object to do this. I have tried coding this with the FileDialog object, but Access crashes on the .Execute method of FD obj. If anyone has any other suggestions about how to do this or how to change my code to get it work, I would LOVE to hear them!! Here's my code:

Private Sub LblEffect2_Click() ' NOT WORKING!!
    With Application
        With .FileDialog(msoFileDialogSaveAs)
            .InitialFileName = "New AR Tracker.mdb"
            .Title = "Please choose a name for your new AR Tracker database"
            .ButtonName = "Create New"
            .Show
            .Execute
        End With
    End With
End Sub

Thanks in advance!!

[RobDog888]

Thats because .Execute does not do what you think it does. It basically applies any setting changes you make to the dialog without showing it. If you show it and then dismiss it then the instance is destroyed.

If you want to create a new blank database tehn try this...

Application.DoCmd.RunCommand acCmdNewDatabase

[RiceRocket]

Ahh, thanks, RobDog!

I'm not trying to create a new blank database, but basically I want to recreate the exact database I have (saving all the forms and tables) but with blank tables. Basically, it would be the same as doing a File -> Save As... or doing a File -> New -> From existing file. Any ideas?

[RiceRocket]

So I figured out how to delete all the records in my tables. Then I tried to use the command you gave me above (acCmdNewDatabase) and I get a "The Command or Action 'NewDatabase' isn't available now" error. I also tried the acCmdCopyDatabase with the same error. Why am I getting this error?

[RobDog888]

[color=navy]Could be from debugging the code and placing the db in exclusive access mode. Try placing the code behind a form's command button click or such. Make sure you didnt open the db as exclusive and have no objects open in design view or altogether even open to make sure.[/coor]

[RiceRocket]

I have tried everything and seem to still get this message. Is there a quick way to tell if my DB is in exclusive access mode?

Could I create another instance of Access and copy all of my tables/forms/modules to that instance or is that just WAY too much work for something so simple? I don't believe what I'm trying to do should be difficult...

[RiceRocket]

So I finally figured out how to get what i wanted. I had to create a .bat file to execute a copy and rename command to emulate a "File -> Save As". Then I deleted all the table data in the new copied database. This is probably the hardest way to do this, but it works... so oh, well. Here's the code:

Private Sub LblEffect2_Click()
Dim tbl As AccessObject
Dim strSQL As String
Dim frm As Form
Dim aApp As Access.Application
Dim newFileFullPath As String
Dim fn As Long, x As Variant
Dim newFilePath As String
Dim newFileName As String
 
On Error GoTo No_Bugs
 
' show filedialog to get new file path/name
    With Application
        With .FileDialog(msoFileDialogSaveAs)
            .InitialFileName = "New AR Tracker.mde"
            .Title = "Please choose a name for your new AR Tracker database"
            .ButtonName = "Create New"
            .AllowMultiSelect = False
            If .Show = -1 Then
                newFileFullPath = .SelectedItems(1)
            Else
            End If
        End With
    End With
 
' get the file path and file name from the filedialog
newFilePath = Left(newFileFullPath, InStrRev(newFileFullPath, "\") - 1)
newFileName = Right(newFileFullPath, Len(newFileFullPath) - InStrRev(newFileFullPath, "\"))
 
' Create batch file and run it
 fn = FreeFile
 Open "C:\DeleteMe.bat" For Output As fn
 Print #1, "copy " & Chr(34) & Application.CurrentProject.FullName & Chr(34) & _
 " " & Chr(34) & newFilePath & Chr(34) & vbCr
 Print #1, "ren " & Chr(34) & newFilePath & "\" & Application.CurrentProject.Name & _
    Chr(34) & " " & Chr(34) & newFileName & Chr(34) & vbCr
 Close #1
 x = Shell("c:\DeleteMe.bat", vbHide)
 
' set a reference to the newly created DB and open it
 Set aApp = GetObject(newFileFullPath, "Access.Application")
 
' delete all the table data in the newly created DB
For Each tbl In aApp.CurrentData.AllTables
    If Left(tbl.Name, 4) <> "MSys" Then
        DoCmd.SetWarnings False
        strSQL = "DELETE * FROM [" & tbl.Name & "]"
        Debug.Print tbl.Name
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
    Else: Debug.Print "EXCLUDED: " & tbl.Name
    End If
Next
 
' recalculate the visible forms to show updated table data (which is none)
For Each frm In aApp.Forms
    Debug.Print frm.Name
    frm.Recalc
Next
 
Exit_Sub:
Set tbl = Nothing
Set frm = Nothing
Exit Sub
 
No_Bugs:
MsgBox "An error has occurred in this application. " _
& "Please contact your technical support person " _
& "and tell them this information:" _
& vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " & Err.Description, _
Buttons:=vbCritical, Title:="AR Tracker Error"
Resume Exit_Sub:
 
End Sub