Improve company productivity with a Business Account.Sign Up

x
?
Solved

Linking 2 databases through cloud database

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

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:Nick Upson
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 59

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 53

Expert Comment

by:Gustav Brock
ID: 38867084
You are welcome!

/gustav
0

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
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…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

608 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