[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 634
  • Last Modified:

Updating database over internet.

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
DiscoStubacca
Asked:
DiscoStubacca
1 Solution
 
chaauCommented:
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
 
NickUpsonCommented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
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.

 
Luke ChungPresidentCommented:
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
 
DiscoStubaccaAuthor Commented:
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
 
DiscoStubaccaAuthor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now