Best way to handle customer upgrades to backend database.

  • Thread starter Thread starter Stonewall
  • Start date Start date
S

Stonewall

My app uses access 2003. I have the tables in one database and the app in
another. I want to streamline upgrades. Currently, I send the client the
updated frontend database but then I have to make the client email me their
backend database and I manually add the new fields and/or tables needed and
send it back to them. This is a pain for them and for me. I want to be able
to send them a new front end database that contains an upgrade option that
will create all the necessary fields and tables in the backend database. Are
there any examples of this? I need the code that will know the location of
the backend database, open it, add new tables and/or modify existing ones.
All this has to be done from within the application. Since the backend
database is in use, can I do it and if so how? Or, do I need to write a
standalone upgrade application?

Thanks
 
I have such a utility I use where I work. We have about 500 back ends to
update from time to time, so to post the code would be overkill and it
wouldn't fit.

But, I would suggest you create a stand alone mde that will do it. With
only one, it isn't a big deal. If you look in VBA Help and Jet DDL commands,
you should find everything you need. Here are a few examples for adding
columns to existing tables:

With Currentdb
.Execute ("alter table tblLevelCharge ADD COLUMN nbrChrgPerCADeduct
DOUBLE;"), _
dbFailOnError
.Execute ("alter table tblLevelCharge ADD COLUMN nbrChrgPerVC
DOUBLE;"), _
dbFailOnError

.Execute ("alter table tblLevelCharge ADD COLUMN nbrChrgMtrFactor
DOUBLE;"), _
dbFailOnError
End With
 
Hi,
I am using similar approach as Dave. I store BE version in BE DB properties,
then in FE I have a table with version number and update script, so when you
run FE - it checks BE version and automatically updates it if necessary.

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com
 
Oh my, this sounds like a great idea! You wouldn't want to share the update
script would you? I could really use it! My clients are driving me crazy
emailing back and forth back end databases for manual updates. Thanks....
 
Can you really make table changes to the BE from the FE when the FE has the
BE open? was thinking I had to create a standalone .mde to run the script
since the database cannot be in use to create or modify fields in tables. at
least, I have never been able to manually do it when I have the front end
open.
 
I have another question for you. I created a function before any forms are
opened and it works great but if the field already exists in the table, I get
an error message. How would I modify this syntax to check to see if the
field already exists? I want to avoid the error messages I am getting when a
field already exists.

Public Function AlterTable()
Dim db As Database
Set db = CurrentDb()
db.Execute "alter table employees ADD COLUMN NoRounding DOUBLE;"
db.Execute "alter table type_course ADD COLUMN Tardy DOUBLE;"
End Function
 
Question, I created a standalone like your example and it worked fine as long
as I hard codedthe path of my database. I want to copy the standalone into
the same folder where the database is and when I execute the standalone I
want it to alter the database in the same folder. Here's the code I used
but when I execute it, it modifies the salont.mdb in MYDOCUMENTS instead of
in the folder where the standalone is located.

Set db = OpenDatabase("salont.mdb")
 
Back
Top