Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Linking 2 databases through cloud database

Posted on 2013-02-03
5
Medium Priority
?
216 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
[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
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In this article, we’ll look at how to deploy ProxySQL.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

610 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