Access Capabilities help

[Guest]

Does anyone know how to make ACCESS copy itself and generate a blank copy for a new FY? My intent is to have a switchboard button that a user can use to duplicate the database, have it ask for the new FY, and find/replace all references in the tables, forms, and reports to the old FY. Is this possible, or am I expecting too much from the product?

 

[Chris]

There is no built in way to do this. You will have to
right macros and/or code to do it.


Chris

-----Original Message-----
Does anyone know how to make ACCESS copy itself and

generate a blank copy for a new FY? My intent is to have
a switchboard button that a user can use to duplicate the
database, have it ask for the new FY, and find/replace all
references in the tables, forms, and reports to the old
FY. Is this possible, or am I expecting too much from the
product?

 

[Guest]

Thanks. If there is anyone with a MACRO that has tried to do this in the past, I would appreciate any ideas on how that you can provide...

 

[Rodrigo]

I'm not sure I really understand what you are trying to do.
Are you trying to just copy the current open database somewhere else? (this
is easy)
The front end?, if the front end is what is open, same as above.
The back end? if the back end is for the current open database, and the only
connection to the back end is linked tables then this could be done by:
make a copy of the back end wherever you want.
listing all of the tables on the current database. Filter out the local ones
(they wont have the connetion to the back end database, this is a property
of the tabledef). re-link them to the path of the copy.
done.
However if you are connecting to the back end in other ways (not only linked
tables, but direct connections, queries, etc) then it would be hard because
you will have to track down all connections and re-writte them to the new
database.

I guess I don't know what you mean by FY, let me know and I could add some
detail to the above, hopefully what I wrote is enough to get you going.

Anyway:
letting people copy our database at will and putting it wherever they want
is generally (always!) a bad idea. The main reason being if you make an
update to the main database, how do you then update all the copies the users
made? If you don't care then they are on their own, but if you do care. It
will be very hard to track down all the copies and update them.

Rodrigo.

Does anyone know how to make ACCESS copy itself and generate a blank copy

for a new FY? My intent is to have a switchboard button that a user can use
to duplicate the database, have it ask for the new FY, and find/replace all
references in the tables, forms, and reports to the old FY. Is this
possible, or am I expecting too much from the product?

 

[Rodrigo]

by the way, I meant "your" database, not "our". I tend to miskey when I type
on my laptop ;-)

 

[Guest]

Rodrigo,

FY = Fiscal Year.

My intent is to make my database capable of being run by anyone who follows me into this position. Since we are using it in a military setting, that means that I could depart on a moments notice and the database (front-end and back-end) need to set up in such a way that they are king of NUG proof.

My thought was that I could build a mechanism into the switchboard that would execute the establishement of a clean database on order at the end of the fiscal year 1 OCT and then the new person would not have to go through what I went through rebuilding the database for the new year.

I have thought of building the reports to continue across the FISCAL years, but generally we close out the files and retire them. If I can generate the reports by FY, etc, then I think my boss will accept this solution and then the replication of a clean database will no longer be necessary.

Appreciate any thoughts and would be happy to "share" responsibility for this with anyone who can help. Look forward to your response.

TAL


----- Rodrigo wrote: -----

by the way, I meant "your" database, not "our". I tend to miskey when I type
on my laptop ;-)

 

[Rodrigo]

If you can follow this MAKE SURE YOU MAKE BACKUPS before you run any of
this.
Though I don't like to to this, here it goes.
Have a database different back end for every year (this year will be
BE_db2004.mdb, next year BE_db2005.mdb) But keep the same front end. Like I
said before, this will only work correctly if the only way you are updating
the Back end is by linked tables. And if youd don't have any of the linked
tables open at the time this is running (the file copy won't work)

when you run the converttonewFY sub this will happen:

sub converttonewFY()
dim strPath as str
dim strNewBEdb as string
dim strBEDb as string

dim db as dao.database
dim tdf as dao.tabledeg
dim cnn as string

strBEPath = "c:\my docs\"
strBEDb = strBEPath & "2004.mdb"

strNewBEdb = strBEPath & "BE_DB" & (cstr(Year(date)) + 1) & ".mdb"
' copy current back end to new database
filecopy strBEDb , strnewdbedb

'Relink all tables with the new path and delete all data on them
set db = currentdb()
for each tdf in db.tabledefs
if len(tdf.connect)<>0 then
cnn = "MS Access;DATABASE=" & strNewBEdb & ";TABLE=" & tdf.name
tdf.connect = cnn
tdf.RefreshLink
docmd.runsql "DELETE " & tdf.Name & ".* FROM " & tdf.NAME & " ;
"
end if
Next tdf
set db = nothing

End Function

Rodrigo,

FY = Fiscal Year.

My intent is to make my database capable of being run by anyone who

follows me into this position. Since we are using it in a military setting,
that means that I could depart on a moments notice and the database
(front-end and back-end) need to set up in such a way that they are king of
NUG proof.

My thought was that I could build a mechanism into the switchboard that

would execute the establishement of a clean database on order at the end of
the fiscal year 1 OCT and then the new person would not have to go through
what I went through rebuilding the database for the new year.

I have thought of building the reports to continue across the FISCAL

years, but generally we close out the files and retire them. If I can
generate the reports by FY, etc, then I think my boss will accept this
solution and then the replication of a clean database will no longer be
necessary.

Appreciate any thoughts and would be happy to "share" responsibility for

this with anyone who can help. Look forward to your response.

 

[Rodrigo]

Or you can do the same thing backwards.

Backup the current Back end as BE2004.mdb.
Delete all data on the current back end.
You are done and ready to start a new year (don't have to relink anything
because you are using the current database).

Rodrigo.