[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