Compile Error: Expected function or variable in VBA for access

Question

I know near nothing about VBA, but I'm trying to modify an application to connect to a MySQL Database.

The following code produces a Compile error at rstProjets.Open and I can't seem to find why.

Public mysqlConn As ADODB.Connection

Private Sub cmdUpdate_Click()
Dim rstProjets As ADODB.Recordset
ConnectMySQL
Set rstProjets = rstProjets.Open("SELECT * FROM subventions LIMIT 5", mysqlConn)
With rstProjets
    If Not .EOF And Not .BOF Then
        .MoveFirst
        Do While Not .EOF
        MsgBox "Subventions:" & rstProjets![pin], , "Subvention ajoutée"
        .MoveNext
        Loop
    Else
        MsgBox "Aucune données à mettre à jour !", , "LVMB"
    End If 
    .Close
End With
mysqlConn.Close
End Sub

Private Sub ConnectMySQL()
Set mysqlConn = New ADODB.Connection
mysqlConn.Open "DRIVER={MySQL ODBC 5.3 Unicode Driver};" & _
    "SERVER=127.0.0.1;" & _
    "DATABASE=database;" & _
    "USER=root;" & _
    "PASSWORD=;" & _
    "Option=0"
End Sub

It would be useful to know where and what compile error :) — Matteo NNZ
– Matteo NNZ, Commented Mar 2, 2015 at 18:08
When I click on a button, it triggers this sub and all I get is this message: Compile Error: Expected function or variable — nebulousGirl
– nebulousGirl, Commented Mar 2, 2015 at 18:10
Run Debug->Compile from the VB Editor's main menu. When that triggers a compile error, it will highlight something in the code. What is highlighted when you get that compile error? — HansUp
– HansUp, Commented Mar 2, 2015 at 18:15
A compile error should be highlighting the line on which the compiler fails the compilation. Could you please tell us which line is it? (Most probably, it's ConnectMySQL (third line of code), which is a macro coming from somewhere else that you didn't include into your project). — Matteo NNZ
– Matteo NNZ, Commented Mar 2, 2015 at 18:20
The very first line is highlighted: Private Sub cmdUpdate_Click(). ConnectMySQL is properly included. If I remove the code inside the sub and replace it with a MsgBox, then there is no compile error. — nebulousGirl
– nebulousGirl, Commented Mar 2, 2015 at 18:22

HansUp · Accepted Answer · 2015-03-02 18:31:05Z

1

Set your rstProjets object variable to a New ADODB.Recordset, and then call its .Open method.

Dim rstProjets As ADODB.Recordset
ConnectMySQL
Set rstProjets = New ADODB.Recordset
rstProjets.Open "SELECT * FROM subventions LIMIT 5", mysqlConn

answered Mar 2, 2015 at 18:31

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Compile Error: Expected function or variable in VBA for access

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related