Are there any VB6 code out there that show how to access SQLServer and MySQL?
I'm told to use SQL commands rather than SQLDMO so that it'll be generic and
not tied to one database. Does anyone have code for this?
I'm told that using SQLDMO can be slow on certain machines, and so I'm told
to use inline SQL instead, which will allow me to also use MySQL.

for heterogenous access to SQL Server and MySQL you should program to a ODBC
datasource. Try some of these links for ODBC drivers.
http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLD,GGLD:2005-01,GGLD:en&q=odbc+mysql

Then use ADO against the ODBC provider.

Show quoteHide quote

Hi

SQLDMO is only there to manage the DB Server, not intended to be a query
platform.

MDAC (ODBC/OLE DB etc) is the access stack for accessing databases.

From VB 6, you use the built-in ADO functionality to access your DB of
choice SQL Server, Sybase, Oracle, MySQL etc. You naturally require drivers
for non-Microsoft databases.

ADO is documented in VB6's books online. BTW, VB 6 is no longer supported by
MS. VB.NET is.

As to inline vs. access though stored procedures. If you go common lowest
denominator in accessing a DB, you have to put up with not being able to use
a lot of the good functionality that could reduce your development effort,
and possibly a slower application.

The correct design would be to have a data access layer that abstracts the
DB platform from your boniness and UI code. In the data access layer, you
expose methods and functions that are DB independent so your upper layers of
code don't care what DB you are talking to. If you need to support 2 DB
platforms, you need to build 2 DB layers that match each platform.

Have a look at the MS site for patterns and practices for good ideas and
code: http://msdn.microsoft.com/practices/

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: m***@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

Show quoteHide quote

I agree with Mike that the correct design should have a data access
layer, and if you decide  to go for VB.NET then definitly look at the
MS practices link.

MS have made a complete Data Access layer available as part of their
Enterprise Lib that you could develop against with minor setup/tweaking

have a look at

Data Access Block:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/daab.asp

Enterprise Library:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/entlib.asp

convert flat file to native file format for bcp
Disappearing data (happens rarely)
Query Help: Counts & Sums
Order by Clause
Passing back more than 1 output parameter to VBA code