Linking 2 databases through cloud database

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.
LVL 1
DiscoStubaccaAsked:
Who is Participating?
 
Gustav BrockCIOCommented:
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
 
Nick UpsonPrincipal Operations EngineerCommented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
 
DiscoStubaccaAuthor Commented:
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
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
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.

All Courses

From novice to tech pro — start learning today.