Solved

Updating database over internet.

Posted on 2013-01-22
7
605 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
[X]
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
7 Comments
 
LVL 25

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

738 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