Destination DB as variable?

[BrokenBiker]

We have a blank database that other units are copying to use at their bases. Most of the other users aren't Access savvy and I've been trying to develop ways to overcome this learning curve by automating as many "customizing" functions as possible. For instance, each unit that uses the database has different organizations, aircraft tail numbers, personnel listings, etc. I've got pretty much everything covered except one item.

The database is set up in a way in which it copies itself when it closes providing the UserID level is not 'Read Only.' This database doesn't use front/back end to control its users. It uses a log-on level based on a security example from this board.

It also has an archive function in which several tables are appended to an 'archive' database. This is a basic copy (minus of few functions) of the main database. The intent is to reduce the number of records in the working db w/o losing historical value.

I think the copy function shouldn't be too much of a problem for new users. I've developed a help file that should walk people through the steps to change the location of the copied file. This is done w/ a *.bat file and the change should be as simple as changing text. However, the archive function works off of severl append queries.

Is there a way to change the destination db other than in the query design view? Maybe a code or a box that treats the file location as a variable? The destination db is U:\QA2k6 Database\Archive\QA Archive.mdb That works fine for us, but a new unit will have a different file location.

I'll try to make a smaller/scaled-down copy of the db later to post if anyone wants to check it out.

 

[KeithG]

Yes, you can do that. You need to design a form to input the path of the database and then create a query def object for each query you need to update. Then you need to change the insert into part of each of the sql statements.

 

[BrokenBiker]

OK. Ummmm...but I don't know how to do that. Is it the same as setting the criteria for a field in a query as the [enter your question here] brackets? Also, wouldn't this method require the file location to be input everytime the queries are ran?

I'm looking for a one-time update, but at least this way seems better than what we have now.

 

[KeithG]

Yes, this could be a one time deal. Are you fimilar with the query def object?

 

[BrokenBiker]

Nope. I'm hoping you could tutor me on the subject. I'll be looking through the Access help files on the subject in the meantime.

 

[KeithG]

If you post your db I can make an example for you.

 

[BrokenBiker]

Thanks. It'll be ready for you tomorrow. I'm gonna hafta scale it down a bit.

 

[BrokenBiker]

OK. I think I've got it down to the bare essentials. The opening form has two buttons. The first one has no command attached to it for you to use/change if you want. The other has the original archive code in it, minus the several other queries removed to shrink the size of the db.

Just to make sure I have it straight...We're gonna set up the db so that the file location requirement is permanently changed in the append query? That's awesome!