sync online and offline access database

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.
LVL 1
delphibeginnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

delphibeginnerAuthor Commented:
Small edit: I've created the app with delphi 2009
0
tbsgadiCommented:
Hi delphibeginner,

What is the offline db used for?

Gary
0
delphibeginnerAuthor 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.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

tbsgadiCommented:
I would just use the online version & copy it to your PC for offline use.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
"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
0
delphibeginnerAuthor 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.
0
tbsgadiCommented:
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.
0
tbsgadiCommented:
Another idea would be to add a mechanism to add offline records to the online database.
0
Geert GOracle dbaCommented:
will there be multiple users ?
who will win if there are 2 updates on the same record ?
0
delphibeginnerAuthor 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.
0
tbsgadiCommented:
In that case all you need to do , is what I said before - copy the online database to your PC. (Not sync)
0
delphibeginnerAuthor Commented:
How can I create a code for that, to copy the online database to the pc.

Thanks
0
tbsgadiCommented:
0
delphibeginnerAuthor 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?
0
tbsgadiCommented:
You just need a procedure to download the mdb
0
Geert GOracle dbaCommented:
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.
0
delphibeginnerAuthor 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?
0
Geert GOracle dbaCommented:
you will need to be a lot more specific about what "online" means

1: a database on a server in your network you have full access to
    (probably with vpn from outside the company, like from home)
2: a mysql database on a server with a hosting company
    (you have access to it via phpmyadmin and a webpage)
    this also means you have no direct access to the database via tcp/ip/odbc
0
delphibeginnerAuthor 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?
0
Geert GOracle dbaCommented:
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
0
Geert GOracle dbaCommented:
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
0
delphibeginnerAuthor 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.
0
tbsgadiCommented:
Glad you got it working!
0
Geert GOracle dbaCommented:
mysql ?
i thought this was ms access ?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.