MS Access to MySQL Connection

Question

I am building an application, using MS Access as a front-end of a MySQL DB. The application consists of a lot of Forms all of which will execute many different SQL statements.

I am establishing the connection using:

    Dim oConn As New ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String

Server_Name = "localhost"   
Database_Name = "test"    
User_ID = "root"    
Password = ""    

oConn.Open "DRIVER={MySQL ODBC 5.3 ANSI Driver}" _
          & ";SERVER=" & Server_Name _
          & ";DATABASE=" & Database_Name _
          & ";UID=" & User_ID _
          & ";PWD=" & Password _
          & ";OPTION=16427"

My questions are: Is it better to Open and Close the connection each time i run an SQL Statement, or Open the connection, when the Application runs and close when the application closes?

-If the first way is better, Can I Create a global Function that returns a connection, to be used in the current form and not having to write the same code over and over for each form and/or SQL statement?

-If the second way is better, Can I Declare and Open the Connection globally, so It can be used from any form?

Keep in mind that:

-There are 50+ different forms and sub-forms in the application.

-The application should be able to run on multiple computers at once accessing 1 database.

Answer 1

I had this same question and nobody got around to answering it.

In general, its better to keep the connection open when you're going to use it and close it when you're done, but not before then. Which is fine if you use it on a per-form basis but if it gets shared it gets a little more complicated.

What I did initially was open the connection for each form, subforms grabbed the connection from their parent, and the form closes the connection when it gets closed.

The issue, if multiple forms use the same connection, is that if you close that connection, other forms using it will have errors and fail. So, if you want to share the connection between forms you can, but just make sure that it never closes unless the file is being closed. I am currently using this method, since I have a base 'menu' form that can't be closed without closing the file, I close the connection onClose for that form.

Another thing to keep in mind, is that the computer could be randomly disconnected from the server, so any code that requires the connection should have a quick test to re-open the connection if it got closed by accident somehow.

EDIT: In it's own module.

Public DB_CONNECTION As ADODB.Connection

Function openConnect(ByRef myconn As ADODB.Connection) As Integer
Static retries As Integer
Dim server As String
server = "localhost"

On Error GoTo connectError
myconn.ConnectionTimeout = 10
myconn.Open "DRIVER={MySQL ODBC 5.1 Driver};SERVER=" & server & "DATABASE=data;USER=" & getSQLuser & ";PASSWORD=password;Option=3"
openConnect = 1
retries = 0
Exit Function
connectError:
'retry several times on failure
Dim errADO As ADODB.Error
For Each errADO In myconn.Errors
  Debug.Print vbTab & "Error Number: " & errADO.Number
  Debug.Print vbTab & "Error Description: " & errADO.Description
  Debug.Print vbTab & "Jet Error Number: " & errADO.SQLState
  Debug.Print vbTab & "Native Error Number: " & errADO.NativeError
  Debug.Print vbTab & "Source: " & errADO.Source
  Debug.Print vbTab & "Help Context: " & errADO.HelpContext
  Debug.Print vbTab & "Help File: " & errADO.HelpFile
  If errADO.Number = -2147467259 Then
        If retries < 3 Then
            If MsgBox("Connection Error, Try to reconnect or close any connection-enabled forms,check your internet connection and try again.", vbCritical + vbRetryCancel, "Connection Error") = vbRetry Then
                retries = retries + 1

                Call openConnect(myconn)
                Exit Function
            End If
        Else
            MsgBox "Connection error. Retried 3 times, check your internet connection and/or contact your system administrator.", vbCritical + vbOKOnly, "Critical Connection Error"
            retries = 0
            Exit Function
        End If
  End If
Next
Select Case err
Case 0
Case Else
    MsgBox "Error Code " & err & ", " & Error(err), vbCritical, "Error #" & err
End Select
    openConnect = -1
End Function

Function closeConnect()
If Not (DB_CONNECTION Is Nothing) Then
    If DB_CONNECTION.State = adStateOpen Then
        DB_CONNECTION.Close
    End If
End If
End Function

Function tryConnect()
Dim err
If DB_CONNECTION Is Nothing Then
    Set DB_CONNECTION = New ADODB.Connection
    Call openConnect(DB_CONNECTION)
Else
    If Not (DB_CONNECTION.State = adStateOpen) Then
        Call openConnect(DB_CONNECTION)
    End If
End If
End Function

In my case, I never call openConnect directly, but always call tryConnect onOpen of any forms that use the DB, or before calls that might happen after some time (for example, the save button). If it's already open, no harm done, but if it's not it prevents an error.

closeConnect I call OnError and OnClose of the menu form.