Solved

Access backend merging

Posted on 2014-10-24
20
178 Views
Last Modified: 2014-11-29
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.
0
Comment
Question by:-MAS
  • 9
  • 6
  • 3
  • +1
20 Comments
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 40403552
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
 
LVL 24

Author Comment

by:-MAS
ID: 40403654
@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
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 167 total points
ID: 40403826
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
 
LVL 24

Author Comment

by:-MAS
ID: 40404221
ok I agree.
But how to copy ?
what to copy?

May be copy can be done using copy command.
What to copy?
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 40404469
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
 
LVL 24

Author Comment

by:-MAS
ID: 40404477
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
 
LVL 5

Expert Comment

by:ReneD100
ID: 40405304
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
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 40405645
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
 
LVL 24

Author Comment

by:-MAS
ID: 40405725
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
 
LVL 5

Assisted Solution

by:ReneD100
ReneD100 earned 333 total points
ID: 40405786
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 24

Author Comment

by:-MAS
ID: 40405796
-->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
 
LVL 5

Assisted Solution

by:ReneD100
ReneD100 earned 333 total points
ID: 40405842
- 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
 
LVL 24

Author Comment

by:-MAS
ID: 40405877
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
 
LVL 5

Expert Comment

by:ReneD100
ID: 40405887
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
 
LVL 24

Author Comment

by:-MAS
ID: 40405904
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
 
LVL 5

Expert Comment

by:ReneD100
ID: 40405913
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
 
LVL 84
ID: 40406077
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
 
LVL 24

Accepted Solution

by:
-MAS earned 0 total points
ID: 40406085
@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
 
LVL 5

Expert Comment

by:ReneD100
ID: 40406099
Sounds like you're getting closer. Good luck with the next step!
0
 
LVL 24

Author Closing Comment

by:-MAS
ID: 40471446
I managed by export and copy then import
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Viewers will learn how the fundamental information of how to create a table.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now