Solved

Updating database over internet.

Posted on 2013-01-22
7
590 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
Flexible connectivity for any environment

The KE6900 series can extend and deploy computers with high definition displays across multiple stations in a variety of applications that suit any environment. Expand computer use to stations across multiple rooms with dynamic access.

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

When it comes to security, there are always trade-offs between security and convenience/ease of administration. This article examines some of the main pros and cons of using key authentication vs password authentication for hosting an SFTP server.
In this article, I am going to show you how to simulate a multi-site Lab environment on a single Hyper-V host. I use this method successfully in my own lab to simulate three fully routed global AD Sites on a Windows 10 Hyper-V host.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

830 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