MySQL VBA Connection String with "NO_DATE_OVERFLOW"

Question

So I have this issue with MySQL linked tables in MS Access: When I manually set up an ODBC Unicode connection with "Disable Date Overflow error" I'm able to delete records in the MySQL linked table using the MS Access front end. MySQL ODBC Settings However, when I connect using the below connection string in VBA the tables are connected but when I enter a date in the date field or try to delete a record I get a Date Overflow error

''-------------------------------------
strConn = "Driver={" & MysqlDriver & "}" & _
           ";server=" & ServerName & _
           ";Database=" & DatabaseName & _
           ";Trusted_Connection=Yes" & _
           ";NO_DATE_OVERFLOW=1" & _  'This is the part that I think it doesn't work in the code
           ";charset=utf8" & _
           ";user=" & ConnUserName & _
           ";password=" & ConnPassword

Delete_ALL_TABLES   '' Delete All the old tables before the import
Conn.Open strConn
Debug.Print Conn.ConnectionString
Set TablesSchema = Conn.OpenSchema(adSchemaTables)
Debug.Print TablesSchema.RecordCount
Do While Not TablesSchema.EOF
 R = R + 1
 def_str = TablesSchema("TABLE_NAME")
 Debug.Print R, def_str
 Set tdfLink = db.CreateTableDef(def_str)
 tdfLink.SourceTableName = def_str
 tdfLink.Connect = "ODBC;" & strConn
 db.TableDefs.Append tdfLink
 DoEvents
 TablesSchema.MoveNext
Loop
''-------------------------------------

MS Access Date Overflow Error

I'm using MySQL ODBC Unicode 8.0 driver and tested the connection with MS Access 32-bit and 64-bit versions.

it only works with the ODBC I manually create with "Disable Date Overflow error" checked.

Would you please help me fix the issue with the connection string?

Bouns help if I can save the password within the connection string so I don't have to import the tables when the MS Access Front end is closed and reopened.

Thank you in Advance :)

Answer 1

Let's start with the bonus. MS Access has an undocumented connection pool. If you create a connection, and later you try to access the same server & database using the same odbc driver main version, then it will reuse that connection, even if you are trying to use different credentials.
Side effect: if you create a DAO connection to the server and database where you have linked tables, they will work without prompting for user and password.

Also, from a security point of view, linked tables can be imported from an accde file into an editable accdb, so it is better to save the tables without user & password, and prompt the user for credentials once at startup. A less secure variation is when you hide the connection string in a VBA function, and you distribute an accde file. So an attacker has to disassemble the accde file to get the password. It can be done, but it stops most non-professionals.

So my proposal is to create a startup function, wich tries to create connections using all the odbc drivers on a list until it finds one, and than relinks tables & passtrough queries only if the driver has been changed.

I'm using this to relink tables: (works with NO_DATE_OVERFLOW)

Private Sub dsnless_table2(Name As String, strconn As String, Optional srcname As String = "", Optional myflags As Long = 0)
'
On Error GoTo myerr
ERT = "dsnless_table2"
ert2 = ert2 & " ->" & ERT
ert3 = ""

   Dim tdef As New TableDef, sourcename As String, mysource As String, db As DAO.Database, eflag As Boolean, oldcon As String
   Set db = CurrentDb
   
eflag = False
On Error Resume Next
   If IsObject(db.TableDefs(Name)) = True Then eflag = True
   If err.Number <> 0 Then eflag = False
err.Clear
On Error GoTo myerr

If eflag = True Then
   sourcename = db.TableDefs(Name).SourceTableName
   oldcon = db.TableDefs(Name).Connect
   
   If compdiff(oldcon, strconn) = False And compdiff(sourcename, srcname) = False Then GoTo skip ' .Connect, .Name and .SourcetableName is equal to new value..
   ' else: recreate table; .Connect is write protected(?) after tabledefs.append
   DoCmd.DeleteObject acTable, Name
   db.TableDefs.Refresh
Else
   sourcename = ""
End If
      
   If Len(srcname) = 0 Then
      If Len(sourcename) > 0 Then
         mysource = sourcename
      Else
         mysource = Name
      End If
   Else
      mysource = srcname
   End If
   
   Set tdef = db.CreateTableDef(Name, 0, mysource, strconn)  
   db.TableDefs.Append tdef  

' db.TableDefs.Append stores actual connection string instead of the specified one. 
   If tdef.Connect <> strconn Then
      tdef.Connect = strconn
      tdef.RefreshLink
   End If
   
skip:
On Error Resume Next
db.TableDefs(Trim(Name)).Properties("Attributes").Value = myflags ' dbHiddenObject ' 536870913  ' dbsystemobject = -2147483646
err.Clear
On Error GoTo myerr
   
   'db.TableDefs.Refresh
   
Exit Sub
'Resume Next
myerr:
    Call show_ert(err.Number, erl)
   
End Sub




Public Function compdiff(inA As Variant, inB As Variant) As Boolean
' returns true if values doesn't match, Including Null <>2 etc.. ' Null,Null --> False.  Null, 1 --> True. 1,1 --> False. A,B --> True.
'input: anything if "=" has a meaning for that datatype.
On Error GoTo myerr

Dim OneNullflag As Boolean, Compflag As Boolean
OneNullflag = IsNull(inA) Xor IsNull(inB) 'csak az egyik Null?
Compflag = Not (IsNull(inA) Or IsNull(inB)) 'egyik sem Null?

If OneNullflag = True Then
   compdiff = True ' Null <> 2
Else
   If Compflag = True Then
      ' No Nulls
      If inA = inB Then
         compdiff = False ' 1=1 ; (1<>1) = False
      Else
         compdiff = True ' 1<>2
      End If
   Else
      compdiff = False ' Null = Null
   End If
End If

Exit Function
myerr:
   ERT = "compdiff()"
   ert2 = ert2 & " ->" & ERT
   ert3 = ""
   Call show_ert(err.Number, erl)
End Function  

And this is the function to create the connection:

Private Function dsntest(strCnn1 As Variant) As Boolean
' used by cnn_startup()
On Error GoTo myerr

   dsntest = False
   If IsNull(strCnn1) Then Exit Function
   If Len(CStr(strCnn1)) < 2 Then Exit Function

Dim dbs As DAO.Database, qdf As DAO.QueryDef, rn As Long

   Set dbs = CurrentDb()
   Set qdf = dbs.CreateQueryDef("")
   
   qdf.ReturnsRecords = False
   qdf.Connect = strCnn1
   'Any VALID SQL statement that runs on server will work below.
   qdf.SQL = "Select 0;" '"SET @icon = 1;" ' doesn't run on Oracle server --> sqlstr parameter
   
   On Error Resume Next
     qdf.Execute
     rn = err.Number
   On Error GoTo myerr
   
   If rn = 0 Then dsntest = True

cleanup:
   Set qdf = Nothing
   Set dbs = Nothing
   
   Exit Function
   
myerr:
   dsntest = False
   ERT = "dsntest"
   ert2 = ert2 & " ->" & ERT
   ert3 = ""
   Call show_ert(err.Number, erl)
   GoTo cleanup
End Function

We basically create a passtrough query wich opens the connection, than we throw it away, so there will not be any saved queries with the password.

Bonus info: I recommend to use odbc driver version 8.0.26. 8.0.29 might work with NO_DATE_OVERFLOW=1. See this for further details: https://bugs.mysql.com/bug.php?id=107235. 0.27 and 0.28 have other Access related bugs.

Bonus info 2: MS Access VBA has a half way documented erl object. I have a line numbering function that I run during the release process. If an error occurs on a numbered VBA line, it will be stored in the erl until you leave the sub/function or you clear the error. Show_ert() sends some telemetry data including erl to the server, so I can monitor client side errors.