Split databases

P

PeterJC

I'm about to split an Access 2007 database for use on a network. Suppose I
want to add a data table to the database? Any functional changes would be
made in the front end, but where would I put the table? In the back end? But
how would the links to the table be established?
Thanks in anticipation.
 
A

Albert D. Kallal

PeterJC said:
I'm about to split an Access 2007 database for use on a network. Suppose I
want to add a data table to the database? Any functional changes would be
made in the front end, but where would I put the table? In the back end?
But
how would the links to the table be established?
Thanks in anticipation.

For each new table you add, you create the table in the back end (same goes
for any table modifications, or adding of fields). You then simply use hte
external data tab, choose access and the link option...you then browse to
the back end file, and thus link in the ONE new table.

Keep in mind that we often develop applications off site. In fact what this
means is that often you'll be developing and testing your software on a copy
of the backend database AND the front end. Thus is really nice since then
during the development process you are to free to enter test data, delete
records, enter junk into the database, and not worry about damaging or
playing with the actual real production data that's so important to your
organization. As a developer I find it very hard to tippy toe around real
live data that's in use by everyone. So work on both a copy of the front end
and the backend. I can then play around and even test dangerous deletion
routines or enter junk with without any fear of damaging valuable data, it's
kind of like the idea try to work on an airplane with passengers in while
you're flying, versus it's sitting in the garage where you've totally free
to test all kinds of things without serious consequences if you make a
mistake.

So, if you make changes to the back end (such as adding new fields), or in
your case adding a new table, then when I do is start a little note pad
document on the list of changes I'm made. The notepad document will look
like this:

add new column Location (text 25)-- > to table customers

copy new table called MeetingDates

etc etc.

In the above you can see my notes that I added a new column called location
to table customers in my test copy of the development back end system
(remember I have a copy of both the backend database, and the front and
program on my test development machine). So as I develop along, if I make
any changes to the back end database table(s), then I make an entry note in
a small notepad document of what I done.

Then when I am ready to deploy the production database, I have to have
everybody stop working for at least a little bit of time. (you might send
out a company memo, or simply schedule certain amount of downtime when
you're ready to deploy this new update to your users). You then open up
the production backend database directly. You now look at your notepad
"list" of changes you made and quickly knock off each little changes made in
that list to the production database. So from the above example I open up
table customers in design mode, and add a new column called location as a
text column of 25.

I then need to add the table called meetingDates. While in this production
backend database, I will then use a file get -> external data and import the
new table I created called MeetingDates from the development back end. You
could also just create a new table from scratch and manually add the columns
to this table. However, it usually better to import the whole table with all
the indexes and column names and everything else already set up (and you
have this table in your testing/development back end...so, you might as well
use it). Copying in the table makes less mistakes then recreating the table
from scratch. So, once I've modified and added the new column to the one
table, and also added the additional tables I've created, you then ready to
deploy you new version of your software.

So, you then link your new front end to the production back end (that was
previous linked to the testing and development back end). You then create a
an accDE, and it is this accDE that is then distributed to all of your users
desktops.
 
P

Peter Hibbs

Peter,

If you want to make changes to tables in a back-end database file then
have a look at the Back End Update Utility code at :-

http://www.rogersaccesslibrary.com/...p?FID=21&SID=1467163adzcf2ff6c866b65a8772d953

If you need to re-link the front end to the back end at the user's
site (which you probably will) then have a look at the Back End
Re-Linker code on the same site.

If the users are using a network and you need to replace a number of
front-end files on several computers then have a look at the Front End
Updater Utility which provides a VB program to do that automatically.

HTH

Peter Hibbs.
 
P

PeterJC

Thanks for your help, Albert.
--
PeterJC


Albert D. Kallal said:
For each new table you add, you create the table in the back end (same goes
for any table modifications, or adding of fields). You then simply use hte
external data tab, choose access and the link option...you then browse to
the back end file, and thus link in the ONE new table.

Keep in mind that we often develop applications off site. In fact what this
means is that often you'll be developing and testing your software on a copy
of the backend database AND the front end. Thus is really nice since then
during the development process you are to free to enter test data, delete
records, enter junk into the database, and not worry about damaging or
playing with the actual real production data that's so important to your
organization. As a developer I find it very hard to tippy toe around real
live data that's in use by everyone. So work on both a copy of the front end
and the backend. I can then play around and even test dangerous deletion
routines or enter junk with without any fear of damaging valuable data, it's
kind of like the idea try to work on an airplane with passengers in while
you're flying, versus it's sitting in the garage where you've totally free
to test all kinds of things without serious consequences if you make a
mistake.

So, if you make changes to the back end (such as adding new fields), or in
your case adding a new table, then when I do is start a little note pad
document on the list of changes I'm made. The notepad document will look
like this:

add new column Location (text 25)-- > to table customers

copy new table called MeetingDates

etc etc.

In the above you can see my notes that I added a new column called location
to table customers in my test copy of the development back end system
(remember I have a copy of both the backend database, and the front and
program on my test development machine). So as I develop along, if I make
any changes to the back end database table(s), then I make an entry note in
a small notepad document of what I done.

Then when I am ready to deploy the production database, I have to have
everybody stop working for at least a little bit of time. (you might send
out a company memo, or simply schedule certain amount of downtime when
you're ready to deploy this new update to your users). You then open up
the production backend database directly. You now look at your notepad
"list" of changes you made and quickly knock off each little changes made in
that list to the production database. So from the above example I open up
table customers in design mode, and add a new column called location as a
text column of 25.

I then need to add the table called meetingDates. While in this production
backend database, I will then use a file get -> external data and import the
new table I created called MeetingDates from the development back end. You
could also just create a new table from scratch and manually add the columns
to this table. However, it usually better to import the whole table with all
the indexes and column names and everything else already set up (and you
have this table in your testing/development back end...so, you might as well
use it). Copying in the table makes less mistakes then recreating the table
from scratch. So, once I've modified and added the new column to the one
table, and also added the additional tables I've created, you then ready to
deploy you new version of your software.

So, you then link your new front end to the production back end (that was
previous linked to the testing and development back end). You then create a
an accDE, and it is this accDE that is then distributed to all of your users
desktops.
 
P

PeterJC

Thanks, Peter.
--
PeterJC


Peter Hibbs said:
Peter,

If you want to make changes to tables in a back-end database file then
have a look at the Back End Update Utility code at :-

http://www.rogersaccesslibrary.com/...p?FID=21&SID=1467163adzcf2ff6c866b65a8772d953

If you need to re-link the front end to the back end at the user's
site (which you probably will) then have a look at the Back End
Re-Linker code on the same site.

If the users are using a network and you need to replace a number of
front-end files on several computers then have a look at the Front End
Updater Utility which provides a VB program to do that automatically.

HTH

Peter Hibbs.
 
A

Armen Stein

You're also welcome to use our free J Street Access Relinker at:
http://www.jstreettech.com/downloads

It handles multiple Access back-end databases, ignores ODBC linked
tables, and can automatically and silently relink to back-end
databases in the same folder as the application (handy for work
databases or single-user scenarios). There's a ReadMe table with
instructions.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
T

Tony Toews [MVP]

Peter Hibbs said:
If you want to make changes to tables in a back-end database file then
have a look at the Back End Update Utility code at :-

http://www.rogersaccesslibrary.com/...p?FID=21&SID=1467163adzcf2ff6c866b65a8772d953

That procedure can make some sense if you are dealing with remote
sites. However it sounds like the user is working right at the only
network in which the app is deployed. In which case your solution is
overkill.

Even that I'd suggest Compare'Em
http://home.gci.net/~mike-noel/CompareEM-LITE/CompareEMscreens/CompareEM-About.htm
as that is a lot more convenient to use. You just need to point the
utility to the old version of the backend and the new version and it
generates the VBA code automatically.

Tony
 
T

Tony Toews [MVP]

PeterJC said:
I'm about to split an Access 2007 database for use on a network. Suppose I
want to add a data table to the database? Any functional changes would be
made in the front end, but where would I put the table? In the back end? But
how would the links to the table be established?

You've got some good answers on splitting and linking of tables. I'd
also suggest using the Auto FE Updater to distribute the updates to
your users.

For more info on the free Auto FE Updater utility see
http://www.autofeupdater.com to keep the FE on each PC up to date.

Don't be afraid to deploy a new version 2 or 3 times a day to fix
small irritating bugs the users have located. No need to be on a
rigid weekly or whatever schedule.

Ultra Frequent Application Deployment
http://www.granite.ab.ca/access/ufad.htm

Tony
 
P

PeterJC

Thanks, Tony. YOu're right, we have a very small network and I'm part of it,
so your comment is well taken.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top