Solved

Linking 2 databases through cloud database

Posted on 2013-02-03
5
208 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 49

Accepted Solution

by:
Gustav Brock earned 250 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 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 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 49

Expert Comment

by:Gustav Brock
ID: 38867084
You are welcome!

/gustav
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now