Access backend merging

I have multiple access files.
Each access files is in each branch.
I want to merge the data on a daily basis to open the details of all branches from main branch. I want to merge over internet.
Anyone can give an idea how to do?
Ifyou want I will change the bank end to sql if that works.
LVL 30
MAS (MVE)EE Solution GuideAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

COACHMAN99Commented:
as long as the tables don't have clashing keys and indices (that all records are uniquely identifiable when merged) you can create a master BE, link all the other BEs, and run a big macro that runs multiple append queries. If any of the tables are using autonum primary keys then the situation gets a lot more complex.
If your 'internet' connection facilitates mapped drives (VPN etc) then its doable. Else a scheduled task on each remote machine can send a copy of the BE to the central machine for local processing.
0
MAS (MVE)EE Solution GuideAuthor Commented:
@COACHMAN99
Many thanks for your reply
How to send?  can you send how to send to the main branch?
Is it an export and import or something else? Plwease explain.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I would strongly suggest you NOT do this over the internet. Instead, grab a copy of those files and move them to your local machine, and do the merge there. Internet connections are fickle and prone to drop/reconnect, and Access doesn't like that.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

MAS (MVE)EE Solution GuideAuthor Commented:
ok I agree.
But how to copy ?
what to copy?

May be copy can be done using copy command.
What to copy?
0
COACHMAN99Commented:
The easy solution is for a local user to compact and repair,  encrypt (7-zip)  and email the file daily.
else automate using scheduled tasks on source machines (servers). You may need to acquire APIs for the 7-zip.
Is it possible your expertise in this area is less than required for this complex job?
If you don't know the files that need to be copied then maybe you need to engage a consultant?
Then there is still the bigger issue of unique keys, autonums etc in the source data.
The level of sensitivity of data is also a concern when emailing.
0
MAS (MVE)EE Solution GuideAuthor Commented:
I am already doing manual export and import without PK conflict. bcz PK will be prefixed with branchcode.
This is first time I am trying to automate.

Sensitivity is not a problem. Let hackers take the entire database they will not get single penny bcz payments sent direct to bank with a phone call and SMS from the money exchange.

BTW this is a cash collection details for few  orphanages.
They want to track how many cash is pending each month and for each branch orphanage
0
ReneD100Commented:
Since I do not get the impression there is no need for real time data I'd advise you look into a VPN connection from each branch to the 'main' office. A lot of router support his, or otherwise you could even use a PPTP connection built into Windows to a VPN router in the main office.
Once VPN is established you can use robocopy, copy, xcopy, FTP or whatever method you like to transfer the back-end.
0
COACHMAN99Commented:
If you already have the manual 'transport' section in place, all you need is a big macro (comprising a bunch of append queries) to merge all data from linked tables, after you have received all the current databases.
0
MAS (MVE)EE Solution GuideAuthor Commented:
Is it possible to export required tables copy to the main by VPN or by some other way and import to the database in the main office ?

I all these should be autoamted. i.e Export, copy and import.
Example export as excel or any supported file format.

Is it a recommended way?
0
ReneD100Commented:
Does the data in the sub offices get deleted? Or does the data remain there as well? In that case you wouldn't have to import/export data, but simply copy the complete back-end to the main location. In the main location you simply link to the tables in each separate file. If the file gets updated, so do the linked tables. Of course this also depends on the quantity, since the transported files will get larger over time. Access files can well be zipped though.
0
MAS (MVE)EE Solution GuideAuthor Commented:
-->Does the data in the sub offices get deleted?
No.
--> In the main location you simply link to the tables in each separate file
I am not clear on this
0
ReneD100Commented:
- You transfer the various back-ends to - let's assume - various folders via robocopy, ftp, etc.
- In the consolidation database you link to the databases/tables in the various folders
- You can now run reports, etc over the data from all the tables
- Next day the various back-ends get replaced and reporting will be processed over the new data. No need to replace the linked tables
0
MAS (MVE)EE Solution GuideAuthor Commented:
Is it possible to export required tables copy to the main by VPN or by some other way and import to the database in the main office ?
0
ReneD100Commented:
Yes, but I would either transfer the branch back-end first to the main location and THEN run the import, or export the 'new' data to a temp db, transfer that file and then import the data.
Technically you could run the import/export query directly over the internet/vpn, but as Scott McDaniel said, that is not advisable. One error in the transfer and the Access file will get corrupted.
I have a client where they had 30 people using a local front-end to a shared back-end and they had constant file corruptions (user PC crashed, not turned off correctly, etc). In the end we installed a Terminal Server there so the Access file was on a 'local' resource for all the users.
0
MAS (MVE)EE Solution GuideAuthor Commented:
is it ok if I export a FTP mapped driver and map that FTP in main office and import to the main office file
0
ReneD100Commented:
Then you are still importing over the net, with the risk of corruption. Map the FTP, transfer the complete file, then do the import. If that is too much data, export it locally in the branch to a 3rd database file, transfer that file and import the data.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can use simple batch files to make copies at the local level:

@EchoOff
xcopy Source, Destination

You could set this to run at specified times via the Windows Scheduler.

Once there, the only way to further automate this would be to either (a) use a VPN and map the remote drives to your machine or (b) use FTP. Both of these have been suggested by other experts.

VPN to copy the file over from a remote location would be okay. In most cases, a filecopy of a not-in-use file is fairly safe.

FTP can be accomplished fairly easily with VBA, if you'd prefer. Basically you'd have to setup an FTP server on your network, then add a routine to the remote machines to invoke the FTP transfer. There are multiple ways of doing this - for example, a batch file that calls a "script" - and many of those can be automated.

But you must first determine the methods you'll use to do these, so we can provide more insight.
0
MAS (MVE)EE Solution GuideAuthor Commented:
@ReneD100
No I will copy to local drive. if it is successful then go to next step of importing.

@Scott McDaniel (Microsoft Access MVP - EE MVE )
It just come to my mind about copying from FTP server to local drive and then import to access main table
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ReneD100Commented:
Sounds like you're getting closer. Good luck with the next step!
0
MAS (MVE)EE Solution GuideAuthor Commented:
I managed by export and copy then import
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.