update access

[millarjeremy]

Morning / Evening Gurus.

I have developed a database and the users are using it... good start!!. Each person has their own version of the mdb and use runtime.

When I make changes in the database, eg each reports, qry, modifications to tables, I want to be able to send these updates to the various people. at present, I have to grab each mdb file and make the modifications and send them back the database.

Is there an easier way??? Not they are not always connected to the network.

Thank you in advance for your assistance.

 

[CJ_London]

Is your db split FE and BE? and all users use a FE which links to the BE?

If so, just sent them a replacement FE for forms, reports, queries and modules. Alternatively put a zipped file of the FE on the network and email them with a link to upload the new file (zipped to discourage them from opening on the network). You can be more sophisticated than this but very much depends on your setup and whether users log in etc.

Ideally you want to have a version number stored in the BE in a table and a routine in your opening/autoexec form so each time they open the FE it checks the version number against the BE and if it doesn't match it closes the system with a request they upload the new FE - you hard code the version number into your code and update it on each new release.

However BE is more of a problem since you can't edit tables whilst other users are 'logged in'. You need to ask them to exit the system whilst you undertake the updates.

If you have not split the db into FE and BE you have a much bigger problem and I strongly recommend you split the db before under taking updates and maintenance otherwise you have a significant chance of db corruption.

 

[millarjeremy]

Thanks mate. I believe you correct that I should really split the fe and be.
Each user may a copy of the be on their machines as thy are remote. Can I send them qry / table updates??

And thank you for your prompt reply.

 

[CJ_London]

If you are using 2003 or earlier you could consider replication of the BE with the master held on the network. However only worthwhile if they can synchronise from the office on a cable. VPN and/or wireless has too high a risk of interuption which will lead to corruption and is very slow. This also presupposes your users are sharing the same data and do not have they own unique datasets - however sounds like this is not the case.

Can I send them qry / table updates

Queries should be in the FE so covered under form etc changes, the BE only contains tables. If you need to make changes to their BE table structure I would write a routine in a small 'update' db to make the changes. You can then send the 'update' db to your users and when it is opened it automatically updates the BE (a bit like running an install routine)- it is not difficult, you can use the SQL CREATE TABLE, ALTER TABLE queries etc to affect the changes to tables, indexes and relationships rather than making changes to TableDefs if you find that easier.