MS Automation - Saving database

[mattieflo]

Hello....I'm trying to build a button that will loop through records by
extracting a store number and save the current database as a different name.
For example, If I have a database that is named store_audits.mdb and a table
on it named tblStores with the following info:

StoreNumber Location
1 Seattle
2 Dallas
3 New York

I want to build a button that will save the current database to a specified
location with database name followed by the storenumber...(Example
store_audits1.mdb)

The reason why is I want each of these locations to have their own front end
database but I want to make a tool that will update all these front ends from
where I'm at. Is there VBA code that uses the "SaveAs" command from the menu?

 

[April]

Have you considered splitting your database into a front end/back end setup,
where your data tables are stored seperately and the user's front end copies
are linked to the backend?

 

[mattieflo]

Yeah I usually do but in this case, i'm trying to save their frontends on
their share drives for them through automation for them.

 

[April]

What if you stored one copy of the front end at a location that everyone
could access, then used a bat file (stored on their desktop) that they would
click on that would copy that front end to their specific drives.

I'm not sure if you've worked with bat files, but if you haven't it's pretty
simple. Just open notepad and type in the window's xcopy function an save it
with the .bat extension.

Syntax
XCOPY source [destination] [options]

Some options:
/Y Suppresses prompting to confirm you want to overwrite an existing
destination file.
/D Copies files whose source time is newer than the destination time.

If there are spaces in the file path, you have to use quotation marks.

XCOPY "L:\My Network Folder\Front End DB.mdb" "D:\User Folder\" /Y /D

To use the bat file, the users just click on it like they would click on any
other desktop shortcut.

If this is something you're interested in, put XCOPY into google and you'll
get tons of info.

 

[Clifford Bass]

Hi,

Or if that does not quite fit your needs, you may want to check out
<http://www.granite.ab.ca/access/autofe.htm>.

I would recommend that since it is a front end that you not deal with
multiply-named files. It can lead to complications.

Clifford Bass

 

[mattieflo]

Wow! I never knew an option like this existed... that's precisely what I
want! Thank you so much, im going to try it and let you know how it worked
out.

Hi,

Or if that does not quite fit your needs, you may want to check out
<http://www.granite.ab.ca/access/autofe.htm>.

I would recommend that since it is a front end that you not deal with
multiply-named files. It can lead to complications.

Clifford Bass

What if you stored one copy of the front end at a location that everyone
could access, then used a bat file (stored on their desktop) that they would
click on that would copy that front end to their specific drives.

I'm not sure if you've worked with bat files, but if you haven't it's pretty
simple. Just open notepad and type in the window's xcopy function an save it
with the .bat extension.

Syntax
XCOPY source [destination] [options]

Some options:
/Y Suppresses prompting to confirm you want to overwrite an existing
destination file.
/D Copies files whose source time is newer than the destination time.

If there are spaces in the file path, you have to use quotation marks.

XCOPY "L:\My Network Folder\Front End DB.mdb" "D:\User Folder\" /Y /D

To use the bat file, the users just click on it like they would click on any
other desktop shortcut.

If this is something you're interested in, put XCOPY into google and you'll
get tons of info.

 

[Clifford Bass]

Hi,

The thanks really go to Tony Toews. Glad to point it out though.

Clifford Bass