Excel VBA: writing to mysql database
2 answers
You can connect to MySQL using 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 Excel connection using 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
+7
a source to share
Writing to the mysql database is no different from writing to any other database.
You would create an ADODB.Connection object, open it with an appropriate connection string, and use the .Execute (or ADODB.Command) method to execute the sql.
See http://msdn.microsoft.com/en-us/library/ms807027.aspx for details .
You must have mysql access driver (ODBC or OLEDB) installed and link to Microsoft ActiveX Data Objects 2.8 from your vba project.
+2
a source to share