excel vba: writing to mysql database

Question

i would like to write a macro that will write to a mysql database. can someone please get me started on this?

Answer 1

Writing to a mysql database is no different to writing to any other database.

You'd create an ADODB.Connection object, .Open it with an appropriate connection string and use the .Execute method (or ADODB.Command) to execute sql.

See http://msdn.microsoft.com/en-us/library/ms807027.aspx for more information.

You'd have to a have a mysql access driver installed (ODBC or OLEDB) and reference the Microsoft ActiveX Data Objects 2.8 from your vba project.

Answer 2

You can connect to MySQL with a connection string and ADO:

''http://support.microsoft.com/kb/246335
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

strCon = "Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=MyDB;" _
& "User=root;Password=pw;Option=3;"

cn.Open strCon

You can also use DSN with a connection to Excel using the Jet driver:

Dim cn As ADODB.Connection

''Not the best way to get the name, just convenient for notes
strFile = Workbooks(1).FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")

''For this to work, you must create a DSN and use the name in place of 
''DSNName
strSQL = "INSERT INTO [ODBC;DSN=DSNName;].NameOfMySQLTable " _
& "Select AnyField As NameOfMySQLField FROM [Sheet1$];"

cn.Execute strSQL