Updating Access Database Structure At Run-Time

Visual Basic
I have an app that uses an access database for its back end. When a program update is issued someone has to manualy add the new fields, queries etc to the clients db file. Is there a way i can mimic the development database schema and update the database from the file upon installation of the program update? Thankyou in advance

Answer Wiki

Thanks. We'll let you know when a new response is added.

I use a seperate access program to update the front ends.

The prog has one form with one button on it called “Install Upgrade”. This button then runs VBA code or Queries to update either the FE or BE tables etc. Below is an example of the VBA to add a new field.

Function AddNumField()

Dim db As Database, tdf As DAO.TableDef, fld As DAO.Field
Dim prp As DAO.Property
On Error Resume Next
Set db = OpenDatabase(“M:SMDatasmdata.mdb”)
Set tdf = db.TableDefs!Units
db.Execute “ALTER TABLE Units ADD COLUMN InstallFee CURRENCY;”
db.Execute “UPDATE Units SET InstallFee = 0 ;”

‘Set fld = tdf.Fields!InstallFee
‘Set prp = fld.CreateProperty(“Format”, , “Currency”)
‘fld.Properties.Append prp
‘Set prp = fld.CreateProperty(“DisplayControl”, dbInteger, acCheckBox)
‘fld.Properties.Append prp

Set db = Nothing

End Function

I have functions for adding different types of fields in a module and then just select (and modify) the function to suit. I even use this type of thing to populate any new fields afterwards.

It’s only limited by your imagination.

Discuss This Question: 2  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Middert
    Thankyou for your reply. I i kind of didnt want to have to remember all the different fields that were added since the previous upgrade was installed. For now i generate a clean empty DB and then in my splash screen check for the UpdateDB flag in my ini file, if an update is present i use ADOX and transfer data from old db to new db and then remove old db and rename new db. Not the best solution for the time being but it got the job done. Also doing it this way i didnt have to note any new queryies of adjustments to queries. Thank you for replying.
    0 pointsBadges:
  • Pphillips001
    Hiya, I don't know what sort of size db we are talking here - but it is standard practice to implement db changes through the use of scripts. Each script evolves the db structure from one deployed version of the front end to the next and retains all the data. When you roll out the product it checks to see the last script it has ran and runs all of the outstanding scripts to bring it uptodate. This can all be handled in the front end code. Doing what you are at the moment is fine in pre-beta implementations but will cause vast headaches when the db matures. I've worked on Access DBs up-to enterprise level systems and the same applies in that it's the only way to go. If anyone has a better idea - please let me know! Ta Paul ----------------------------------------------------------- The road to ultimate knowledge is started by assuming we know nothing. (Archimedes)
    0 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: