Updating tables

[Tony Williams]

I have a database that is split between front end (on 6 PC's) and a backend
(on a server).
I have updated a couple of the tables with new fields and changed field
names and added a new one table. How do I get the new table definitions into
the existing backend without having to manually recreate my changes? The
current tables have data that we don't want to loose so I can't just import
the tables from an external database.
Thanks
Tony

 

[Douglas J. Steele]

The tables are all in the back-end. None of the data is actually in the
front-ends. All you need do is give each user a copy of the new front-end,
replacing their existing copy.

 

[Guest]

Sorry Douglas I didn't make myself clear. I've changed the tables in the back
end and these need updating into the current backend.
Thanks
Tony

 

[Rick Brandt]

Sorry Douglas I didn't make myself clear. I've changed the tables in
the back end and these need updating into the current backend.

With an MDB back end those changes are usually reflected automatically. If not,
use the Linked Table Manager to refresh the links.

 

[Douglas J. Steele]

You've made yourself even less clear now. <g>

You can go into the existing backend and make the changes, or you can write
code to make the changes for you (either DAO or ADOX or DDL)

There are third product products that can help in this matter. Take a look
at http://www.fmsinc.com and http://www.ssw.com.au/

 

[Peter Hibbs]

Tony,

Use the code at :-
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='BE Update Utility.mdb'
which will do it automatically for you.

HTH

Peter Hibbs.

 

[Guest]

Sorry! Let me start again.
I have an existing database that is split between backend(on a server) and
front end (on 6 PC's). I took a copy of the backend mdb file, because I
couldn't work on the live file. I mad changes to some of the tables and added
another new one. I now want to replace the current backend file with my
amended version. However the current version will now hold more uptadate data
which I don't want to overwrite by importing the new tables. How do I get the
data from the current backend into my new backend or conversely how do I get
the updates to the tables in my amended backend into the current backend.
Hope I've explained that properly this time. Apologies.
Thanks
Tony

 

[Douglas J. Steele]

You can either make the same changes to the existing backend (using the
methods I mentioned before), or you can write queries to take the data from
the existing database and transfer it to the new one.

 

[Guest]

Would the queries be Update queries? Would I have to import the existing
tables with another name into the new backend database and then use the
update queries or can I use update queries across 2 database files?
Sorry if these are simple questions, never done this before.
Tony

 

[Tony Toews [MVP]]

I have a database that is split between front end (on 6 PC's) and a backend
(on a server).
I have updated a couple of the tables with new fields and changed field
names and added a new one table. How do I get the new table definitions into
the existing backend without having to manually recreate my changes? The
current tables have data that we don't want to loose so I can't just import
the tables from an external database.

Compare'Em
http://home.gci.net/~mike-noel/CompareEM-LITE/CompareEM.htm

It's a good utility but with some minor flaws that I can live with.
I'd suggest emailing the author with your comments about the flaws as
you find them so we can pressure him into fixing them. <smile>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

or you can write queries to take the data from
the existing database and transfer it to the new one.

IMO though that's a lot more work then adding the new tables, fields,
indexes and relationships using DAO.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Guest]

Thanks Tony, I'll have a look at CompareEm. I must admit the though of
writing all those update queries was a little daunting for a realtive novice
like me!
Tony

 

[Douglas J. Steele]

Since you're trying to take data that exists in TableA in the old database
and ensure that it's in TableA in the new database, you'd be using an Append
query. If you've actually populated the new columns in new database, don't
forget to do that to any new rows you may be adding.

If you're concerned that existing data in Database2 was changed in Database1
since you made the copy, you might want to look at my November, 2003 "Access
Answers" column in Pinnacle Publication's "Smart Access" where I show how
you can run a single query that will add new records to the second table,
and update existing ones if necessary. You can download the column (and
sample database) for free at
http://www.accessmvp.com/djsteele/smartaccess.html

You don't need to copy the tables from one database to another. You can
link, or you can write queries that'll work across databases. Within
Database2, the SQL of your queries can refer to the table in Database1 as
[;Database=E:\Folder\Database1.mdb].TableA

 

[Guest]

Thanks Douglas I'll have alook at your article. I may run into problems with
the queries as I haven't much experience of using append queries in this way
so I may come back but I'll do that in the Queries forum.
Thanks again
Tony

Since you're trying to take data that exists in TableA in the old database
and ensure that it's in TableA in the new database, you'd be using an Append
query. If you've actually populated the new columns in new database, don't
forget to do that to any new rows you may be adding.

If you're concerned that existing data in Database2 was changed in Database1
since you made the copy, you might want to look at my November, 2003 "Access
Answers" column in Pinnacle Publication's "Smart Access" where I show how
you can run a single query that will add new records to the second table,
and update existing ones if necessary. You can download the column (and
sample database) for free at
http://www.accessmvp.com/djsteele/smartaccess.html

You don't need to copy the tables from one database to another. You can
link, or you can write queries that'll work across databases. Within
Database2, the SQL of your queries can refer to the table in Database1 as
[;Database=E:\Folder\Database1.mdb].TableA

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Would the queries be Update queries? Would I have to import the existing
tables with another name into the new backend database and then use the
update queries or can I use update queries across 2 database files?
Sorry if these are simple questions, never done this before.
Tony

 

[Tony Toews [MVP]]

Thanks Tony, I'll have a look at CompareEm. I must admit the though of
writing all those update queries was a little daunting for a realtive novice
like me!

Likewise for me. It's pretty picky work as you have to do this in
table relational sequence. By that I mean that you'd have to append
all lookup tables, then the header tables then the details tables.

Let's take a typical invoicing app as an example with a number of
tables. Invoice type, customer type, customer, product categories,
product items. invoice header and invoice details. You'd have to
append them in the following sequence:

1) Any of Invoice type, customer type, product categories,
2) Any of customer, product items.
3) invoice header
4) invoice details

Then compact it once you're done.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Guest]

UGH! Sounds tricky. I'm looking at your suggestion CompareEm and one from
Peter Hibbs which also looks interesting.
Thanks again for your input.
Tony