Share & synchronise mdb without server?

[snowsride]

My daughter is setting up a small business with a friend - they will work from different locations but will need to share a database which I am going to create. They only have laptops so I need a method to synchronise any changes to the tables. So far I've considered setting up a daily routine using an export table option and then having each of them email the files to the other which hopefully can then somehow be synchronised with the current mdb tables.

I have no experience with data access pages but could I make use of them for my purposes? From initial investigations it looks as if they can't be used to add records they can just be used to edit or delete records?

This is clearly not going to be a very elegant method of working since it will depend on both users remembering to run the routine at the end of each working day but the volume of business is likely to be low so investing in a server cannot be justified financially.

Grateful for any ideas.

 

[Snak]

You can do this, but you will need a web host with MySQL database facility. Go to mysql.com and download the ODBC driver for Access. This will allow you to put your data tables online, and both your daugfhter and her partner can connect directly. No synchronisation necessary.

MySQL, the ODBC driver and the latest Jet engine for your version of Access are all free and referenced at mysql.com.

 

[maxmangion]

Moreover, doing it this way you should give more importance to security since your data is going to be online, especially if you are going to store sensitive data.

 

[shadow9449]

Snak:

Would you elaborate on how this is accomplished?

If I've created an Access database, and I have the drivers allowing my Access front end to link to MySQL tables, now what?

Do I somehow convert the Access tables to MySQL?

Thanks

SHADOW

 

[Snak]

Hi Shadow

Create your table(s) in Access and then export...(File > Export .....Scroll down the 'Files of Type...' combo to ODBC databases, then choose [or set up] a DSN for the MySQL tables).

I usually do it the other way round - create the tables in MySQL and then link to them (File > Get External Data > Link Tables.... Scroll down the 'Files of Type...' combo to ODBC databases, then choose [or set up] a DSN for the MySQL tables).

You can then use Access forms, queries macros etc directly on the tables.

 

[shadow9449]

Snak:

Create your table(s) in Access and then export...(File > Export .....Scroll down the 'Files of Type...' combo to ODBC databases, then choose [or set up] a DSN for the MySQL tables).

It's that easy (once the MySQL driver has been installed) to use a hosted Access application?

I guess every time you work on it, you have to redo the process, but once you have an application up and running, that's a much better way to have people at numerous sites tap into an .mdb over a WAN.

Thanks for the tip off.

SHADOW

 

[Snak]

Never a problem

Glad I could help.

 

[shadow9449]

Here's something to read on the topic. I hope it's helpful:

http://dev.mysql.com/tech-resources/articles/migrating-from-microsoft.html

SHADOW

 

[Snak]

Excellent article.

Under most circumstances, using MySQL with Access via ODBC, and for relatively simple applications, some of the esoterics of field type differences do not matter. The obvious exception to this is a date/time field. The cute thing is that MySQL stores dates as text: YYYY-MM-DD HH:SS:hh (where hh is hundredths of a second).

When reading such a field directly (in an Access Query for example), the use of the Cdate() function as in Cdate([MySQLDateField]) renders any recognisable date type (so it works with '4th May 2003' or '15/01/1956' as well) into a 'proper' date that Access can work with.

 

[shadow9449]

Snak:

I'm still having trouble exporting my Access tables as MySQL. I installed the MyODBC driver for Windows, but when I try to set up a DNS, I get errors. This might sound like a stupid question, but do I need some sort of MySQL server running on my computer in order to make a "local" DNS?

I know that with MSDE, you need to have the server actively running (that little icon in the system tray) in order to use it, so I'm wondering if I require that as well for MySQL?

SHADOW

 

[Snak]

Yes. If you want to use your own machine to host the MySQL tables, you need to install MySQL locally.

When you set up a DSN, if you've installed the MySQL ODBC code, there will be an option in the list of drivers (including MySQL v 3.51) in the Create New Data Source option (either from within Access or from Control Panel -> Administrative Tools -> ODBCD data Sources (XP, 2000) or Control Panel -> ODBC data Sources (W9x).

The ODBC connection does not care where the data actually are - all it needs is the info to get at them (server, username, password, database name). With a local installation of course, the Server is 'localhost'.

 

[shadow9449]

Yes. If you want to use your own machine to host the MySQL tables, you need to install MySQL locally.

Ok, that's why it bombed when I tried it.

I think what I'll do is have my buddy, the webhost, set up a database with some tables for me, and then connect to them using the MyODBC from Access and see if I can write queries, reports, forms, etc against those tables and see what the performance is like. If that succeeds, maybe I'll set up my own MySQL server locally, and backend existing Access databases onto it for development, and then have him host them so my clients can link offices at different locations.

Thanks again.

SHADOW

 

[Snak]

You're welcome.

A good plan. Good luck.