We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

sync online and offline access database

Medium Priority
1,538 Views
Last Modified: 2013-11-28
I've created an app which works great, it uses a access database (created in access 2007 but saved as 2003).
I want to have a central online database with which the app can auto or manually sync the offine (local) database with.

Because in the online database, songs will be added and the app needs to check if the offline database is up to date with the online database.

I have no Idea how to do this, I know relication with access is possible, but I'm not sure if it does want I need from it.

Please some help.
Comment
Watch Question

Author

Commented:
Small edit: I've created the app with delphi 2009

Commented:
Hi delphibeginner,

What is the offline db used for?

Gary

Author

Commented:
At first I want to make a online db only for the app, but then I thought, that when if there is no internet connection then the app wont work, so thats why an offline db aswell.
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
"Replication" is now a Deprecated technology in MS Access.
You can use it in Access 2007 *only* if your database stays in the 2003 format.

So you may want to look for a different approach, as tbsgadi has suggested.

JeffCoachman

Author

Commented:
@ tbsgadi

Thanks for the advice, that is an idea, but different people use the app, so it would be a great idea to be able to update, any idea how to do that.

Copy the online database and use it offline would be an idea but only if I used the app for myself, but like I said its used by more people, so I dont think that would be an option.

Commented:
As Jeff says Access Replication is on it's way out...I wouldn't go there.
If you really need  replication I would look at SQL Server, although I think it would be overkill.

Commented:
Another idea would be to add a mechanism to add offline records to the online database.
Geert GOracle dba
CERTIFIED EXPERT
Top Expert 2009

Commented:
will there be multiple users ?
who will win if there are 2 updates on the same record ?

Author

Commented:
@tbsgadi: The idea is to add (through the app) records to the online database (they shouldnt exist in the offline database already).

@Geert Gruwez: Only 1 person at a time should be able to add a record to the online database. If a record is already there it shouldnt be added to the database.

After added to the online database, multiple users should be able to update their local database through the app.

Commented:
In that case all you need to do , is what I said before - copy the online database to your PC. (Not sync)

Author

Commented:
How can I create a code for that, to copy the online database to the pc.

Thanks

Author

Commented:
Thanks, I just had a look at it, but I saw that it was a code for vb, I'm using delphi 2009, is it much of a differents with the code?

Commented:
You just need a procedure to download the mdb
Geert GOracle dba
CERTIFIED EXPERT
Top Expert 2009

Commented:
the only good solution to your problem: a online solution !
a database server
- for n-tier
  a application server with connection to the database server
- for single-tier
user in network (with vpn or whatever) and connection to the database server
a exe running local with connection to that database or app server

or a db driven website

any other solution will fail.

Why ?
You have no way of telling the user his record couldn't get posted online.
Let's say someone posts a record before he goes on holiday.
After 3 weeks he returns to work and wants to synchronize and the app tells him the record couldn't get posted.
What happens then ? Reenter the record ? Do you remember what you typed 3 weeks ago ?
And you will have 20 users ? = 21 databases to keep in sync, offline.
Impossible !

You need an online solution.

Author

Commented:
Thanks I will try to look for a procedure to copy and replace the database.

Also I want to connect to a database (access) online to added records to it, but I cant seem to connect it.
I want to use and url in the source of adoconnection, but doesnt work.
I've looked on google for a solution but havent found one yet.

Any suggestions?
Geert GOracle dba
CERTIFIED EXPERT
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
What I meant by online is:

A ms access database in a map of my webpage, say: www.domain.nl/data/database.mdb

I want to connect to that database but as I read on google this will probably difficult.

And I'm not quit sure how to use mysql or sql (as database online) for the connect instead of ms access online.

Hope there is a solution for that?
Geert GOracle dba
CERTIFIED EXPERT
Top Expert 2009

Commented:
there is some levels of "online"

the link you provided is available via a static webpage and a http link (hypertext transfer protocol)
the actual file can be downloaded via ftp (file transfer protocol) (or http too)

what you need is a webserver which can open the access database using a cgi-dll
if you want to this with delphi then you need to look into
DataSnap, WebSnap, IntraWeb, etc.

Basically this is a dll on the server which is invoked by the webserver receiving a http request
this dll can then read in the msaccess database
firstly you'll need to verify which dll's you can upload to the server
if the domain does not allow it, then you'll have to look at what they provide

for the domain you provided you'll need at least "hosting online"
this provides "cgi" and "PHP" scripts
for mysql db, you'll need "hosting business"
http://www.vip.nl/webhosting.php?cat=business
Geert GOracle dba
CERTIFIED EXPERT
Top Expert 2009

Commented:
if you want to learn how to set up all this at home,
you'll need to look at a AMP solution: (Apache Web Server, My SQL and PHP)

a very good site for more info: http://delphi.about.com/od/intraweb/IntraWeb_RAD_Web_Delphi_development.htm

Author

Commented:
I've solved it with a mysql backup and import it in sql. Because I couldnt connect to mysql directly. This was the way to do it.
Thanks for you replies.

Commented:
Glad you got it working!
Geert GOracle dba
CERTIFIED EXPERT
Top Expert 2009

Commented:
mysql ?
i thought this was ms access ?
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.