Run-time error '3035': System resource exceeded.
Conan Kelly
I'm trying to programmatically create a union query (code at the
end): the same 80 columns from 12 different tables all unioned
together...results will be 80 columns wide. (please, no one mention
anything about database normalization...we get these files from a
client this way)
I keep getting the run-time error listed in the subject line.
I've tried this with the DB in 2000, 2002-2003, and 2007 file/DB
formats in both AC 2003 and AC 2007 and have gotten the same error
every time.
When Googling this error message, a couple things came up that I
checked. There was mention of MaxLocksPerFile. I set MaxLocks... in
the registry to 1,000,000 in 3 different places: "HKLM\SOFTWARE
\Microsoft\Jet\4.0\Engines\Jet 4.0", "HKLM\SOFTWARE\Microsoft\Jet
\4.0\Engines\Jet 3.x", and "HKLM\SOFTWARE\Microsoft\Jet\3.5\Engines
\Jet 3.5". Still getting error.
Also I saw mentioned something about a 250-column limit, but like I
said we are only going out 80 columns.
Could there be a limit on the number of characters in the SQL
statement. The SQL Statement for this query I'm trying to create is
66,300 characters long.
Is there anything else anyone can think of that might be causing this
error?
FYI: This code successfully created the query using 12 tables, that
are 74 columns wide each, using AC2003 with the DB/file in the 2000 DB/
file format, a few months ago.
Thanks for any help anyone can provide,
Conan
Code:
Sub CreateUnionReplace0sQuery()
Dim db As Database
Dim ptbl As TableDef
Dim pfld As Field
Dim pqry As QueryDef
Dim pstrSELECT As String
Dim pstrSQL As String
Dim pintIndex As String
Dim pstrField As String
Set db = Application.CurrentDb
pstrSQL = ""
pintIndex = 0
pstrField = ""
If mbytMaxFields = 0 Then
mbytMaxFields = MaxTableFields(db)
End If
For Each ptbl In db.TableDefs
' If ptbl.Fields.Count = 56 Then
If ptbl.Fields.Count = mbytMaxFields Then
pstrSELECT = "SELECT "
If pintIndex > 0 Then
pstrSQL = pstrSQL & vbCrLf & vbCrLf & "UNION ALL" &
vbCrLf & vbCrLf
End If
For Each pfld In ptbl.Fields
If pfld.OrdinalPosition <> 0 Then
pstrSELECT = pstrSELECT & ","
End If
If pfld.OrdinalPosition < 7 Then
pstrSELECT = pstrSELECT & pfld.Name
Else
pstrSELECT = pstrSELECT & "iif([" & ptbl.Name & "].
[" & pfld.Name & "]=0,null,[" & ptbl.Name & "].[" & pfld.Name & "]) as
" & Left(pfld.Name, 3) & "20" & Right(pfld.Name, 2) 'MmmYYYY
End If
Next pfld
pstrSQL = pstrSQL & pstrSELECT & vbCrLf
pstrSQL = pstrSQL & "FROM " & ptbl.Name
pintIndex = pintIndex + 1
End If
Next ptbl
pstrSQL = pstrSQL & ";"
db.CreateQueryDef "qryCoreData", pstrSQL
End Sub
Marshall Barton
so you have exceeded it. There may be more problems, but
you'll have to reduce the length of the SQL before you can
do anything else.
It may not be pretty to read but you can do some things
without affecting the way the query works:
1. Only the first SELECT statement determines the field
names, so if you must use field alias names, do not include
them in the other SELECTs.
2. Do not prefix field names with [table name] when the
field name is not used in more than one table in the FROM
clause. It looks like your SELECT statments only use one
table (ie. no joins) so you can safely omit the table name
everywhere except the FROM clause.
3. Use a single vbCrLf before and after UNION and/or use a
single space character instead of vbCrLf everywhere :-\
4. The ";" is not needed so get rid of it ;-)
--
Marsh
MVP [MS Access]
David-W-Fenton
news:6044d6c0-37a1-4d9b...@y19g2000prb.googlegroups.co
m:
> The SQL Statement for this query I'm trying to create is
> 66,300 characters long.
You are doing something completely wrong, then. There is no possible
reason that a SQL statement should ever need to get that long.
--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/
Tony Toews
<CTBar...@msn.com> wrote:
>I'm trying to programmatically create a union query (code at the
>end): the same 80 columns from 12 different tables all unioned
>together...results will be 80 columns wide. (please, no one mention
>anything about database normalization...we get these files from a
>client this way)
How about creating 12 queries with each of the 80 columns in it? Then
a short Union query which mentions the 12 queries with a "SELECT *
FROM QueryName Union ,..."
Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Conan Kelly
Thank you for your response.
I was able to cut it down to ~26,000 characters following your advice
and it worked just fine.
Thanks again for all of your help,
Conan
Marshall Barton
needed, incredible.