Solved

Updating database over internet.

Posted on 2013-01-22
7
563 Views
Last Modified: 2013-01-29
Hi all,

I have an Access database at one location and a Firebird database at another location.
I need to somehow get the Inventory data from the Access database into the Firebird database on a regular basis.
I used to have the Access database just email sales people the inventory every day but now they want to see the firebird inventory and access inventory all together in the firebird database.

From what I can gather I could keep a live connection with VPN and odbc connections but this would be too slow (offices are 1500 kms apart with only medium internet speeds).
I've been told that they wouldn't mind if it just updated overnight.
So it wouldn't be live data but close enough.
I'm thinking that, at night, I could export the info from Access to a delimited txt file send it to the server with firebird on it then import it into firebird.
This all has to be an automated process.

Am I thinking about this the right way or are there better ways (without making a completely new system)?
0
Comment
Question by:DiscoStubacca
7 Comments
 
LVL 24

Expert Comment

by:chaau
ID: 38808146
How about you keep the live connection to your Access database via VPN, but this connection will be used for overnight data transfers. This way you will save yourself from hassle of using a middle layer (an export file). The overnight transfers could be over a slow connection and can be written in a stored procedure or a script

EDIT: You can also do the other way around. You can create a linked table in MS ACCESS that links to your firebird database and update it from that end. See which one is easier to do
0
 
LVL 19

Expert Comment

by:NickUpson
ID: 38809266
I'd go for either link the firebird to access and update as required, which has the advantage of being real-time, or export from access into a file, convert the file into a series of sel statements, copy to the same machine as the firebird database and load it using isql.
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 38809688
In general you should avoid working with Access databases over a WAN. They are far too prone to corruption in that situation.

You could _perhaps_ link the Firebird database, but again the viability of that would depend on the WAN speeds and such.

If you need to do this daily, I think you'd be better off doing the "import csv file" into the Firebird db.  This is the most reliable way to manage this.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 10

Expert Comment

by:Luke Chung
ID: 38809828
Why not use your VPN and have a computer on the source side is run via Remote Access?  

That'll provide the same performance as a local machine when it comes to data retrieval. It would use the same back-end database (assuming your database is split). The data will be live so you don't need to do any importing or syncing. Reports are printed to the user's local machine.

We've done this kind of hosting for clients to great success. Depending on the application, with one machine and multiple VMs, it's pretty easy to support 5 or more simultaneous users.
0
 
LVL 1

Author Comment

by:DiscoStubacca
ID: 38816141
I don't think I trust the (not great) internet connection with Access enough to rely on a live odbc connection even on VPN.
The problem with the remote access computer is that it sounds like the remote inventory would be viewed through a separate process whereas they would like it all to be listed together within their inventory.
There are about 30+ users of the current firebird backed app which they would like the other inventory put in.
I think I'll end up going for exporting Access data to a csv or txt, transferring file to firebird server and importing it into firebird.
I'm thinking either using VPN and task scheduler to transfer file or automated FTP.
Would there be any advantage with one or the other process or is there a better way?

Thanks,
0
 
LVL 1

Author Comment

by:DiscoStubacca
ID: 38816174
Out of curiosity, I'm looking to, in the future, convert the Access backend to SQL Server.

If it was a case of linking SQL Server to Firebird via odbc connection over the VPN would this be a more viable option?
Would the data transfer would be less prone to corruption in the case of bad connection or connection interruptions?

Thanks
0
 
LVL 84
ID: 38818689
Linked connections are always less desirable than a true client-server type application, where you pull only the data needed from the  database, and write back only when needed (i.e. using a fully unbound application).

With that said, you would probably have better performance and less corruption using SQL Server (or some other server-type database) over a VPN than you would with Access (which is a file-server type database).
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Running Access application from Task Scheduler 6 37
VBA code won't run Delete Query 5 26
Access coding 2 15
error 3022 in access vba 3 18
ADCs have gained traction within the last decade, largely due to increased demand for legacy load balancing appliances to handle more advanced application delivery requirements and improve application performance.
For many of us, the  holiday season kindles the natural urge to give back to our friends, family members and communities. While it's easy for friends to notice the impact of such deeds, understanding the contributions of businesses and enterprises i…
After creating this article (http://www.experts-exchange.com/articles/23699/Setup-Mikrotik-routers-with-OSPF.html), I decided to make a video (no audio) to show you how to configure the routers and run some trace routes and pings between the 7 sites…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

863 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

23 Experts available now in Live!

Get 1:1 Help Now