MS Access migration horrors

crashsystems · 01-29-2007, 03:27 PM

I am the admin for a local office network running a samba server on Debian Linux for about 30 clients. On the server I have a contact database with 36000+ entries, and a form-based gui coded in vbscript. Due to the plethora of bugs I'm having with Access, I want to switch to mysql with a web-based front end. Does anyone know an easy way to migrate my data to mysql. Also, if possible, I would like to avoid hand-coding the web-based cgi front end, but I need it to be close enough to the original interface so as to not completely confuse the office staff. Does anyone have any recommendations?

pljvaldez · 01-29-2007, 03:41 PM

I'm not even good enough to be called a novice w/ Access, but here's a howto someone here at LQ wrote earlier this year:
http://www.linuxquestions.org/linux/..._Data_to_Linux

Here's another link from google:
http://www.oracle.com/technology/pub...ne_access.html

As for the form, I don't have any idea...

farslayer · 01-29-2007, 03:42 PM

Can't you simply export the tables from access to csv or similar format then import that data into mysql >

alternately you could consult the Microsoft to MYSQL Migration guide on MySQLs' web site.

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

Code:

Migration Tools

There are a wide variety of tools available to help you migrate a SQL Server or Access database 
to MySQL. We'll look at several different tools so you can choose the one that best suits your 
needs. The tools we will look at will include the following:

    * MSSQL2MYSQL
    * Microsoft DTS
    * SQLyog
    * Access Export
    * Text Import/Export

SQLYog and the Microsoft DTS wizard offer graphical interfaces that can be used with both MSSQL 
and Microsoft Access to import tables into MySQL. MSSQL2MYSQL is a script by Michael Kofler that 
can convert not only the table structure and data, but converts the index information as well. 
If you use Microsoft Access you may not have access to the above tools, but you can use the 
data export features of Access.

http://www.kitebird.com/articles/access-migrate.html

crashsystems · 01-29-2007, 04:21 PM

Thanks for the links. I think for now I will migrate the data to mysql, and create links in the Access database to the data on the server, that way I can continue to use the interface the users are familiar with. Once my plans for switching our office completely to Linux come closer to happening, I'll custom-build the web based front-end.