From Windows Access to mySQL

[gbaughma]

OK... I have a web server that is currently backed with Access (ODBC), and I'm considering installing Linux and MySQL on another box.

What am I setting myself up for? Comments?

Right now, the connections are made with a system DSN connection. Some of the queries are in the form of "SELECT * FROM table WHERE...." and then a connect.execute(SQLString).... but some of the connections are ADO

objRS.AddNew
objRS.Fields("field")="whatever"

... and so forth. Still, I make the connection with the DSN, not DSN-less.

So, what kind of can of worms am I opening up?

My "best case" scenario would be to change the DSN entry on my IIS server to say something like source=\\linuxbox\mysql-whatever database....

... after I import all the data from Access to mySQL.

So, if someone's done this before, let me know what I'm looking at, traps, pitfalls, etc. I may be looking at.

Thanks in advance!



Just my 2¢

"In order to start solving a problem, one must first identify its owner." --Me
--Greg

 

[BocaBurger]

If your company owns either ETL or EDI software (examples: IBM DataStage, IBM WebSphere TX), you can connect to both DBs via ODBC and transfer everything you need to MySQL. Set up your Web sites on Linux too and you don't need IIS.



BocaBurger
<===========================||////////////////|0
The pen is mightier than the sword, but the sword hurts more!

 

[gbaughma]

I don't think we're running either ETL or EDI. My web pages consist of hundreds of thousands of lines of code written in ASP... I don't think I want to convert those.

Currently, my web server (IIS) is using an ODBC connection to an access database (well, actually several access databases)... I just want to dedicate a machine that's sitting idle to be a database server. The cost for MS-SQL is prohibitive, so I thought that mysql would be a great solution.

My ASP scripts make DSN connections... so I'm just wondering what kind of can of worms I'm opening by attempting to a) move the database to a different server, and b) having that server running Linux and mysql.

I'm assuming that somewhere I'll need an ODBC driver to install on my IIS box to make the connection?



Just my 2¢

"In order to start solving a problem, one must first identify its owner." --Me
--Greg

 

[Kozusnik]

There is an ODBC driver on mysql.org. The import is actually very simple. Export the access (forgive me, I can't call access a database ) tables to csv files, create your tables in mysql, either use the load function, or get phpMyAdmin loaded on the Linux box. You can do it remotely, but I can almost guarantee you'll migrate the entire site to Linux once you use it (read up on .htaccess files for the phpMyAdmin directory). Apache-ASP should handle the asp scripts just fine. Then you can remove ODBC and use localhost.

Good luck,
Mark

 

[BocaBurger]

Then you can convert the Windows box to Linux, have the DB on it's own server, the Web site on another and be more secure and faster.



BocaBurger
<===========================||////////////////|0
The pen is mightier than the sword, but the sword hurts more!

 

[QatQat]

Apache-ASP supports perl scripting, in a ASP-Like syntax environment. I do not think you can port vbscript ASP pages directly to linux unless you pay for Sun-ASp (formerly chillisoft ASP) and still not all your objects will be supported. Mixed solutions are anyway always possible, you could use your linux server, with mysql database and run a light windoz webserver on VMware or, if you are good with your kernel, try out XEN.


The quickest way to migrate your access DB is to use mysql migration toolkit, available for free on mysql site.

CHeers

QatQat

Life is what happens when you are making other plans.

 

[lgarner]

I believe that you'll be fine. We have productions systems that access both MSSQL and MySQL via ODBC. The only big issue that I can think of is differences in the feature sets of syntax.

 

[QatQat]

The only major difference is that the recordcount method of your Recordset object does not work.

so

dim Counter
Counter = RS.recordcount

response.write(Counter)

will display "-1" instead of the actual number of records.

The ASP syntax is exactly the same; that is the point of creating an ODBC driver, it would not make sense to have an ODBC driver that behaves differently from the ODBC specifications.

QatQat

Life is what happens when you are making other plans.