Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Access backend merging

Posted on 2014-10-24
20
190 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 25

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 25

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 25

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 25

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
 
LVL 25

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 25

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 25

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 25

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 25

Author Closing Comment

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

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

840 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