Forum Discussion
smith477
Feb 09, 2023Copper Contributor
Error 3048 when exporting
Hello,
I am attempting to export a database to an excel file. Access creates an empty file, and throws the error "Cannot open any more databases", which from what i've seen so far, is error 3048. I only have the one database open.
I have checked the available connections using Isladog's addin (Available Connections Add-In (isladogs.co.uk)) and it only shows 4 connections being used, so I'm definitely not going over the 255 limit. I also checked to make sure it wasn't the old 2022 issue where access wasn't closing databases fully, but the only access database process visible in Task Manager is the one that should be there.
I can also open up other databases just fine.
I am using Access 2013
Processor 11th Gen Intel(R) Core(TM) i5-1145G7 @ 2.60GHz 1.50 GHz
Installed RAM 16.0 GB (15.7 GB usable)
System type 64-bit operating system, x64-based processor
The design view of the database has this:
SELECT *
FROM D_Blank;
UNION SELECT*
FROM D_ASN_TYP_QRY;
UNION SELECT*
FROM D_BL_QRY;
UNION SELECT*
FROM D_CNTR_QRY;
UNION SELECT*
FROM D_CNTR_TYP_QRY;
UNION SELECT*
FROM D_CNTR_WT_QRY;
UNION SELECT*
FROM D_OCNCARR_QRY;
UNION SELECT*
FROM D_POL_QRY;
UNION SELECT*
FROM D_SN_QRY;
UNION SELECT*
FROM D_VOY_QRY;
UNION SELECT*
FROM D_VSL_QRY;
- Tom_van_StiphoutSteel ContributorShould this really be "UNION", or "UNION ALL"?
You are very likely running into the max complexity for an Access query.,
As others have said: a discussion of your database design may be in order. Be open to that, please.
If you don't want to go there: turn the first query into MakeTable, and the others into Append queries, then export the table. A horrible solution in my book, but it would get the job done without errors.- MendipDataSystemsBrass Contributor
Hi. Nothing much to add to the above suggestions but glad you found my available connections add-in useful, if only to eliminate one possible reason for your current issue.
You may also wish to investigate increasing the MaxLocksPerFile and MaxBufferSize settings.
- George_HepworthSilver Contributor
I, too, was seeing a big red "Repeating Columns" light flashing over this problem, but without that detail you request, it's all a bit of guesswork.
- loufabCopper ContributorHi,
The sources of these SELECTs are queries, they may open several tables.
I would do successive Exports of each request rather than these UNION consumers.
In automation use CopyFromRecordset of Excel library.
https://learn.microsoft.com/fr-fr/office/vba/api/excel.range.copyfromrecordset