Automatic updating?

[Paul]

Hi Group,

I hope this is the correct forum for this question - if it
is not please accept my apologies and I will repost
correctly.

On a weekly basis, I update my database tables with new
information. This new data must then be made available to
all users. Unfortunately, due to security restrictions,
the databases are not networked. To make the data
available to all users, I email the whole database to
everyone, provide instructions, and get them to overwrite
the whole database.

My question is, and I hope someone can answer, is there a
better and more efficient way of doing this? Because of
limited staff IT skills etc the whole procedure is
becoming confusing and very time consuming.

Looking forward to a reply

Regards

Tony

 

[Arvin Meyer]

Networking is more secure than what you are doing now. At least with
networking you can set group policies on how you require users to
authenticate. You have little control over who sees or intercepts the email,
nor what they can do after they've received it.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Nikos Yannacopoulos]

Paul,

If your weekly updates consist of new record additions only (no changes in
existing records), then one way to make it more efficient would be to:
1. Create an Excel Workbook with one sheet for each table you need to
update, and paste the new records in it, then send it out to the users
2. In the database, link each sheet of the workbook as a table, and make and
append query (from each linked sheet to the corresponding table)
3. On a form in the database, add a command button to run some code or a
macro that will run the append queries
The Excel workook should sit in a folder that is common to all users' PC's,
e.g. C:\Temp or a special folder for the case. Then upon receipt of the
workbook each week, a user would have to save it in the designated folder
(overwriting the existing one, if not deleted), click the Update botton in
the database and be done. I would take some additional measures to make
sure the new records are not appended more than once, such as deleting the
spreadsheet through the code behind the button after the append action.

If your weekly updates include changes in existing records, then it gets a
bit more complicated, but can still be done (though not very neat).

HTH,
Nikos

 

[Nikos Yannacopoulos]

Paul,

If your weekly updates consist of new record additions only (no changes in
existing records), then one way to make it more efficient would be to:
1. Create an Excel Workbook with one sheet for each table you need to
update, and paste the new records in it, then send it out to the users
2. In the database, link each sheet of the workbook as a table, and make and
append query (from each linked sheet to the corresponding table)
3. On a form in the database, add a command button to run some code or a
macro that will run the append queries
The Excel workook should sit in a folder that is common to all users' PC's,
e.g. C:\Temp or a special folder for the case. Then upon receipt of the
workbook each week, a user would have to save it in the designated folder
(overwriting the existing one, if not deleted), click the Update botton in
the database and be done. I would take some additional measures to make
sure the new records are not appended more than once, such as deleting the
spreadsheet through the code behind the button after the append action.

If your weekly updates include changes in existing records, then it gets a
bit more complicated, but can still be done (though not very neat).

HTH,
Nikos