If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Building SQL statement in VBA. Getting 3131 Runtime Error in FROM clause :@
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 |
#2
|
|||
|
|||
Building SQL statement in VBA. Getting 3131 Runtime Error in FROM clause :@
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. wrote in message ps.com... 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 |
#3
|
|||
|
|||
Building SQL statement in VBA. Getting 3131 Runtime Error in FROM clause :@
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];" |
#4
|
|||
|
|||
Building SQL statement in VBA. Getting 3131 Runtime Error in FROM
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 -- Email address is not valid. Please reply to newsgroup only. " wrote: 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 |
#5
|
|||
|
|||
Building SQL statement in VBA. Getting 3131 Runtime Error in FROM clause :@
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. wrote in message oups.com... 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];" |
#6
|
|||
|
|||
Building SQL statement in VBA. Getting 3131 Runtime Error in FROM clause :@
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 .. wrote in message oups.com... 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];" |
Thread Tools | |
Display Modes | |
|
|