Help on Database Maintenance

[jer007]

I'm very close on completing my db and puting it on my network for use. I have some plans for some possible additions down the road to the db. Is there a way to place a working copy of the db on the network but keeping a maintenance copy on my PC to work on and then when I've completed an add-on or modification to add it to the working copy without corupting data?

Thanks for your input

Jeremy

 

[kevinf2349]

Split the database into a backend database (containing all the tables) and a front end database (I usually prefer an MDE file).

That way the data is seperate from the coding enabling you to roll out new code without fear of losing data. There are plenty of examples and discussion on these fora on how to do it...but it is pretty darn easy

Hope this helps

 

[JonMa]

In Access I've used to replicate databases.
It means that you have one Development Replica (Main) and one or more pure replicas (copies). Extra system tables are generated because of it.
You can make a copy of your database (backup) and then go into access.mdb (see TOOLS).

What I've done is to have a copy on the server and then.
1. Take FTP from server down to my PC.
2. Stop activity on the server.
3. Run Replica (see below).
4. Make changes to the development replica.
5. Make another copy from development replica which I upload to server (FTP replace).
6. Start activity on the server.


'*******************************************************
' DirectSync REPLICA.
' From Web to Development Master use jrSyncTypeImp
'*******************************************************
Dim oReplica
Const jrSyncTypeExp = 1 'From Development to web-OneWay.
Const jrSyncTypeImp = 2 'From web to Development-OneWay.
Const jrSyncTypeImpExp = 3 'TwoWay.

Const jrSyncModeInDirect = 1
Const jrSyncModeDirect = 2
Const jrSyncModeInternet = 3

Set oReplica = CreateObject("JRO.Replica")
oReplica.ActiveConnection = "C:\REP\Development.mdb"
oReplica.Synchronize "C:\REP\Web.mdb", jrSyncTypeImp, jrSyncModeDirect
Set oReplica = Nothing

 

[jer007]

Thanks for the ideas. I've been looking at something else and I'm wondering if it would work. If I were to have my db on the network and make changes to a copy on my local machine, would it work to use the File->Get External Data->Import option to bring the information from my updated db to the network copy and under the option "Import Tables" select "Definition Only" instead of the default "Definition and Data" to prevent the data in the tables from changing?

 

[hpicken]

I use a great utility from Tony Toews

http://www.granite.ab.ca

Basically it installs on each user's PC and every time they open the FE application it checks to see if there is a later version, downloads it to their PC and then opens it.

This way I can develop updates save them as an MDE file on the server without having to do any thing else.

This serves two purposes. The first, the user always has the latest version and secondly the FE file runs from the user's PC and therefore faster.

Hope this helps.

Howard

 

[hpicken]

I've just chaecked the link and the full link is

http://www.granite.ab.ca/access/autofe.htm

Howard

 

[randy700]

jer007,

Your import idea will work as long as you are very careful and follow a few additional steps. Importing in the manner you suggest will give you an additional (empty) table.

Example:
you have a table named tblEmployees
you import (structure only) a new tblEmployees
your imported table will be named tblEmployees2

You will also have to design a query that copies all data in tblEmployees to tblEmployees2, delete tblEmployees, and rename tblEmployees2. If relationships exist, they will have to be recreated.

Most design changes involve forms and reports, not tables. I would opt for the FE/BE method because you can replace the front end without fear of corrupting or losing data. If you do have design changes that involve tables....
get all users out of the application.
in your new copy, link to the tables in the original db.
copy all data into the new table.
replace the back end with the new version.
Unless you added new tables, all links should still work.


Randy

 

[jksmi]

Hi, if you use the import option don't bring in any VB modules, classes or forms with modules if you are using A2K. it will corrupt your database, this is a known bug (service pack 4 is supposed to fix it but it hasn't on my machine)