Migrating BackEnd to MySQL (1 Viewer)

[Treason]

I fear I have outgrown MSAccess as a backend... I have 70+ users now and things are running pretty darn slow. I have so much more data now and so many more tables etc..

My main reason for the move is speed. From what I understand, If I query 5 records from a table across the network. Access transfers the entire table to the local machine, then the local machine drops what it doesn't need. Which is really insane when I think about it. So I could go to MS SQL Server or to MySQL. SQL Server is probaly smarter but money is a big issue, so I will probaly go the free route and choose MySQL. From what I understand, Access can link to a MySQL backend table using ODBC. I want to keep all my forms and VBA, so my users don't really notice the switch.

So here are my questions...

Does MySQL handle MS functions, like Left(), InStr() etc? I have the bad habits of commonly using these function in my queries.

How will MySQL handle opening DAO recordsets through code? Will that work the same?

What other issues might I encounter? And is it really worth it?!?

My DB speed stinks right now... I just want to query my information faster...

 

[WayneRyan]

Treason,

SQL Server is definitely a good move. Especially with that many users.

As for speed. The SQL Server database engine is incredibly fast. It's
amazing to see the speed with which it can process data. It can definitely
kick JET's butt in that respect.

BUT, if your front-end bases its forms on tables, or queries that encompass
entire tables, you won't see the benefits. If performance is an issue, then
you'll have to use combos or listboxes to streamline the amount of data that
will be sent over the net. That issue won't change regardless of the DB engine.

MySQL can handle functions in queries. I get confused with all of the different
tool sets. For example, the capability exists, but retrieving pieces of strings
is "Mid" with JET, but "SubString" with T-SQL. Any functions that you have
previously declared in Public Modules will have to be migrated to the server.

You can still use DAO, but I think it's worth the modifications to go to
ADO.

The other issues:

Enterprise Manager - Excellent control panel for the maintenance of many
databases. Central point for maintain users/backups/relationships/activity.
Great tool!

Data bound triggers - No more relying on only your forms to control what
happens when data changes. This will give you reliable audit trails, etc.

Definitely worth the move.

Wayne