Tell me if I did a clean workaround.
So I had the problem, after splitting my database, I would get Error 3048 "Cannot open anymore databases" when opening my report. My report shows a list of orders containing an item in a given time period. I pinpointed the problem to the subreport that contains the actual list of items. It is shown for each order in the list and has as its source a query containing all item orders in the database. It retrieves the appropriate items for each order from the query based on OrderID.
So what I did was saved a copy of the source query and changed it into an append query that adds the results of the query to a temporary table I created with the same fields. I also created another copy of the query and changed it to a delete query for the the same temptable. I then went to the main form that contains the button which calls the report. In the OnClick macro I added an action to run VBA code and placed it before the action that opens the report. The RunCode action opens a VBA function fncTempTabelle() I put in the main form's module as follows:
It works. Does all this look good? Did I miss an easier way to solve this whole problem?
BTW, I did this in the split frontend and realized the temp table is now in the frontend, so I had to go back to the unsplit database, add all this stuff I did, and split it again. Which made me wonder, is there a way to create a table from the front end that is stored on the back end?
So I had the problem, after splitting my database, I would get Error 3048 "Cannot open anymore databases" when opening my report. My report shows a list of orders containing an item in a given time period. I pinpointed the problem to the subreport that contains the actual list of items. It is shown for each order in the list and has as its source a query containing all item orders in the database. It retrieves the appropriate items for each order from the query based on OrderID.
So what I did was saved a copy of the source query and changed it into an append query that adds the results of the query to a temporary table I created with the same fields. I also created another copy of the query and changed it to a delete query for the the same temptable. I then went to the main form that contains the button which calls the report. In the OnClick macro I added an action to run VBA code and placed it before the action that opens the report. The RunCode action opens a VBA function fncTempTabelle() I put in the main form's module as follows:
Code:
Function fncTempTable() 'Temporary Table is used to avoid Database Connections error.
Dim dbs As DAO.Database
Set dbs = CurrentDb
dbs.Execute "qryTempTableErase" 'Clean any previous usage of the table
dbs.Execute "qryTempTableGen" 'Populate the table with current items
Set dbs = Nothing
End Function
It works. Does all this look good? Did I miss an easier way to solve this whole problem?
BTW, I did this in the split frontend and realized the temp table is now in the frontend, so I had to go back to the unsplit database, add all this stuff I did, and split it again. Which made me wonder, is there a way to create a table from the front end that is stored on the back end?