SQL on VBA: Compilation error, a function or variable was expected

Question

I have the code below but it isnt working. It gives me the error: Compilation error, a function or variable was expected.

I guess the error is on the Database.Execute method, but I didnt come with a solution yet.

Sub ChavesMontante()

Dim dbschaves As Database

Dim rschaves As DAO.Recordset

Dim tipodechave As String

Dim SQLCMD As String

Dim chave As String

Set dbschaves = CurrentDb

chave = "BED20777"

SQLCMD = 

"(SELECT us.instalacao, ch.bloco, us.tipounidade " & _
"FROM (SELECT useccionadora, bloco " & _
"FROM sgdprd.redeprimaria rp " & _
"WHERE rp.useccionadora IS NOT NULL " & _
"CONNECT BY rp.nox = PRIOR rp.fontex AND rp.noy = PRIOR rp.fontey " & _
"START WITH rp.useccionadora = '" & chave & "' ) ch " & _
"INNER JOIN sgdprd.useccionadora us ON ch.useccionadora= us.instalacao) "

Debug.Print SQLCMD

Set rschaves = dbschaves.Execute(SQLCMD)


End Sub

There is no object in Excel's VBA model named as `Database`. — cyboashu, Sep 01 '17 at 22:16
Did you try `SQLCMD = "(SELECT us.instalacao, ch.bloco, us.tipounidade " & _` ...? — yacc, Sep 02 '17 at 03:38
Are your tables in a Oracle backend? Then you could run a Pass-Through query. Access SQL doesn't support `CONNECT BY` or `START WITH`. — Andre, Sep 02 '17 at 07:01
Review https://stackoverflow.com/questions/31827817/sql-server-equivalent-of-oracle-connect-by-prior-and-order-siblings-by — June7, Sep 03 '17 at 02:26
Yes, they are in a Oracle backend. What do you mean with Pass-Through? I'm not familiar with the concept. — Caio Gomes, Sep 03 '17 at 13:30
https://support.office.com/en-us/article/Process-SQL-on-a-database-server-by-using-a-pass-through-query-B775AC23-8A6B-49B2-82E2-6DAC62532A42 -- The query is then executed on the Oracle Server, so it must use 100% Oracle SQL syntax. — Andre, Sep 03 '17 at 16:20
@Andre Thank you so much for the help. I'm able to do this hierarchical structure from oracle on access now using this pass-through method! Thank you so much again. — Caio Gomes, Sep 03 '17 at 18:38
Cool, you're welcome. :) Your original question (compile error) was answered by June7, please [accept](http://stackoverflow.com/help/someone-answers) the answer so the question is shown as resolved. @CaioCésarSilvaGomes — Andre, Sep 04 '17 at 13:53

June7 · Accepted Answer · 2017-09-02T15:37:25.930

0

Execute is used for action SQL statements (UPDATE, DELETE, INSERT), not to set a Recordset object.

Try the following changes:

Dim dbschaves As DAO.Database
...
Set rschaves = dbschaves.OpenRecordset(SQLCMD)

Also, remove the outer parens enclosing the entire SQL statement. I have never seen CONNECT BY PRIOR START WITH. Does the query open in Access?

edited Sep 02 '17 at 15:37

answered Sep 01 '17 at 23:39

June7

19,874
8
24
34

I made that. Now it gives me the error: Syntax error, missing operator, 'rp.seccionadora IS NOT NULL CONNECT BY rp.nox = prior rp.fontex and rp.noy = prior rp.fontey START. Any guess? – Caio Gomes Sep 02 '17 at 00:01
It is not possible to do hierarchical structure like CONNECT BY ... PRIOR on access, thats the reason I'm using VBA. – Caio Gomes Sep 02 '17 at 00:09
You're funny... Even if you use VBA the query will still be run through the Access query engine. @CaioCésarSilvaGomes – Andre Sep 02 '17 at 07:03
Thats true, but I'm not able to enter this code straight on access using sql. Although I've seen examples of code using this type os structure on vba. That's the reason I'm trying it. – Caio Gomes Sep 03 '17 at 00:51

SQL on VBA: Compilation error, a function or variable was expected

1 Answers1