VBA - More help please

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
 
G

Guest

Why not just make a user database linked to your master db that you can copy
to each users machine? This seems like a lot of hassle, and potential
annoyance to your users.
 
G

Guest

Hi Lance, thanks for your reply.
I thought of putting a user DB on the network with links to the Master, and
provide them access via this button, but I was not sure that the links would
keep things secure enough. They would still be able to modify data directly
in the tables, they would still be able to run queries that could change data
in the tables. SO I need figured I needed to do it this way.

I have figured out some of my issues and reposted my code.
Using VBA help I found this would switch currentdb
dbt = OpenDatabase("D:\Dev\AdhocDB\Adhoc.mdb")
Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase dbt

AND this would allow me to run the queries in the Adhoc DB
Dim db As DAO.Database
Set db = OpenDatabase("D:\Dev\AdhocDB\Adhoc.mdb", True)

when used along with this WHICH WAS RECOMMENDED TO ME IN A PREVIOUS POST BY
PIETER AS A PREFERED METHOD FOR RUNNING QUERIES FROM CODE
db.Execute "qd_Detail", DAO.dbFailOnError
db.Execute "qa_L_Detail", DAO.dbFailOnError

HERE IS THE LATEST, WHAT IS DONE INCORRECTLY?
--------------------
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
End If
On Error GoTo Err_DuplicateDB_Click
Else
'call procedure to repopulate tables
Call RefreshData

' THEN CALL THIS PROCEDURE TO COMPACT THE NEWLY UPDATED ADHOC DB
Call CompactDb
End If

dbt = OpenDatabase("D:\Dev\AdhocDB\Adhoc.mdb")
Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase dbt

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

------------------------
Private Sub RefreshData()
Dim db As DAO.Database
Set db = OpenDatabase("D:\Dev\AdhocDB\Adhoc.mdb", True)

On Error GoTo Err_RefreshData
'THIS MODULE WILL RUN ALL THE DELETE AND APPEND QUERIES THAT ARE USED TO
REFRESH THE TABLE DATA
'NOT SURE IF I NEED TO DECLARE EVERY QUERY OR JUST PLACE THE QUERY NAME IN
THE COMMAND

db.Execute "qd_Detail", DAO.dbFailOnError
db.Execute "qa_L_Detail", DAO.dbFailOnError
db.Close

Exit_RefreshData:
Exit Sub

Err_RefreshData:
MsgBox Err.Description
Resume Exit_RefreshData

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top