[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Linking 2 databases through cloud database

Posted on 2013-02-03
5
Medium Priority
?
217 Views
Last Modified: 2013-02-07
Hi,

My task is to get inventory information from a microsoft access database in one location into a firebird database in another location.
It could be a live link but a daily update would suffice.
I previously was tossing up between a vpn live connection and exporting a txt file, transferring, updating and decided on transferring the txt file (via ftp automation) as the safest way.

Our tech in the other location is now pushing to have a linked table in Access to a cloud sql server, our firebird database would also link to this table.
The idea being that this would minimise corruption and the SQL server database could error check before uploading into firebird.

Does this sound like a good idea or am I better off sticking to  the txt file transfer?

Thanks for any ideas.
0
Comment
Question by:DiscoStubacca
5 Comments
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 1000 total points
ID: 38850339
Sounds like a good idea.
You can link the tables in the SQL Server database via ODBC to your Access database and then use them as any other linked table.

When you have appended your records to those tables, your tech in the other location can link his Firebird and read.
You could also have a logtable where you record the time whenever you have added records. Then he just needs to check this to see if any new records should be present.

/gustav
0
 
LVL 19

Expert Comment

by:NickUpson
ID: 38850361
as already pointed out this has advantages, just watch out for transaction handling with the firebird update. If you get that wrong it could slow down other users of the database
0
 
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 total points
ID: 38850890
Well to offer a differing view, I'd stick with a tried and true FTP transfer.

There are latency issues when using a cloud DB that need to be taken into account when developing (assume your talking Azure).   Plus your now dealing with a third set of tables, another DB that needs administrative work, etc.   To me, it just seems like it is complicating the picture.

 If an FTP server is already available at either site, then it seems like a no brainer to transfer it that way.

 Out of Access, you can to a TransferText command based on a query, so there is almost no development there.

  You need some work on the Firebird side to import and translate, but that would be it.

Jim.
0
 
LVL 1

Author Closing Comment

by:DiscoStubacca
ID: 38865690
I'm definitely leaning towards the ftp transfer so I've got this solution ready as a backup.
Our other tech is very keen on using cloud db so it looks like we are giving that a go first.
Thanks for the advice.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 38867084
You are welcome!

/gustav
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

834 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