SQL code won't work in Excel VBA

Question

I am using Excel 2010 VBA and querying 3 tables from a PGSQL database

The following SQL code works perfect in MS ACCESS:

SELECT user.surname,user.forename,recall.recalldate,telephone.number
FROM (user INNER JOIN recall ON user.entity_id = recall.master_id) 
LEFT JOIN Telephone ON recall.master_id=Telephone.master_id

but doesn't seem to transfer to EXCEL VBA:

Dim RECALL As String
RECALL = "SELECT user.surname,user.forename,recall.recalldate,telephone.number " _
        & "FROM (user INNER JOIN recall ON user.entity_id = recall.master_id) " _
        & "LEFT JOIN Telephone ON recall.master_id=Telephone.master_id " _

Set rs = conn.Execute(RECALL)
With ActiveSheet.QueryTables.Add(Connection:=rs, Destination:=Range("A1"))
  .Refresh
End With

I get a runtime error: ...expected table source after FROM, got(

The user table links to the recall table using user.entity_id = recall.master_id and shows all recall records for users. Then I need the telephone numbers for the matched users. Not all will have a tel number, but I need all recalls regardless of whether they have a tel number.

What is wrong with the SQL code in VBA?

You are comparing two different SQL dialects: MS Access ACE/Jet SQL and PostgreSQL. One difference is use of parentheses in joins. If PGSQL tables are linked in Access, you will run the ACE/Jet dialect. If using pass-through queries like in Excel, you use the direct connected PGSQL dialect. Had you run a pass-thru query in Access you would receive same error. Also user is a reserved word in both dialects. Either escape or rename. — Parfait
– Parfait, Commented Mar 18, 2016 at 13:23

ScaisEdge · Accepted Answer · 2016-03-15 20:00:18Z

1

Seems you have a () after form try without

 SELECT user.surname,user.forename,recall.recalldate,telephone.number
 FROM  user INNER JOIN recall ON user.entity_id = recall.master_id
 LEFT JOIN Telephone ON recall.master_id=Telephone.master_id

answered Mar 15, 2016 at 20:00

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

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

3 Comments

BradleyS Over a year ago

This now shows runtime error: Preprocessor;relation "Telephone" does not exist; SQLSTATE 42P01;SOURSE_FILE src\backend\parser\parse_relations.c; COURSE_line 984; SOURCE_FUNCTION parserOpenTable

ScaisEdge Over a year ago

Check for Telephone or telephone you have a lower case in select and upper case in JOIN .. otherwise you don't have a table with this name ..

BradleyS Over a year ago

Yep that's it. Thank you

ASH · Accepted Answer · 2016-03-25 21:07:28Z

0

Yeah, it's probably different SQL for different databases. Typically, the SQL is very similar, but there are always slight differences between different databases.

Take a look at this little utility in the link below when you have time. That is very useful for converting SQL to VBA. Give it a try, and see how you get along.

http://allenbrowne.com/ser-71.html

answered Mar 25, 2016 at 21:07

ASH

20.5k28 gold badges117 silver badges247 bronze badges

Collectives™ on Stack Overflow

SQL code won't work in Excel VBA

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related