I have developed a db and deployed it to 8 different sites which will be entering data into 12 different tables....I want to consolidate that data once a month. I am looking for reccomendations for "offloading" each sites data from all the tables and then "uploading to one "central" computer....

KISS please...

I am unskilled at code....and only slightly more advanced at access in general...

Thanks

Teddy

Welcome to UA!

Unforntunaely this is NOT a KISS task.

Someway you will need to get the data from the remote sites to the "central;" computer. The you will have to import all the data into the central database.

I would hoped to have determined before you send out your app, how you will do this and get it tested.

Will you be ssedning the whiole database, or exporting out just the changes from the remote lcoations?

After they send the data, will they be able to edit it? (is not, you will have to lock the records) If yes, then you will have to send the changes.

When importing, does the data have a feild to flag which remote databse the data is coming from?

How to determine if dupliclate records have been sent?


I have done a solution just like this, the app will use the internet to FTP the new data to an FTP server. The server looks for update files and automativcally imporets the data.


It can be done, but takes a lot of planning.

Hope this helps you get started.

Thanks for the questions..

In the old days of flat files it was easy....just export 1 file as an XL sheetand then import it as such....now i have lots of tables...not so easy or just very tedious
.
Have not sent it out yet...

DB is Split FE/BE

Remote sites will need to send data that is new or edited on a monthly basis....

There is actually two ways to determine which site it is from....There is a clientid field that contains 7 digits the first three digits id's the site

4112004002 indicates 411=springfield, 2004=intake year, 002=second client.

....there is also a site field.

tblClient tblSite
*clientID
SiteID----------------------------------->*SiteID
SiteName

Concerning duplicate records....I assume that if the client id is key with no duplicates it won't import....

Doesn't answer the question if an old record is edited that new info needs to overwrite the old info...

This obviously begs the question....how do I centralize the BE...Remote sites do have High Speed Internet access...either DSL/Cable...Is there someway to put the FE on their computer and the BE in one place....and link the tables....

Ive tried it on a VPN connection....pretty slow....

Got a feeling that if you have to ask the question...you cant afford the answer????

Next steps...???

Teddy

Ti handle the edited records, you will have to decide if you want to merge changes or overwrite the records from the remote location..

I add a field to each master record for Batch ID in the remote data. I create a batch table that hold when the bacth was sent and then select records to add to the bacth. Then when they click the "create batch" button, I export all the records for the selected for the batch into an "export" MDB that is then sent in to the "central" office.

This can be done mostly with append queries. (Delete queies to empty the export MDB).

I have now started to use Microsoft Terminal Server. The cost to develop the app vs. the cost of terminal server, termianl server was a lot cheaper (both in developement and maintenance)

The will require a terminal server in the "central" office that has internet access. Then on the dektops, run Remote Destop Client. It is already in XP and avaialble from win 98 se and laterand it is free from Microsoft.

You do not have to install anything else on the remote destop. Everything runs on the server. The remote office "remote" intol the server to do all the work. It works like remote control softeare, but it is a "session" on the server. The only thing sent between the server and the remote computer is screen shots and keboard data. It runs very fast even with dial-up internet access.

OK...
Again appreciate your continued help here...apologize if my ignorance gets in the way...but you appear very patient...

It sounds like Terminal Server is a great answer....I am a youth worker who stumbled across Access a few years ago as a way to automate recording services provided to clients....I attended classes at a local college...also attend a local users group on a monthly basis....

We do have an "IT" guy in central office, with no access experieince at all....

I searched for info on Terminal server and from a financial POV it looks reasonable enough....for what it promisies...Is there a way to try it out...so that I can impress the boss before purchase....

From a networking stand point this is what we have....Central office with a server...FE/BE sit on the server and the computers in Central Office have access to it...via a shortcut to the FE...it works fine...

1 remote office 100 miles away has access to the Central office server...I assume they call this a WAN? they are mapped to the drive that has the db on it....when they click on the DB icon the db loads slowly....when they click on the button to load the main screen...that takes forever to load I mean forever....once loaded records can be added and edited fairly quickly....close that form to do something else and then reload the main form....takes forever again....I have tried to put the FE on the remote desktop and link the tables to the be in central office...not much better....

the other seven offices are not "linked" to the central office in anyway....but do have high speed internet access...

I have also tried our VPN...I would rather watch the grass grow and paint dry than wait for the db to load using that...

I assume that if Terminal server is the answer that would remove the need to do all this uploading and downloading of records at remote locations...

Again your help and attention to this is very much appreciated....I have emailed our IT guy for his knowledge and reccomendation concerning Terminal server....Anxious to hear what he says...

Teddy

I assume that if Terminal server is the answer that would remove the need to do all this uploading and downloading of records at remote locations...

That would be true. Also is you nmake any changes to the app, you only have top load it on the server. No need to send updates to the remote offices.


I have one site where all thee run Termainl server and all the workstations on the local network use the Remote Destop cvlient to access the server and run their apps. THe workstations have Win98 SE and are just pentium I and II class with 6 GIG or smaller hard drives. They connect to the local terminal server and run Office 2003 apps! The can use almost any PC as a work station as long as it has 64 meg RAM and WIndows 98 SE.

When you mapp a drive over a VPN anbd the use linked tables, The JET engines in local and must "pull" all the data in a table down to the remote machine to the process and select all the records that are needed. That is why it is so slow.


What OS are you using for the machine that has the backend data MDB?

I have set up a terminal services server....

I have logged on and I essentially like what I see. certianly cmpared to recoding in some "foriegn language"

Seveal questions will arise from my boss tho...

?Only 2 clients can log in at atime??? Is this a software constraint or licensing????

Any good resources to read about for security issues....and their resolution...

It appears that the connections are somewhat fragile....After 15 minutes of non use it seems to break connection...

I am gonna poke around looking for amswers to above but I appreciate your input...I seem to be on to an acceptable solutuoin.

Teddy

I also understand that access is limited to 5 concuurent users...

Accurate?????

I think I might be able to live with that at the present time tuff down the road....

Tedy

Consolidate multiple copies of a database.

Set up a folder structure. 1 folder for each "site" and 1 folder for the consolidated db.

Have each site zip their data files and e-mail them to you. Put an unzipped copy of each site's data into their folder.

Create your consolidated database with a blank copy of your backend tables.
In this db create append queries that link to the tables in each site folder (8X12 queries).
Run the queries.

The easiest way to run them all is to put them into a macro.

This is a fairly simple and direct solution to the problem.
It's clunky but it works as long as your records all have a unique identifier.
Take care that your data are secured during transport.