Solved

sync online and offline access database

Posted on 2009-07-01
24
982 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.
0
Comment
Question by:delphibeginner
  • 9
  • 8
  • 6
  • +1
24 Comments
 
LVL 1

Author Comment

by:delphibeginner
ID: 24759169
Small edit: I've created the app with delphi 2009
0
 
LVL 46

Expert Comment

by:tbsgadi
ID: 24761410
Hi delphibeginner,

What is the offline db used for?

Gary
0
 
LVL 1

Author Comment

by:delphibeginner
ID: 24761867
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
 
LVL 46

Accepted Solution

by:
tbsgadi earned 125 total points
ID: 24761876
I would just use the online version & copy it to your PC for offline use.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24762104
"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
 
LVL 1

Author Comment

by:delphibeginner
ID: 24763415
@ 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
 
LVL 46

Expert Comment

by:tbsgadi
ID: 24763484
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
 
LVL 46

Expert Comment

by:tbsgadi
ID: 24763508
Another idea would be to add a mechanism to add offline records to the online database.
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 24769431
will there be multiple users ?
who will win if there are 2 updates on the same record ?
0
 
LVL 1

Author Comment

by:delphibeginner
ID: 24783735
@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
 
LVL 46

Expert Comment

by:tbsgadi
ID: 24783766
In that case all you need to do , is what I said before - copy the online database to your PC. (Not sync)
0
 
LVL 1

Author Comment

by:delphibeginner
ID: 24785027
How can I create a code for that, to copy the online database to the pc.

Thanks
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 46

Expert Comment

by:tbsgadi
ID: 24785116
0
 
LVL 1

Author Comment

by:delphibeginner
ID: 24785282
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
 
LVL 46

Expert Comment

by:tbsgadi
ID: 24785511
You just need a procedure to download the mdb
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 24788717
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
 
LVL 1

Author Comment

by:delphibeginner
ID: 24798239
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
 
LVL 36

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 125 total points
ID: 24809103
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
 
LVL 1

Author Comment

by:delphibeginner
ID: 24809286
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
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 24866830
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
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 24866860
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
 
LVL 1

Author Comment

by:delphibeginner
ID: 24866877
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
 
LVL 46

Expert Comment

by:tbsgadi
ID: 24866885
Glad you got it working!
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 24884955
mysql ?
i thought this was ms access ?
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.

708 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

16 Experts available now in Live!

Get 1:1 Help Now