Solved

Updating database over internet.

Posted on 2013-01-22
7
611 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 85

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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 85
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

696 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