G
Guest
Hi, some of you have helped me get to this point and I am very gratefully
for all your help, but need a little more help please.
Below is the base of what I have started with and without writing a book
here I will try to explain, what I am trying to accomplish.
The master DB is in production, and used by about 8 people, some of whom are
use to being able to go to the make changes to the structure of things. I am
trying to stop that but need to do it carefully. SO I will LOCK the Master
DB down preventing users from making any structural changes or creating and
adhoc queries... and create a copy of the master db in a sub folder call
adhoc. Create a button on the main screen that will run this procedure.
Some of you may recognize bits and pieces from previous posts. When the
procedure runs, it will test to see if anyone is already in the adhoc db, and
if there is it will just open the adhoc db for the new user. If there is no
one in there, the procedure will run queries that delete the records from the
table, and append the data from links (that I have set as hidden) back into
the tables, then compact the adhoc db, (I've read that the compact command
needs to create a second file), Then after the compacted db is renamed to
adhoc.mdb, it is opened
How to I call procedures in the adhoc db to run the delete and append queries
AND
How do delete and rename the compacted DB
--------------------
Private Sub DuplicateDB_Click()
On Error GoTo Err_DuplicateDB_Click
Dim dbs As DAO.Database
Dim lDbFileName As String
lDbFileName = "D:\Dev\AdhocDB\Adhoc.ldb"
If Len(Dir(lDbFileName)) > 0 Then
On Error Resume Next
Kill lDbFileName
If Err.Number = 0 Then
'delete was successful, file not in use
' call procedure to repopulate tables
Call RefreshData
' THEN CALL THIS PROCEDURE TO COMPACT THE NEWLY UPDATED ADHOC DB
Call CompactDb
'Open Adhoc DB
Else
'delete failed, file in use
' call procedure to open db as is
Set dbs = OpenDatabase("D:\Dev\AdhocDB\Adhoc.mdb")
End If
On Error GoTo Err_DuplicateDB_Click
End If
Exit_DuplicateDB_Click:
Exit Sub
Err_DuplicateDB_Click:
MsgBox Err.Description
Resume Exit_DuplicateDB_Click
End Sub
------------------------
Sub CompactDb()
Dim dbs As Database
Set dbs = OpenDatabase("\\UNCPATH\FILENAME.mdb")
' This statement creates a compact version of the database
DBEngine.CompactDatabase "\\UNCPATH\FILENAME.mdb",
"\\UNCPATH\FILENAME2.mdb"
'NOW DELETE THE ORIGINAL DB AND RENAME THE COMPACTED DB TO THE ORIGINAL NAME
End Sub
for all your help, but need a little more help please.
Below is the base of what I have started with and without writing a book
here I will try to explain, what I am trying to accomplish.
The master DB is in production, and used by about 8 people, some of whom are
use to being able to go to the make changes to the structure of things. I am
trying to stop that but need to do it carefully. SO I will LOCK the Master
DB down preventing users from making any structural changes or creating and
adhoc queries... and create a copy of the master db in a sub folder call
adhoc. Create a button on the main screen that will run this procedure.
Some of you may recognize bits and pieces from previous posts. When the
procedure runs, it will test to see if anyone is already in the adhoc db, and
if there is it will just open the adhoc db for the new user. If there is no
one in there, the procedure will run queries that delete the records from the
table, and append the data from links (that I have set as hidden) back into
the tables, then compact the adhoc db, (I've read that the compact command
needs to create a second file), Then after the compacted db is renamed to
adhoc.mdb, it is opened
How to I call procedures in the adhoc db to run the delete and append queries
AND
How do delete and rename the compacted DB
--------------------
Private Sub DuplicateDB_Click()
On Error GoTo Err_DuplicateDB_Click
Dim dbs As DAO.Database
Dim lDbFileName As String
lDbFileName = "D:\Dev\AdhocDB\Adhoc.ldb"
If Len(Dir(lDbFileName)) > 0 Then
On Error Resume Next
Kill lDbFileName
If Err.Number = 0 Then
'delete was successful, file not in use
' call procedure to repopulate tables
Call RefreshData
' THEN CALL THIS PROCEDURE TO COMPACT THE NEWLY UPDATED ADHOC DB
Call CompactDb
'Open Adhoc DB
Else
'delete failed, file in use
' call procedure to open db as is
Set dbs = OpenDatabase("D:\Dev\AdhocDB\Adhoc.mdb")
End If
On Error GoTo Err_DuplicateDB_Click
End If
Exit_DuplicateDB_Click:
Exit Sub
Err_DuplicateDB_Click:
MsgBox Err.Description
Resume Exit_DuplicateDB_Click
End Sub
------------------------
Sub CompactDb()
Dim dbs As Database
Set dbs = OpenDatabase("\\UNCPATH\FILENAME.mdb")
' This statement creates a compact version of the database
DBEngine.CompactDatabase "\\UNCPATH\FILENAME.mdb",
"\\UNCPATH\FILENAME2.mdb"
'NOW DELETE THE ORIGINAL DB AND RENAME THE COMPACTED DB TO THE ORIGINAL NAME
End Sub