Need info concerning VB6/MySQL

[RDM2]

It has been decided that I must convert 3 VB6 Projects that use a common SQL Server DB to use a MySQL DB. The DB migration went well and appears to be ready.
PROBLEM: I have not been able to find the info I need to make the code substitutions in the VB6 projects. The code that has worked well is:

Public oMSDEServer As SQLDMO.SQLServer

Set oMSDEServer = CreateObject("SQLDMO.SQLServer")

Providers used in the connection strings are "Provider=SQLOLEDB.1" for the ADODCs on the Forms, and "Provider=MSDataShape;Data Provider=SQLOLEDB.1" for the DE.

If someone can share or point me to the right info on how to substitute I sure would appreciate it. I.m facing some major, major rewrite if this does not work.

TIA

 

[gmmastros]

SQLDMO is built specifically to connect to a SQL Server database. As far as I know, you cannot use it for ANY other type of database.

I'm curious though.... Why the decision to switch away from SQL server?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RDM2]

I will show a little ignorance here and ask: Are the ADODCs and the DE dependent upon SQLDMO? If not I would really like to find a way to make them work.

As for the DB switch, a biggie in the decision is that MySQL is free and this system is in direct support of Schools.Have you ever heard of a school that had enough money? At least a public school.

We were waiting for the performance numbers to get better, reliability to be confirmed, and to start hearing some good things concerning Views in MySQL.

Given all that, it appears that it may be time to change. If we hit major road blosk(s), it wouldn' be terribly difficult to return yo SQL Server.

Thanks

Ralph

 

[gmmastros]

Have you ever heard of a school that had enough money? At least a public school.

Yes. In fact my business depends on it.

SQL Server is not free. This is true. But, you could use MSDE2000, SQL Express 2005, or SQL Express 2008. They are free. Furthermore, public schools qualify for reduced pricing on Microsoft software.

I currently have 200 customers, all of them public school districts. Most of them are using the free version of SQL Server (MSDE or SQL Express). Some school districts already own SQL Professional (the non-free version). In those cases, they simply attach my database to their existing instance of SQL Server.

Never once has anyone ever complained about our choice of database. I once had an IT director ask whether our software used MySQL. I said no, but the free version of SQL Server from Microsoft will be mroe than adequate for their purposes.

For an average customer, the database is approximately 500 megs. This fits well within the limits of the free version of SQL Server (msde has a 2 gigabyte limit and SQL Express has a 4 gigabyte limit).

As far as I know, ADODC and DE are NOT dependent on SQL DMO. You'll need to use a different connection string in order to use MySQL. Connection string information can be found at

http://connectionstrings.com

Personally, I don't use ADODC or DE, so I could be wrong about this. I see no reason why you wouldn't be able to get this to work with MySQL.

If we hit major road blosk(s), it wouldn' be terribly difficult to return yo SQL Server.

I wouldn't count on this if I were you. The syntax for SQL Server and MySQL is different enough that you may have a rough time maintaining both.

I hope this helps.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JoeAtWork]

ADO was designed to be "Database agnostic", as long as the database vendor has created an OLE DB provider (or even an ODBC driver), then theoretically you should be able to simply change the connection string and your existing code should work.