question

Walker huang avatar image
0 Likes"
Walker huang asked Walker huang commented

SQL utilization for connection to Database


It already succeed to connect to Database(Microsoft Access).but as the below picture showing, it can not read data from database if using HAVING clause of SQL. On the contrary, it would make it if not using HAVING clause. how to address this problem? thank!

BTW, encounter the same issues if using ORDER BY clause.




image20220706085421.png


FlexSim 22.1.2
sqldatabase
· 2
5 |100000

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Jason Lightfoot avatar image Jason Lightfoot ♦ commented ·
Does the same SQL statement work in an Access Query?
0 Likes 0 ·
Walker huang avatar image Walker huang Jason Lightfoot ♦ commented ·

Hi, so far not validate it in Access Query

0 Likes 0 ·

1 Answer

·
Jordan Johnson avatar image
1 Like"
Jordan Johnson answered Walker huang commented

I think this is something specific to this combination of database file and query. I did an internet search for "odbc error number 07002" and found many different applications with the same issue. In each case, the resolution was to correct something about the query.

So, here are some things I would try:

  • Double check spelling/capitalization of all column/table names
  • Wrap all identifiers (column names, table names, aliases) in square brackets
  • Make sure you only use aliases, if you have declared them. For example, I'd try changing your GROUP BY statement to GROUP BY Item
  • Try the query in Access, to see if it is valid there, as @Jason Lightfoot suggests.

You could also try using a nested query instead of a HAVING clause, if ODBC supports nested queries:

SELECT Item, Max_SPQ FROM (
  SELECT [FLEX PART NO] AS Item, MAX([SPQ]) AS Max_SPQ FROM SPQ
  GROUP BY Item
) WHERE Max_SPQ > 1
· 1
5 |100000

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Walker huang avatar image Walker huang commented ·

thank you very much Jordan. your answer in great, but need to do a little modification as the below picture, then it will work. 1657155443061.png

0 Likes 0 ·
1657155443061.png (21.7 KiB)

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.