How to modify an mdb and retain user data?

[Guest]

I am just getting my first Access 2000/VBA application complete enough to implement at two sites. I would appreciate advise on what is considered "best practice" for further additions and modifications to such an application once it is in use. Since everything is rolled up into one mdb file, how do you make mods and retain the user's data? Do you export the tables from the old mdb and then import those into the new mdb, or what

ctda

 

[Cheryl Fischer]

The best way to deliver updates of your application to clients or users
without jeopardizing their data is to distribute your application in TWO
databases: a Front-End (containing all Queries, Forms, Reports, Macros and
Modules) and a Back-End containing shared data tables. The vast majority of
your updates will likely be to the Front-End, so you can deliver a new copy
of the MDB or MDE to the clients or users safely and simply instruct them to
copy it to the location where the current Front-End is located.

Here is a link which you may find helpful:

How to manually split a Microsoft Access database:
http://support.microsoft.com/default.aspx?scid=kb;en-us;304932

There is also a Database Splitter utility included in most versions of MS
Access.


--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

I am just getting my first Access 2000/VBA application complete enough to

implement at two sites. I would appreciate advise on what is considered
"best practice" for further additions and modifications to such an
application once it is in use. Since everything is rolled up into one mdb
file, how do you make mods and retain the user's data? Do you export the
tables from the old mdb and then import those into the new mdb, or what?

 

[Guest]

Thanks for the education. This sounds like what I need, but a question or two before I try it

1) Why not use the built-in Access database splitter? Is there a downside to using it

2) I assume that table relationships will remain intact in the Back-End database file. Is this correct

3) Once an MDB is split, what do you do if you want to make changes to a table or to table relationships? Do you have to unsplit it somehow and then split it again

4) How does the Front-End MDB know where the Back-End mdb is located on the network

ctda


----- Cheryl Fischer wrote: ----

The best way to deliver updates of your application to clients or user
without jeopardizing their data is to distribute your application in TW
databases: a Front-End (containing all Queries, Forms, Reports, Macros an
Modules) and a Back-End containing shared data tables. The vast majority o
your updates will likely be to the Front-End, so you can deliver a new cop
of the MDB or MDE to the clients or users safely and simply instruct them t
copy it to the location where the current Front-End is located

Here is a link which you may find helpful

How to manually split a Microsoft Access database
http://support.microsoft.com/default.aspx?scid=kb;en-us;30493

There is also a Database Splitter utility included in most versions of M
Access


-

Cheryl Fischer, MVP Microsoft Acces
Law/Sys Associates, Houston, T

I am just getting my first Access 2000/VBA application complete enough t

implement at two sites. I would appreciate advise on what is considere
"best practice" for further additions and modifications to such a
application once it is in use. Since everything is rolled up into one md
file, how do you make mods and retain the user's data? Do you export th
tables from the old mdb and then import those into the new mdb, or what

 

[Cheryl Fischer]

1) Why not use the built-in Access database splitter? Is there a downside
to using it?

Either method is ok.

2) I assume that table relationships will remain intact in the Back-End

database file. Is this correct?

In Access 2002, the relationships are maintained; I presume they are also
maintained in other versions. In any case, the Database Splitter advises
you to make a copy of the original database.

3) Once an MDB is split, what do you do if you want to make changes to a

table or to table relationships? Do you have to unsplit it somehow and then
split it again?

Relationships between tables should be created and maintained in the
Back-End database.

4) How does the Front-End MDB know where the Back-End mdb is located on

the network?

The Database Splitter links your Back-End tables to the front-end. If you
do not use the Database Splitter, use File|Get External Data to locate the
Back-End database and link to the tables within it.


--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

Thanks for the education. This sounds like what I need, but a question or two before I try it:

1) Why not use the built-in Access database splitter? Is there a downside to using it?

2) I assume that table relationships will remain intact in the Back-End

database file. Is this correct?

3) Once an MDB is split, what do you do if you want to make changes to a

table or to table relationships? Do you have to unsplit it somehow and then
split it again?

 

[Guest]

Cheryl

Thanks VERY MUCH for taking time to answer all my questions. I see now that the developer can make changes to tables and table relationships any time in the Back-End MDB, just like was done before splitting it

I'm still confused about one thing though: When code changes are made, how can you deliver a new copy of the Front-End MDB by just replacing the old one with new. Won't that lose the link that was created when the database was originally split? How does that link get maintained, or re-created

ctda


----- Cheryl Fischer wrote: ----

1) Why not use the built-in Access database splitter? Is there a downsid

to using it

Either method is ok

2) I assume that table relationships will remain intact in the Back-En

database file. Is this correct

In Access 2002, the relationships are maintained; I presume they are als
maintained in other versions. In any case, the Database Splitter advise
you to make a copy of the original database

3) Once an MDB is split, what do you do if you want to make changes to

table or to table relationships? Do you have to unsplit it somehow and the
split it again

Relationships between tables should be created and maintained in th
Back-End database

4) How does the Front-End MDB know where the Back-End mdb is located o

the network

The Database Splitter links your Back-End tables to the front-end. If yo
do not use the Database Splitter, use File|Get External Data to locate th
Back-End database and link to the tables within it


-

Cheryl Fischer, MVP Microsoft Acces
Law/Sys Associates, Houston, T

 

[Cheryl Fischer]

There are a couple of different ways to go about this:

1. If your clients/users are in one location and you are in another with no
connection to their LAN, you can give them a re-link tables option and use
the code found at the Access Web:

http://www.mvps.org/access/tables/tbl0009.htm

2. If you and your users are connected to the same LAN, deliver the
modified Front-End with the proper links already in place. You can use the
UNC for the Back-End MDB to avoid drive letter mapping inconsistencies.

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

Cheryl,

Thanks VERY MUCH for taking time to answer all my questions. I see now

that the developer can make changes to tables and table relationships any
time in the Back-End MDB, just like was done before splitting it.

I'm still confused about one thing though: When code changes are made,

how can you deliver a new copy of the Front-End MDB by just replacing the
old one with new. Won't that lose the link that was created when the
database was originally split? How does that link get maintained, or
re-created?

 

[Guest]

Cheryl

I am connected to the same LAN where I need to install my new app - your option #2. I did some experimenting. When you say deliver the modified Front-End "with the proper links already in place", I believe you mean the following

- using a copy of the modified FE MDB, delete the table relationships and tables in i
- link the tables in the BE MDB (which has the current/live data) to the new FE MDB (which is minus tables) by using the File/Get External Data menu option of Acces

I tried this accross the network and it seems to work just fine. (It doesn't work though if you don't delete the tables in the new FE MDB first.) Just so I can be certain, please confirm that these are the steps you were referring to

Thanks
ctda


----- Cheryl Fischer wrote: ----

There are a couple of different ways to go about this

1. If your clients/users are in one location and you are in another with n
connection to their LAN, you can give them a re-link tables option and us
the code found at the Access Web

http://www.mvps.org/access/tables/tbl0009.ht

2. If you and your users are connected to the same LAN, deliver th
modified Front-End with the proper links already in place. You can use th
UNC for the Back-End MDB to avoid drive letter mapping inconsistencies

-

Cheryl Fischer, MVP Microsoft Acces
Law/Sys Associates, Houston, T

Cheryl

that the developer can make changes to tables and table relationships an
time in the Back-End MDB, just like was done before splitting ithow can you deliver a new copy of the Front-End MDB by just replacing th
old one with new. Won't that lose the link that was created when th
database was originally split? How does that link get maintained, o
re-created

 

[Cheryl Fischer]

That is correct.

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

Cheryl,

I am connected to the same LAN where I need to install my new app - your

option #2. I did some experimenting. When you say deliver the modified
Front-End "with the proper links already in place", I believe you mean the
following:

- using a copy of the modified FE MDB, delete the table relationships and tables in it
- link the tables in the BE MDB (which has the current/live data) to the

new FE MDB (which is minus tables) by using the File/Get External Data menu
option of Access

I tried this accross the network and it seems to work just fine. (It

doesn't work though if you don't delete the tables in the new FE MDB first.)
Just so I can be certain, please confirm that these are the steps you were
referring to.

Thanks.
ctdak


----- Cheryl Fischer wrote: -----

There are a couple of different ways to go about this:

1. If your clients/users are in one location and you are in another with no
connection to their LAN, you can give them a re-link tables option and use
the code found at the Access Web:

http://www.mvps.org/access/tables/tbl0009.htm

2. If you and your users are connected to the same LAN, deliver the
modified Front-End with the proper links already in place. You can use the
UNC for the Back-End MDB to avoid drive letter mapping inconsistencies.

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

Cheryl,

see now
that the developer can make changes to tables and table relationships any
time in the Back-End MDB, just like was done before splitting it. made,
how can you deliver a new copy of the Front-End MDB by just replacing the
old one with new. Won't that lose the link that was created when the
database was originally split? How does that link get maintained, or
re-created?

downside
to using it? Back-End
database file. Is this correct?

they are
also

maintained in other versions. In any case, the Database

Splitter

advises
you to make a copy of the original database.

changes
to a

table or to table relationships? Do you have to unsplit it

somehow
and then

split it again? in the
Back-End database. located on
the network?

front-end.
If you

do not use the Database Splitter, use File|Get External Data

to
locate the

Back-End database and link to the tables within it.Law/Sys Associates, Houston, TX

 

[Guest]

Thanks again or all your help on this
ctda

----- Cheryl Fischer wrote: ----

That is correct

-

Cheryl Fischer, MVP Microsoft Acces
Law/Sys Associates, Houston, T

Cheryl

option #2. I did some experimenting. When you say deliver the modifie
Front-End "with the proper links already in place", I believe you mean th
following

tables in i
- link the tables in the BE MDB (which has the current/live data) to th

new FE MDB (which is minus tables) by using the File/Get External Data men
option of Accesdoesn't work though if you don't delete the tables in the new FE MDB first.
Just so I can be certain, please confirm that these are the steps you wer
referring to

Thanks ctda
There are a couple of different ways to go about this
1. If your clients/users are in one location and you are in anothe

with n
connection to their LAN, you can give them a re-link tables optio and us
the code found at the Access Web

http://www.mvps.org/access/tables/tbl0009.ht
2. If you and your users are connected to the same LAN, deliver th

modified Front-End with the proper links already in place. You ca use th
UNC for the Back-End MDB to avoid drive letter mappin inconsistencies

-
Cheryl Fischer, MVP Microsoft Acces Law/Sys Associates, Houston, T see no
that the developer can make changes to tables and table relationship an
time in the Back-End MDB, just like was done before splitting it made
how can you deliver a new copy of the Front-End MDB by just replacin th
old one with new. Won't that lose the link that was created when th
database was originally split? How does that link get maintained, o
re-created

downsid
to using it Back-En
database file. Is this correct

they ar
als

maintained in other versions. In any case, the Databas

Splitte

advise
you to make a copy of the original database

change

to
table or to table relationships? Do you have to unsplit i

someho
and the

split it again in th
Back-End database located o
the network

front-end
If yo

do not use the Database Splitter, use File|Get External Dat

t
locate th

Back-End database and link to the tables within itLaw/Sys Associates, Houston, TX