Building SQL statement in VBA. Getting 3131 Runtime Error in FROM clause :@

[russellhq]

I'm trying to make an SQL statement on the fly using form variables to
use in a query for a report. I've copied the SQL statement from the
query and assigned it to a string in VBA using what I think is the
correct format but I keep getting the error in the subject. I've gone
through the code many times and can't see the issue.

The only problem I can see is the SQL string is very long. It has to
account for 20 tables and 115 fields.

The code below is what I've used except for the SQL string, as its
quite long.

Private Sub cmdCert_Click()

Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String
Dim rptCert As String


Set db = CurrentDb()
Set qdf = db.QueryDefs("qryTest")


strSQL = "SELECT tblWPS.QualifyingRange, ...... ORDER BY
tblWelderQualification.[Test Number];"

'"WHERE tblWelderQualification.[Test Number]=" & Chr(34) & [Test
Number] & Chr(34) & _

qdf.SQL = strSQL


DoCmd.Close acReport, "rptTest" 'Closes report if allready open

DoCmd.OpenReport "rptTest", acViewPreview 'Opens report


End Sub

 

[Michel Walsh]

It seems you are still within the limits (Access 2003, in help: Access
Specifications) of the tables (20, a max of 32 is allowed):

---------------------------------------------
Attribute Maximum
Number of enforced relationships 32 per table minus the number of
indexes that are on the table for fields or combinations of fields that are
not involved in relationships
Number of tables in a query 32
Number of fields in a recordset 255
Recordset size 1 gigabyte
Sort limit 255 characters in one or more fields
Number of levels of nested queries 50
Number of characters in a cell in the query design grid 1,024
Number of characters for a parameter in a parameter query 255
Number of ANDs in a WHERE or HAVING clause 99
Number of characters in an SQL statement approximately 64,000


------------------------------------------

Since the SQL parser mentions an error in the FROM clause, check if it is
ok, mainly, if there are the proper spaces around the table names and the
other keywords. It may also be a problem of matching parenthesis, or, as it
is sometimes with Jet, with missing ( ) around some 'complex' joins.

 

[russellhq]

I've gone through it but can't see a problem ??

I ran out of line continuers, thats why there's a long line in there.

"FROM (((((((((((((((((tblWelders INNER JOIN (tblWPS INNER JOIN
tblWelderQualification ON " & _
"tblWPS.WPS=tblWelderQualification.[WPS No]) ON tblWelders.[Welder
ID]=tblWelderQualification.[Welder ID]) " & _
"INNER JOIN tblWeldProcess ON tblWPS.[Root Process]=tblWeldProcess.ID)
INNER JOIN tblWeldProcess AS " & _
"tblWeldProcess_1 ON tblWPS.[Fill Process]=tblWeldProcess_1.ID) INNER
JOIN tblWeldProcess AS tblWeldProcess_2 " & _
"ON tblWPS.[Cap Process]=tblWeldProcess_2.ID) INNER JOIN tblWeldType
ON tblWPS.[Weld Prep]=tblWeldType.ID) " & _
"INNER JOIN tblProductType ON tblWPS.[Product Type]=tblProductType.ID)
INNER JOIN tblMaterialGroups ON " & _
"tblWPS.Material=tblMaterialGroups.ID) INNER JOIN tblConsumables ON
tblWPS.RootConsumable=tblConsumables.ID) " & _
"INNER JOIN tblConsumables AS tblConsumables_1 ON tblWPS.[Fill
Consumable]=tblConsumables_1.ID) INNER JOIN " & _
"tblConsumables AS tblConsumables_2 ON tblWPS.[Cap
Consumable]=tblConsumables_2.ID) INNER JOIN tblShieldingGas " & _
"ON tblWPS.[Root Gas]=tblShieldingGas.ID) INNER JOIN tblShieldingGas
AS tblShieldingGas_1" & _
"ON tblWPS.[Fill Gas]=tblShieldingGas_1.ID) INNER JOIN tblShieldingGas
AS tblShieldingGas_2 ON " & _
"tblWPS.[Cap Gas]=tblShieldingGas_2.ID) INNER JOIN tblAuxiliaries ON
tblWPS.Auxiliaries=tblAuxiliaries.ID) " & _
"INNER JOIN tblWeldingPositions ON tblWPS.[Root
Position]=tblWeldingPositions.ID) INNER JOIN tblWeldingPositions " & _
"AS tblWeldingPositions_1 ON tblWPS.[Fill
Position]=tblWeldingPositions_1.ID) INNER JOIN tblWeldingPositions AS
tblWeldingPositions_2 ON tblWPS.[Cap
Position]=tblWeldingPositions_2.ID) INNER JOIN tblWeldDetails ON
tblWPS.WeldDetails=tblWeldDetails.ID ORDER BY tblWelderQualification.
[Test Number] " & _
"ORDER BY tblWelderQualification.[Test Number];"

 

[Guest]

Russell,

What are you actually doing in the VBA code you mentioned? Does the SQL
return the same set of fields from the same set of tables every time (I would
hope so if this is for a report)?

If so, I would define the basic query and save it, then use that as the data
source for your report. Then, in your code, you can define the WHERE clause
and pass that to the report as a parameter using the docmd.openReport method.
This way, the query never changes, only the filter that is passed to the
report.

Dale

 

[Michel Walsh]

can you try:



FROM
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
(
tblWelders INNER JOIN tblWelderQualification ON
tblWelders.[WelderID]=tblWelderQualification.[Welder ID])
INNER JOIN tblWPS ON tblWPS.WPS=tblWelderQualification.[WPS No])
INNER JOIN tblWeldProcess ON tblWPS.[Root Process]=tblWeldProcess.ID)
INNER JOIN tblWeldProcess AS tblWeldProcess_1 ON tblWPS.[Fill
Process]=tblWeldProcess_1.ID)
INNER JOIN tblWeldProcess AS tblWeldProcess_2 ON tblWPS.[Cap
Process]=tblWeldProcess_2.ID)
INNER JOIN tblWeldType ON tblWPS.[Weld Prep]=tblWeldType.ID)
INNER JOIN tblProductType ON tblWPS.[Product Type]=tblProductType.ID)
INNER JOIN tblMaterialGroups ON tblWPS.Material=tblMaterialGroups.ID)
INNER JOIN tblConsumables ON tblWPS.RootConsumable=tblConsumables.ID)
INNER JOIN tblConsumables AS tblConsumables_1 ON
tblWPS.[FillConsumable]=tblConsumables_1.ID)
INNER JOIN tblConsumables AS tblConsumables_2 ON
tblWPS.[CapConsumable]=tblConsumables_2.ID)
INNER JOIN tblShieldingGas ON tblWPS.[Root Gas]=tblShieldingGas.ID)
INNER JOIN tblShieldingGas AS tblShieldingGas_1 ON tblWPS.[Fill
Gas]=tblShieldingGas_1.ID)
INNER JOIN tblShieldingGas AS tblShieldingGas_2 ON tblWPS.[Cap
Gas]=tblShieldingGas_2.ID)
INNER JOIN tblAuxiliaries ON tblWPS.Auxiliaries=tblAuxiliaries.ID)
INNER JOIN tblWeldingPositions ON
tblWPS.[RootPosition]=tblWeldingPositions.ID)
INNER JOIN tblWeldingPositions AS tblWeldingPositions_1 ON
tblWPS.[FillPosition]=tblWeldingPositions_1.ID)
INNER JOIN tblWeldingPositions AS tblWeldingPositions_2 ON
tblWPS.[CapPosition]=tblWeldingPositions_2.ID)
INNER JOIN tblWeldDetails ON tblWPS.WeldDetails=tblWeldDetails.ID

ORDER BY tblWelderQualification.[Test Number]




You can cut and paste in the SQL view of a new query, add something as
SELECT *, and switch in design view. I just change the start of the FROM
clause, to get the uniform pattern:

((( ... ) INNER JOIN ... ON ... ) INNER JOIN ... ON ... ) INNER JOIN ...
ON ...



Hoping it may help,
Vanderghast, Access MVP.

I've gone through it but can't see a problem ??

I ran out of line continuers, thats why there's a long line in there.

"FROM (((((((((((((((((tblWelders INNER JOIN (tblWPS INNER JOIN
tblWelderQualification ON " & _
"tblWPS.WPS=tblWelderQualification.[WPS No]) ON tblWelders.[Welder
ID]=tblWelderQualification.[Welder ID]) " & _
"INNER JOIN tblWeldProcess ON tblWPS.[Root Process]=tblWeldProcess.ID)
INNER JOIN tblWeldProcess AS " & _
"tblWeldProcess_1 ON tblWPS.[Fill Process]=tblWeldProcess_1.ID) INNER
JOIN tblWeldProcess AS tblWeldProcess_2 " & _
"ON tblWPS.[Cap Process]=tblWeldProcess_2.ID) INNER JOIN tblWeldType
ON tblWPS.[Weld Prep]=tblWeldType.ID) " & _
"INNER JOIN tblProductType ON tblWPS.[Product Type]=tblProductType.ID)
INNER JOIN tblMaterialGroups ON " & _
"tblWPS.Material=tblMaterialGroups.ID) INNER JOIN tblConsumables ON
tblWPS.RootConsumable=tblConsumables.ID) " & _
"INNER JOIN tblConsumables AS tblConsumables_1 ON tblWPS.[Fill
Consumable]=tblConsumables_1.ID) INNER JOIN " & _
"tblConsumables AS tblConsumables_2 ON tblWPS.[Cap
Consumable]=tblConsumables_2.ID) INNER JOIN tblShieldingGas " & _
"ON tblWPS.[Root Gas]=tblShieldingGas.ID) INNER JOIN tblShieldingGas
AS tblShieldingGas_1" & _
"ON tblWPS.[Fill Gas]=tblShieldingGas_1.ID) INNER JOIN tblShieldingGas
AS tblShieldingGas_2 ON " & _
"tblWPS.[Cap Gas]=tblShieldingGas_2.ID) INNER JOIN tblAuxiliaries ON
tblWPS.Auxiliaries=tblAuxiliaries.ID) " & _
"INNER JOIN tblWeldingPositions ON tblWPS.[Root
Position]=tblWeldingPositions.ID) INNER JOIN tblWeldingPositions " & _
"AS tblWeldingPositions_1 ON tblWPS.[Fill
Position]=tblWeldingPositions_1.ID) INNER JOIN tblWeldingPositions AS
tblWeldingPositions_2 ON tblWPS.[Cap
Position]=tblWeldingPositions_2.ID) INNER JOIN tblWeldDetails ON
tblWPS.WeldDetails=tblWeldDetails.ID ORDER BY tblWelderQualification.
[Test Number] " & _
"ORDER BY tblWelderQualification.[Test Number];"

 

[John Spencer]

What you posted has TWO ORDER BY clauses in it. Is that part of the problem
or it that a typo.


As for the line continuation problem, that can be solved by using something
like the following

StrSQL = "SELECT Fielda, FieldB, SomeOtherField" & _
"FieldJ, SomeTable.SomeField" & _
...

StrSQL = StrSQL & " FROM TableA Inner JOIN TableB" & _
" ON TableA.PrimaryKey = TableB.PrimaryKey" & _
...
StrSQL = StrSQL & " WHERE ...

StrSQL = StrSQL & " ORDER BY ...

By the way, if you are trying to add the WHERE clause, it has to be added
BEFORE the ORDER BY Clause.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..