Databases
StackOverflow2013
Postsdbo
POMS Access out of stack space when creating query
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMS Access out of stack space when creating query
    primarykey
    data
    text
    <p>I am trying to create an Access database that will link together multiple structurally identical databases together. These other databases are exports from a 3D model that have identical tables, but different data in each. What I need to do is report from all the database like they are one big database. How I thought I would approach this is to create queries that would union the individual identical tables from each database into one query, which I could then use in all my other reports. the code I wrote to join the tables together is below. The trouble I'm running into is that , this code gives me an out of stack space error on the "Set QueryDef..." line. Can someone tell me what I'm doing wrong?</p> <pre><code>Public Sub CreateUnionQueries() 'On Error GoTo Err_CreateUnionQueries Dim QueryRs As DAO.Recordset Dim TableRs As DAO.Recordset Dim QueryDef As DAO.QueryDef Dim SQLText As String Dim qry As QueryDef 'Get list of all Foreign Table Names Set QueryRs = CurrentDb.OpenRecordset("select distinct ForeignName from msysobjects where ForeignName is not null") 'Loop over list to create union queries If QueryRs.RecordCount &lt;&gt; 0 Then Do While Not QueryRs.EOF Set TableRs = CurrentDb.OpenRecordset("select Name from msysobjects where ForeignName = """ &amp; QueryRs![ForeignName] &amp; """") Do While Not TableRs.EOF SQLText = SQLText &amp; "select * from " &amp; TableRs![Name] TableRs.MoveNext If Not TableRs.EOF Then SQLText = SQLText &amp; " UNION ALL " End If Loop 'Create union query For Each qry In CurrentDb.QueryDefs If qry.Name = "Q-" &amp; QueryRs![ForeignName] Then DoCmd.DeleteObject acQuery, "Q-" &amp; QueryRs![ForeignName] End If Next qry Set QueryDef = CurrentDb.CreateQueryDef("Q-" &amp; QueryRs![ForeignName], SQLText) QueryDef.Close Set QueryDef = Nothing QueryRs.MoveNext TableRs.Close Set TableRs = Nothing Loop Else MsgBox "No files are linked currently" End If QueryRs.Close Err_CreateUnionQueries: MsgBox "We have an error" Set QueryRs = Nothing Set TableRs = Nothing Exit Sub End Sub </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMridul Raj
    1. USBishop
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMS Access out of stack space when creating query
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMS Access out of stack space when creating query
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POMS Access out of stack space when creating query
      1. This table or related slice is empty.
      1. VTUpMod
    1. COHow many databases? Are you sure they are identical? I ran your code with four databases and tables and it worked for me, except for the missing Exit Sub.
      singulars
      1. POMS Access out of stack space when creating query
      1. USFionnuala
    2. COHave you tried running compact and repair on all of your databases? I think the Out of Stack Space error can come from corruption.
      singulars
      1. POMS Access out of stack space when creating query
      1. USHK1
    3. COWhat happens when you reduce the amount of time the while loop loops? I am wondering if you are reaching an upper limit of unions. It is also possible to have queries too large for Jet to handle. Could you add a debug.print statement to the SQLText and post the full query?
      singulars
      1. POMS Access out of stack space when creating query
      1. USdmarra
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload