Avatar of Peter Fors
Peter ForsFlag for Sweden asked on

From Access to Excel having Error -2147217900 (Invalid SQL statement...

I have code for Office Automation. Data in Access turns into a chart in Excel. The code works normally as expected. But now I receive an error:


Error -2147217900 (Invalid SQL statement; expected DELETE, INSERT, PROCEDURE, SELECT or UPDATE, line 230

The code (part of it) is like this:

          Dim conQuery As String
110       conQuery = "[ChartDataOGAEpH]"
            ' Create recordset.           ‘Debug.Print conQuery 220       Set rst = New ADODB.Recordset 230       rst.Open _             Source:=conQuery, _             ActiveConnection:=CurrentProject.Connection

Open in new window

Error when the SQL is:

SELECT Format([MätDatum],"Short Date") AS Registered, [Jäsprotokoll rader].OG, [Jäsprotokoll rader].SG AS AE, [Jäsprotokoll rader].pH

FROM Jäsprotokoll INNER JOIN [Jäsprotokoll rader] ON Jäsprotokoll.JäsprotokollID = [Jäsprotokoll rader].JäsprotokollID

WHERE (((Format([MätDatum],"Short Date")) Is Not Null) AND (([Jäsprotokoll rader].SG) Is Not Null) AND ((Jäsprotokoll.JäsprotokollID)=[Forms]![Jäsprotokoll historiskt]![JäsprotokollID]))

ORDER BY Format([MätDatum],"Short Date");

Although the output of the query is:

ChartDataOGAEpH

Registered

OG

AE

pH

2021-09-07

12,9

12,9

5,2

2021-09-08

12,9

9,5

4,6

2021-09-09

12,9

6

4,35

2021-09-10

12,9

3

4,3

2021-09-13

12,9

2,5

4,4

2021-09-14

12,9

2.5

4,41

2021-09-15

12,9

2,5

4,48


What am I doing wrong?

VBA* excel graphMicrosoft Access

Avatar of undefined
Last Comment
Peter Fors

8/22/2022 - Mon
John Tsioumpris

Have you checked the query via SSMS ?
If the connection is directly to SQL then you have check the commands ...e.g SQL doesn't have Format
ASKER
Peter Fors

Hi John,
Thanks for helping.
It is all Access, the underlying tables as well as the query.
But I removed the Format.
Still the same Error.

I then tried to change the query and random picked a table as source (conquery = "MyPickedTable")
Now the code works as expected.

I can workaround the issue via a Query - > Table.
But why is there an error at all?
John Tsioumpris

So no SQL server ?
What about DAO recordset?
Dim rst as DAO.Recordset
Dim strSQL as String
strSQL = ' Your sql
set rst = CurrentDb.OpenRecordset(strSQL)
Debug.print rst.Recordcount  

Open in new window


Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
Peter Fors

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question