Seren said:
I have five same Access database programs in five different locations.
These places will then send their data to a central location and dump their
data where it needs to be stored until all the information stored is sent
back to these five places.
Two questions.
1) What is the best way to export table data? Output it as excel or as a
table. What options do I have?
There isnt really any best way, it depends on what you want to do.
You can export it to a file to be sent to your central location in numerious
formats such as .csv, .xls, .html, fixed width etc etc, and even in your own
special format if you want. If you intend to open/view this exported data
file then I would suggest either .csv or .xls format as these are easy to
read in MS Excel. Personally I like use text (csv) or .xls files as they
are small and easy to send via email or the network, plus they are
application independant wrt your database which means your central (remote)
location could theoretically use a totally different version of MS Access
and not have compatibility problems to deal with.
Lets assume you are happy to use .xls format, ie the 5 locations will each
create a .xls file form their tables and then send these to you. The
instructions below dont really change much if you want a different format.
Depending on your version of Access, if your number of tables is small, and
if you "trust" your locations to do things properly you could just get them
to right click their tables and select SendTo... Get them to choose .xls
format, and then the table gets exported and put automatically into an email
ready for them to send to you.
The disadvantage of the above is they have to do it for every table, every
month.
An alternative is to create a macro which does this automatically for all
tables. Select the macros tab/object. Click New. In the Action choose
TransferSpreadsheet (if you were wanting to export a different way then you
would use a different action, eg TransferText, or TransferDatabase etc).
Set
TransferType: Export
SpreadsheetType: <select the entry relevant for your org>
TableName: <insert your table you are exporting>
FileName: <name of file you want to be created for this table> **NOTE**
Access has problems with spaces here! If you want to use spaces then post
back and get insructions on how to do this
HasFieldNames: up to you here
Range: Leave blank for the export, if you dont it will crash.
Enter a new action on a new line if your macro for each of the tables you
want to export. So for example if you have 5 tables you will have 5
transferSpreadsheet actions in your 1 macro.
When this macro is executed it will automatically export your table data
into the specified files ready to be sent to the central location.
2) The information is updated weekly and the central data node needs to
have an updated information. How can I get central table to update only the
fields that has changed since the last update?
Well... if a field has not changed it shouldnt make any difference if you
overwrite/update it should it...? So you could just do a blanket update on
all your new data...
Do you have a current data issue between sites? for example site A may have
updated record 2 but sites B and C also use this data. How will you know
that site A is the "current" data and not accidentally update the
master/central data when you get B and C's data sent? You will probably
need to use dates to get around this so you can establish which is the most
recent data.
Lets assume for some reason the above is not true.
Import your new data into a temporary table (you will need 1 temp table for
each of the exported tables above).
Create a query which compares the temp records to your existing records and
only returns the records from your temp table which have changed. This
probably has 2 separate steps:
1. return all records in the temp table which are new, ie there are no
matching records in the existing data tables. If you need help with this
sort of outer join query post back and we will tell you how to do this.
You will APPEND these records to your existing data table.
2. return all records where there is a match but the data/fields have
changed. You will UPDATE these records in your existing data table with the
new temp table data for these records (this is where you would need to use
dates if there was the possibility that the mismatched data is actually
older data not yet updated)
You will have 2 queries for each table. If you have lots of tables then you
can write a macro which executes all these queries automatically.
Now delete (or just clear out) your temporary tables ready to import data
from the next external site (again you can use a simple macro to do this).
Post back if you need specific help with any of the above.
Hope this helps
A
