Update Tables

S

Scafidel

Using Access 2007, I created a database and then copied it to several
employees' computers in different locations. From time to time, I refine and
update my database with added fields, etc to address problems, needs and /or
improvements. These include changes to the Form, Table and VBA. Is there a
(simple) way to update the other computer databases? By email?
Thanks
 
A

aaron.kempf

Yes, keep everything where it belongs-- on a database server.
Upsize to SQL Server and choose ADP.

That is the best, easiest-- most direct route to sharing your data and
objects with multiple users.
WIth Access MDB / AccDB, you would need to copy this to each desktop
to do this.

So do you _REALLY_ want to maintain things in 20 different places-- or
in _ONE_ place; where data belongs-- on a db server?

-Aaron
 
J

Jerry Whittle

For the forms, reports, queries, etc., splitting the database is the answer.
The front end (FE) has all the "stuff", except for the tables. They are kept
in the back end (BE). If you make a change to a form, for example, just send
them a copy of the new FE.

Tables are another matter. In a perfect world, you would have all the
requirements and the table structure would be set in stone. I usually add
some extra text, date/time, number (double), and yes/no fields to each table.
That way if I need another field, it's already there. Might not have the best
names, but it works.
 
S

Scafidel

I know, I know. But, this is a trial project on 6 computers presently, with
no direct sharing. I hope to put it on the server in the future, but right
now, this is all they want, so I have to work with what I have.
--
Scafidel
Lafayette, Louisiana


[email protected] said:
Yes, keep everything where it belongs-- on a database server.
Upsize to SQL Server and choose ADP.

That is the best, easiest-- most direct route to sharing your data and
objects with multiple users.
WIth Access MDB / AccDB, you would need to copy this to each desktop
to do this.

So do you _REALLY_ want to maintain things in 20 different places-- or
in _ONE_ place; where data belongs-- on a db server?

-Aaron
Click to expand...
 
R

Roger Carlson

Jerry is correct, this should be split into a Front-End and a Back-End.

Tony Toews has a utility calle the Auto FE Updater
http://www.granite.ab.ca/access/autofe.htm
which will keep your Front-ends in sync. On my website
(www.rogersaccesslibrary.com), is a small Access database sample called
"SQLLoader.mdb"
http://www.rogersaccesslibrary.com/download3.asp?SampleName=SQLLoader.mdb
which illustrates how to update the structure of a remote Back-End database.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
T

Tony Toews [MVP]

Scafidel said:
Using Access 2007, I created a database and then copied it to several
employees' computers in different locations. From time to time, I refine and
update my database with added fields, etc to address problems, needs and /or
improvements. These include changes to the Form, Table and VBA. Is there a
(simple) way to update the other computer databases? By email?
Click to expand...

You want to split the MDB into a Front End MDB containing the queries,
forms, reports, macros and modules with just the tables and
relationships in the Back End MDB. The FE is copied to each network
users computer. The FE MDB is linked to the tables in the back end
MDB which resides on a server. You make updates to the FE MDB and
distribute them to the users, likely as an MDE.

See the "Splitting your app into a front end and back end Tips" page
at http://www.granite.ab.ca/access/splitapp/ for more info. See the
Auto FE Updater downloads page
http://www.granite.ab.ca/access/autofe.htm to make this relatively
painless.. The utility also supports Terminal Server/Citrix quite
nicely.

I also am using a product called Compare'Em to create the VBA code to
make the changes to the backend tables, indexes and relationships. It
needs some minor improvements but overall works well. Compare'Em
http://home.gci.net/~mike-noel/CompareEM-LITE/CompareEM.htm

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/
 
T

Tony Toews [MVP]

Scafidel said:
Using Access 2007, I created a database and then copied it to several
employees' computers in different locations. From time to time, I refine and
update my database with added fields, etc to address problems, needs and /or
improvements. These include changes to the Form, Table and VBA. Is there a
(simple) way to update the other computer databases? By email?
Click to expand...

Be advised that Aaron's answer to everything is SQL Server and/or ADP.

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/
 
Top