Update tables without overwriting data ?

[SpookiePower]

I have made a database for my work. This database I have spilt
in a front and back-end. I'm still working and improving this database
on my private computer at home. I know that if I make any changes
to the front-end, I can just overwrite the front-end at work with the
new front-end made at home. But my question is - If I make any changes
to the back-end, like adding a new table, adding a new field or things like that. How can
I then add these new updates I have made from home, to the back-end
at work without overwriting all the data in the tables ??

 

[Guest]

Spookie,

The best way is to prepare a detailed list of all of the changes that you
need to make. You'll need to wait until everyone else is out of the database,
in order to open it in exclusive mode. First make a back-up copy, just to be
safe. Then use your list to make the required changes.

You should plan on distributing an updated copy of the Front-end as well.
The reason is that it is always a good idea to delete linked tables in a FE
database, compact the DB, and then re-create the linked tables from scratch
whenever you make any design changes to the BE database. Access caches a lot
of information about linked tables, which may not be up-to-date in your users
existing copies of the FE DB, if you go making design changes.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[SpookiePower]

Spookie,

The best way is to prepare a detailed list of all of the changes that you
need to make. You'll need to wait until everyone else is out of the database,
in order to open it in exclusive mode. First make a back-up copy, just to be
safe. Then use your list to make the required changes.

What you mean is that I can not import the updates/changes I have made to
the BE at home, into the BE at work ? And I have to make the updates/changes
into the BE at work ?

You should plan on distributing an updated copy of the Front-end as well.
The reason is that it is always a good idea to delete linked tables in a FE
database, compact the DB, and then re-create the linked tables from scratch
whenever you make any design changes to the BE database. Access caches a lot
of information about linked tables, which may not be up-to-date in your users
existing copies of the FE DB, if you go making design changes.

I'm not sure if I understand it the right way.
But is it not possible to compact a FE that is linked to a BE ? and if I want
to compact the FE, I have to delete the link between the FE and BE first,
and then make the link again after the compact is done.

I only thought that a compact would do work on a FE and not on the
BE.

 

[Guest]

Hi Spookie,

What you mean is that I can not import the updates/changes I have
made to the BE at home, into the BE at work ?

You can programmatically import a table from one database to another, but
opening the target database and simply copying from the source database is a
lot easier. In any case, you need to open the BE at work in exclusive mode in
order to make any design changes. That means, simply, that no one else can
have the database open at the same time.

You can even programmatically create fields in a remote database, but that's
going to be a whole lot more work than simply opening the BE database and
adding the field via the graphical user interface. There was a utility
marketed at one time--I forget the name of this utility--but it was
advertised to be able to collect changes that you made in the structure of a
development copy of a database and add these changes into a target database.
Of course, this utility was not free.

But is it not possible to compact a FE that is linked to a BE ?

It's certainly possible to compact a FE. However, this only compacts the
FE--it does not compact the BE.

and if I want to compact the FE, I have to delete the link between the
FE and BE first, and then make the link again after the compact is done.

Deleting the linked tables, and compacting the FE database, is something
that you should do if you have made changes to the structure of the BE
database. I have personally helped troubleshoot queries in a FE database at
work that were returning the wrong results, after a person made changes to
the structure of the BE database. Your mileage may vary. Compacting the FE
after deleting the links insures that you drop all cached information about
the table links. This way, there is no invalid information stored in the
hidden system tables (tables whose names start with MSYS).


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[SpookiePower]

You can even programmatically create fields in a remote database, but that's
going to be a whole lot more work than simply opening the BE database and
adding the field via the graphical user interface. There was a utility
marketed at one time--I forget the name of this utility--but it was
advertised to be able to collect changes that you made in the structure of a
development copy of a database and add these changes into a target database.
Of course, this utility was not free.

This is excatly what I was looking for. But so fare it seems to me
that I have to write the updates to the BE twice. Once at home,
where I develop and test the BE, and then at work, where I add
the updates by writing all the fields,tables and so on.

It's certainly possible to compact a FE. However, this only compacts the
FE--it does not compact the BE.

I did not know that it works this way.

I'm not really sure how to do this compact-thing when I have a spilt
database. But what if I put the FE and BE together again into one
mdb file (if it is possible) after a update and then do a compact,
and then after that, spilt them again. Can this be done this way ?

What if I only have made changes to the FE, do I then have to
make a compact ?

 

[Guest]

Hi Spookie,

I woke up this morning remembering the name of the utility I mentioned in my
last post. It is called Surgical Strike and is marketed by databasecreations,
inc.:

http://www.databasecreations.com/prod_surgicalstrike.htm

Scrolling to the bottom of this page, it looks like the cheapest copy will
run you $199.

I'm not really sure how to do this compact-thing when I have a spilt
database.

It's actually quite simple. You can compact a local FE .mdb file anytime you
like, as long as it is not being shared by more than one user (which it
shouldn't be). To compact the BE .mdb file, you must be able to open it
exclusively. That means that all other users have to be out of the file. As a
database administrator, it's a good idea to make periodic back-ups of the BE
database anyways. The most reliable backup copy is made when the file is not
opened by anyone.

I think you are confusing the process of compacting a database with linking
from a front-end to a back-end database. These are two completely different
and independent processes. While it's certainly very easy to combine the FE
and BE back into one file, there's nothing to be gained by doing this.

What if I only have made changes to the FE, do I then have to
make a compact ?

It's a good idea to compact your databases on a fairly frequent basis. This
applies to both the FE and the BE databases. Simply put, you need exclusive
access to compact any database.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[SpookiePower]

Hi Spookie,

I woke up this morning remembering the name of the utility I mentioned in my
last post. It is called Surgical Strike and is marketed by databasecreations,
inc.:

http://www.databasecreations.com/prod_surgicalstrike.htm

Thanks alot.

I think you are confusing the process of compacting a database with linking
from a front-end to a back-end database. These are two completely different
and independent processes. While it's certainly very easy to combine the FE
and BE back into one file, there's nothing to be gained by doing this.

I did not know that it was possible to compact a FE and BE. I thought that
they had to be in one mdb file to do a compact. Thanks

 

[Guest]

I did not know that it was possible to compact a FE and BE. I thought that

they had to be in one mdb file to do a compact. Thanks

They are two separate .mdb files. Each one can be compacted independently of
the other. However, as I stated before, if you make any design changes to the
BE database, it is best to make sure that any cached table link information
in the FE database is completely up to date. You do this by deleting the
linked tables, compacting the FE (to discard the old table link information)
and then re-create the linked tables. Using Surgical Strike would not prevent
the need to do this.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[SpookiePower]

They are two separate .mdb files. Each one can be compacted independently of
the other.

It took me a while to find out

However, as I stated before, if you make any design changes to the
BE database, it is best to make sure that any cached table link information
in the FE database is completely up to date. You do this by deleting the
linked tables....

Delete linked tables..I'm not sure how to do this.

 

[Guest]

Hi Spookie,

Delete linked tables..I'm not sure how to do this.

To delete the linked tables in the FE database, simply select the table
(actually a shortcut to the table) in the database window and press the
delete button. Each linked table that you select should have a black arrow
pointing to the right. After deleting all linked tables, compact the FE
database. It is helpful to hold down the shift key, to prevent any startup
code from running when the database re-opens. Then, use File > Get External
Data > Link Tables... to re-establish the linked tables.

If you have lots of linked tables, you can write a VBA procedure to delete
only the linked tables, leaving any local tables. Post back if you would like
to explore this further.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[SpookiePower]

Thanks a lot. You have really helped me getting better at this.
Thanks