One of MySQL's strengths is its use of Binary Large Object (BLOB) columns. These columns store unprocessed binary data, typically files, that can be retrieved and manipulated like the other common datatypes. The difficulty comes in accessing the BLOB column in VB. Prior to ADO 2.5, the only way to move data in and out of a MySQL BLOB column using Visual Basic was to use the appendchunk and getchunk methods. With ADO 2.5, the stream object has been added, greatly simplifying the process of working with MySQL BLOBs. In this article, I will focus entirely on using the stream object.
MySQL and BLOBs - Connection Object (Page 3 of 6 )
Now that we have a connection string, we can connect to the database. First I will give a sample of how I connect.
Dim conn As ADODB.Connection Set conn = New ADODB.Connection conn.ConnectionString = GloConnectionString conn.CursorLocation = adUseClient conn.Open
In the first line we create an ADODB connection object in memory and in the second line we instantiate it. The alternative syntax to this is to type:
Dim conn As New ADODB.Connection
While I previously recommended the 'as New' syntax, I have since learned that it slows down your code since it must check if the object is instantiated every time a reference is made to the object.
In the second line we set the Connection String of our Connection object to the global variable we specify in the login prompt. Conversely, you can always place the connection string here. In the third line we set the connection to use client-side cursors. I have found that this setting helps to prevent a lot of problems that can pop up while programming ADO with MySQL (for more details on cursorlocation, cursortype, and locktype, see http://www.vbmysql.com/articles/cursorsandlocks.html.) In the final line we open the connection object, which we will of course have to close when we are finished with conn.close.