Re: 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.



<russellhq@xxxxxxxxx> wrote in message
news:1172239763.079204.59560@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • Re: Problems with Delete Command
    ... The SQL could get fairly messy if you need to construct it in code, ... ContactID, and WebComID, and create your on-the-fly SQL on that saved query, ... to find the list of ContactIDs from the junction table, ... This is a style/readability thing: if you are going to use string ...
    (microsoft.public.access.tablesdbdesign)
  • Re: multiplatform (pocketPC & desktopPC) (Daniel !!)
    ... Friend Versione As String ... Public Sub GetMyConnectionPalmare() ... Dim errorMessages As String ... Private Function GetDS_Desktop(ByVal SQL As String) As DataSet ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: multiplatform (pocketPC & desktopPC) (Daniel !!)
    ... Friend Versione As String ... Public Sub GetMyConnectionPalmare() ... Dim errorMessages As String ... Private Function GetDS_Desktop(ByVal SQL As String) As DataSet ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: Use ADO to retrieve data from Access Parameter Query
    ... I want to run an existing Access *Parameter* Query and pass 2 params to the ... Dim Param1 As ADODB.Parameter ... > Here is a function that returns a recordset based on a SQL statement. ... > Public Function RunQuery(ByVal strSelect As String, ...
    (microsoft.public.excel.programming)
  • Re: Need help with Code Please!!!
    ... posted in response to my last post was some SQL, but this is not the SQL that ... the actual string that gets built at the end of the SQ1 build process. ... The message says Syntax error in query expression ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ...
    (microsoft.public.access.formscoding)