Best way to handle customer upgrades to backend database.

[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

 

[Klatuu]

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

 

[Alex Dybenko]

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

 

[Stonewall]

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....

 

[Alex Dybenko]

Hi,
unfortunately I do not have it as stand-alone solution. but this is easy to
implement - create a table to record update steps, and a function which will
go through this table and run these steps

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Stonewall]

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.

 

[Alex Dybenko]

Hi,
I do this on program start, before any form opened or other code running. If
somebody else gas same BE opened - I gave a message that all users have to
close application because BE need to be updated

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Stonewall]

Oh! Makes sense. You have been extremley helpful. Thanks...

 

[Stonewall]

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

 

[Stonewall]

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")

 

[Douglas J. Steele]

Set db = OpenDatabase(CurrentProject.Path & "\salont.mdb")