VBA – Access 2003 Syntax Help - PLEASE

[Programmer - wannaB]

What Code do I need to write something like this

If TESTDB.ldb then (or is there a better way to know if TESTDB is in use)
Delete TESTDB1.ldb (is the DB really in use or is the ldb just not closed
If TESTDB.ldb then
Open TESTDB and make TESTDB Currentdb
ELSE
CALL procedure that deletes and repopulates tables
END IF
ELSE
CALL procedure that deletes and repopulates tables
END IF

Procedure that deletes data from tables and repopulates from Master DB which
was the DB that this procedure was called form.

Delete data from list of tables (about 12, this list of tables could either
be in a table or a simple comma delimited list. Then compact the TESTDB, then
import (that seems like it might not be the right term to use here, IMPORT
makes me think of importing objects from another DB and that is NOT what I am
doing here) refill the tables with data from the MasterDB, and compact the
TESTDB again.

 

[Dale Fye]

Before you do anything, make sure you have backup copies of your testdb.

You can use the Dir( ) function to test whether a file exists. If it
exists, it will return the filename portion of the path\name. If it doesn't
exist, DIR( ) returns and empty string. So, to determine whether the file
exists, use something like:

if len(dir("C:\temp\testdb.ldb")) > 0

Then, to test whether the file is in use or not, I guess you could try to
"Kill" it using the Kill statement. I would wrap this in an error handler to
test if it was successful. If the Err.Number = 0, then the file was deleted
and was not actually in use. Don't forget to reinitiate your error handler
after this segment of code.

On Error Resume Next
Kill "C:\Temp\testdb.ldb"
If Err.Number = 0 then
'delete was successful, file was not in use
'Call the DeleteAndPopulate code from here
else
'delete was not successful, file is in use
endif
On Error Goto ErrorHandler

To delete tables from another database you could set a reference to the
database
Dim db as dao.database
set db = dbengine(0).OpenDatabase("H:\Access\test.mdb")

Then use the tabledefs object to delete the selected tables
db.tabledefs.delete("tableName1")
db.tabledefs.delete("tableName2")

Then you could use docmd.transferdatabase method to transfer the tables from
your source (Master) database to the other database. Probably want to finish
the deletions, then close the db connection to the destination db, then
compact it (I think this will involve giving the compacted copy a new name),
delete the old one, rename the new one to the original name, and then use
TransferDatabase to copy the tables from your Master.

As an alternative, you could write SQL queries that will delete all of the
records from those tables and then append data to them (this method won't
work if the tables contain Autonumber fields).

DELETE * FROM tblNumber1 IN 'C:\temp\test.mdb'

INSERT INTO tblNumber1 (field1, field2, field3, ....) IN 'C:\temp\test.mdb'
SELECT * FROM tblNumber1

Hope this gives you some ideas.
Dale

 

[bourse. marcel]

"Programmer - wannaB" <[email protected]> a écrit
dans le message de
news:[email protected]...

 

[Programmer - wannaB]

YES, that all looks great Dale, thank you.
I am afraid that by deleting and replacing the tables I would loose
relations in the queries. To prevent that do I need to delete and append the
data back in to existing tables? And can you tell me what code is used to
run a query from a VBA module?

 

[Pieter Wijnen]

Db.Execute "Query or SQL", DAO.dbFailOnError
is the preferred method

HtH

Pieter

 

[Programmer - wannaB]

Thanks Pieter, I was just about to answer my own question after finding that
a query can be run from something like this
DoCmd.OpenQuery "qry_name"
Would you please tell me what it is about the Db.Execute command that makes
it preferred? Thank you!

 

[Douglas J. Steele]

Two important differences from my perspective:

1) The Execute method doesn't pop up the message "You're about to .... n
records".
2) Using the dbFailOnError flag with it means that a trappable error will
occur should something go wrong running the query.

 

[Programmer - wannaB]

Thanks Doug, that helps

Two important differences from my perspective:

1) The Execute method doesn't pop up the message "You're about to .... n
records".
2) Using the dbFailOnError flag with it means that a trappable error will
occur should something go wrong running the query.