How to connect VB to MySQL

[Arvin]

Hello,

I have a date range form that has Start Date, End Date and Preview Report command button. In the OnClick Event Prod I have
Dim strDateField As String in my code that should connect to a MySQL Db, to a table that stores the UPDATED Date. The problem is I can't seem to get the form to work ....

here is my code

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler      'Remove the single quote from start of this line once you have it working.
    'Purpose:       Filter a report to a date range.
    'Note:          Filter uses "less than the next day" in case the field has a time component.
    Dim strReport As String
    [COLOR=red][B]Dim strDateField As String[/B][/COLOR]
    Dim strWhere As String
    Dim lngView As Long
    Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Do NOT change it to match your local settings.
 
    'DO set the values in the next 3 lines.
    strReport = "QryDetailsVariable_Crosstab1"      'Put your report name in these quotes.
    [B][COLOR=red]strDateField = "[UPDATED]"[/COLOR][/B] 'Put your field name in the square brackets in these quotes.
    lngView = acViewPreview     'Use acViewNormal to print instead of preview.
 
    'Build the filter string.
    If IsDate(Me.txtStartDate) Then
        strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
    End If
    If IsDate(Me.txtEndDate) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
    End If
 
    'Close the report if already open: otherwise it won't filter properly.
    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If
 
    'Open the report.
    Debug.Print strWhere        'Remove the single quote from the start of this line for debugging purposes.
    DoCmd.OpenReport strReport, lngView, , strWhere
Exit_Handler:
    Exit Sub
Err_Handler:
    If Err.Number <> 2501 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
    End If
    Resume Exit_Handler
End Sub

Any help is appreciated

Thank you in advance

 

[boblarson]

Is this a linked table that you've linked from MySQL into Access? If not, there is no connection. I don't see any other means of connecting to it.

 

[Arvin]

Is this a linked table that you've linked from MySQL into Access? If not, there is no connection. I don't see any other means of connecting to it.

This form is linked to a report that is based on a query that this done on a My SQL Db Tbl that stores the "Updated" date

Is that what you mean? .... Sorry I kinda new to this VB and MySQL issue

 

[Arvin]

By the I keep getting error

Error 3070: The Microsoft Access database engine does not recognize '[UPDATE]' as a vaild field name or expression.

 

[boblarson]

Make sure you spelled it right. In the Code you have it as UPDATED and in the error you show [UPDATE] (which by the way is a RESERVED word and so you shouldn't use that as an object name).

 

[Arvin]

Oops! ....sorry, thats a typo on my part ...it does read [UPDATED]

sorry for the confusion .....

So....I select my dates ..... and then click the preview button and then I get the error

Error 3070: The Microsoft Access database engine does not recognize '[UPDATED]' as a vaild field name or expression.

Just wondering ..... since '[UPDATED]' is in a MySql table called "tickets" should the following line read as.

strDateField = "[tickets]![UPDATED]"

instead of

strDateField = "[UPDATED]"

I would still have to declare it, no? ...but if the Access DB is already linked to the MySQL Db via OBDC then I'm thinking I shouldn't haven't have to declare anything .....

Man ...I'm really confused

 

[Banana]

What is the form's or report's recordsource? If it doesn't include the UPDATED in the query, then Access wouldn't know where it goes to.

 

[Arvin]

The form itself is unbound, the report that it displays is based on a crosstab query that does list UPDATED

Thank you in advance

 

[RuralGuy]

It looks like we have another thread with the same topic and OP.