Dear experts,
I am currently working on a project that requires the export of data (various tables) from MySQL Database which consists of Binary, Decimal and Hex in a single table.
The code which i use is a great source and works well until when the columns hold Binary data. The error shown is Error 400, which stops the data extraction process.
I suspect the issue lies with the Range function with Offset.
--------------------------------------------------------------------------------------------------
For H = 0 To Horizon
Range("A8").Offset(0, H).Value = rs.Fields(H).Name 'Move cell from Left to Right
For V = 0 To Verti
Range("A8").Offset(V + 1, H).Value = myArray(H, V) 'Move cell from Up to Down
Next
--------------------------------------------------------------------------------------------------
The pictures for the error 400 and Binary format in MySQL Database table can be seen from the following hyperlink.
Error400.jpg
VBA_Error400.jpg
MySQL_DataBase_Table_with_BinaryFormat_from IMAGE_Column.jpg
MySQL_DataBase_Table_with_BinaryFormat_from IMAGE_Column.jpg
Can you please advice how i can move on?
Thank you very much.
=======================================================================================
Sub ExtractAgentFlowsFromMySQL()
Dim Password As String
Dim SQLStr As String
Dim Server_Name As String
Dim User_ID As String
Dim Database_Name As String
Set rs = CreateObject("ADODB.Recordset")
Range("a8:bb60000").ClearContents
Server_Name = Range("b3").Value ' IP number or servername
Database_Name = Range("b6").Value ' Name of database
User_ID = Range("b4").Value ' id user or username
Password = Range("b5").Value ' Password
Tabellen = Range("e2").Value ' Name of table to write to
SQLStr = "SELECT * FROM " & Tabellen
Set Cn = CreateObject("ADODB.Connection")
Cn.Open "Driver={MySQL ODBC 5.1 Driver};Server=" & Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"
rs.Open SQLStr, Cn, adOpenStatic
Dim myArray() ' Declare array
myArray = rs.GetRows() ' Put into myArray
Horizon = UBound(myArray, 1) ' Returns the highest available subscript for the indicated dimension of an array.
Verti = UBound(myArray, 2) ' Returns the second available subscript for the indicated dimension of an array.
For H = 0 To Horizon
Range("A8").Offset(0, H).Value = rs.Fields(H).Name 'Move cell from Left to Right
For V = 0 To Verti
Range("A8").Offset(V + 1, H).Value = myArray(H, V) 'Move cell from Up to Down
Next
Next
rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing
End Sub
=======================================================================================
Bookmarks