ADO + MySQL + Ms Access 2007. Unbound Form filters

[Serj1980]

Hi all!

I am on this forum the first time. I beg your pardon for my accent. I'm from Russia)
There is a small procedure that I use as a connecting ms Access and MySQL server:

.....
Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strSQL As String
strSQL = "table1"

con.CursorLocation = adUseClient
con.Open "Provider=MSDASQL;" & _
"Driver={MySQL ODBC 5.1 Driver};" & _
"Server=my server;" & _
"Port=3306;" & _
"Database=my base;" & _
"User=login;" & _
"Password=my password;" & _
"Option=16426 "

rst.Open strSQL, con, adOpenDynamic, adLockOptimistic
Set Me.Recordset = rst

rst.Close
con.Close
Set rst = Nothing
Set con = Nothing
......

This procedure transfers the data in the unbound form of ms Access.
Everything works fine, but I can not use standard filters ms access 2007. I mean the filters are listed below:


I understand that this is a problem of this method. Is it possible to make the filter work with unbound forms?

 

[Banana]

Hi -

No, that is a limitation of the method of binding the ADO recordset to a form. There are also few other limitations. Here's a partial list.

If you want to keep using ADO recordset, you have to manually re-implement the filters in code and use Filter method/Sort.

I also want to point out that you may be better off using linked tables. Note that you are still using ODBC in ADO, when linked tables access ODBC directly so that means less layer of technology. Using linked tables will allow you to use Access' native filtering.

Also, note that adOpenDynamic is not what you will get in back (the provider does not support this but ADO will silently change the value to closest equivalent, likely adOpenKeyset).

I hope that helps.