split mdb error 3048 cannot open any more databases: adp a solutio

[Guest]

Hi Everybody,

I'm using Access 2003. A union query based on 12 queries is causing trouble
(error 3048) if I split my database. However it works fine when the database
is not split (single mdb file). I should mention that my database has a total
of 30 tables and that the queries used for the union query are quite complex
(some of them consist of 10 tables/queries). There is no code involved at
this level.
Since I would like different users to share the application on a network,
it's necessary to be able to split the database. Not being able to do so
would be a major setback.

As far as I understand, error 3048 arises from the fact that Access
allocates table IDs for each table used in a query and there's a maximum of
table IDs that Access can handle simultaneously. Also, more table IDs are
allocated in a split database than in a single-file database. Error 3048 is
triggered whenever there are no more available table IDs.

Given the size of my application (number of tables and resulting complexity
of relationships), is the only way for me to set up a client-server
architecture to convert my mdb file into an Access-Project (adp), using
MSDE/SQL Server as back end and Access as front end? Is this the way to go or
am I missing something here?

Any help greatly appreciated

Thanks

bronson

 

[Allen Browne]

How about creating a temp table to hold the output from this query.

Change the 12 queries into Append queries, and execute them to populate the
temp table.

Since Access is executing them in sequence, not all at once, it should work
around the limitation error 3048 indicates your UNION query is hitting.

 

[TC]

I hesitate to make this comment, because I seem to be having no luck
recently, suggesting to people that they review their table designs!

But the simple fact of the matter, is this. There would be very, very
few situations where you would really need to have a UNION of 12
tables. Indeed, there are not many cases where you need to union /any/
tables! In over 30 years of professional software development - mainly
focussed on database software - I have never needed anything remotely
approaching 12 UNIONs.

So I have to say, your table structures might be suspect!

If you wanted to post them, someone could comment. For the sake of that
dicussion, all we'd need is the names of the fields (not types &
lengths), and a clear indication of what is the primary key of each
table. *No SQL*.

Just my 2c ...

TC (MVP Access)
http://tc2.atspace.com

 

[Guest]

Hi TC and Allen,

Thanks for your thoughts. I think I need to clarify a couple of points
regarding my 12-union query. Its main purpose is to hold data like a matrix.
In this case, the union contains all different transactions affecting
inventory.
The first columns describe each position (TransactionID, Date, ItemID,
Description). Then, each query drops the data in its respective column,
setting all other columns to 0. Here's a simplified example of how it works:

tblAdditions
ID, Date, ItemID, Description, Total
------------------------------------------
A1 01/01/06 1 Item A 100
A2 01/02/06 2 Item B 10

tblOrders
ID, Date, ItemID, Description, Total
-------------------------------------------
O1 01/05/06 2 Item B -5
O2 01/31/06 1 Item A -25

The Union would then be

Union
ID, Date, ItemID, Description, Additions, Orders
---------------------------------------------------------
A1 01/01/06 1 Item A 100 0
A2 01/02/06 2 Item B 10 0
O1 01/05/06 2 Item B 0 -5
O2 01/31/06 1 Item A 0 -25

Some queries may appear twice based on different conditions and that's how I
end up with 12 queries in that union.

I then use another query which sums up totals for each item in this union to
get the desired result. As I said, it works perfectly in a single file mdb.
The union is executed in 30 secs, which is still acceptable. I only get the
error message when I split the database.

TC, I think that the overall structure of the database/tables is ok, but I
would probably have reacted the same way reading "12-union".

Allen, as you suggested, I think a temp table should solve the problem.
Could you give me any advice on best way to set it up? Could you also
possibly tell me whether it would make sense to switch to an adp?

Again thanks & rgds to both of you

bronson

 

[Allen Browne]

I doubt an ADP will solve the problem. You will still need as many
connections unless you change your approach.

The temp table will need the the 5 fields you show in your example, possibly
with an AutoNumber as well.

To change a query to an Append query, open it in design view, and choose
Append on Query menu. Access will ask what table to append to.

You can then populate the temp table with this:
Dim db As DAO.Database
Set db = dbEngine(0)(0)
'Clear the temp table.
db.Execute "DELETE FROM tblTemp;", dbFailOnError
'Exeute each of the queries.
db.Execute "Query1", dbFailOnError
db.Execute "Query2", dbFailOnError
...

It will require some adjustment if the queries contain references such as
Forms!Form1!Text0, since the Expression Service will not interpret those
references in the context of Execute.

 

[TC]

Hi TC and Allen,

Thanks for your thoughts. I think I need to clarify a couple of points
regarding my 12-union query. Its main purpose is to hold data like a matrix.
In this case, the union contains all different transactions affecting
inventory.

But why several tables? Couldn't you have a single table, with a
"transaction type" in each record?

tblAdditions
ID, Date, ItemID, Description, Total

Could the same Item (eg. ItemID 1) appear in more than one record? If
so, would all of those records (for that item) always have the same
Description? If so, this table is not designed properly. The item
descriptioins shouldn't be in it. The same comment applies to
tblOrders.

tblOrders
ID, Date, ItemID, Description, Total
-------------------------------------------
O1 01/05/06 2 Item B -5
O2 01/31/06 1 Item A -25

TC, I think that the overall structure of the database/tables is ok, but I
would probably have reacted the same way reading "12-union".

If it works for you, fine. But there are clear indictions, already,
that the design is not correct in theory

Cheers,
TC (MVP Access)
http://tc2.atspace.com

 

[Guest]

Hi TC and Allen,

Allen, thanks for the temp table setup instructions. I'll definitely try
that.

TC, the main problem is that the purpose of the example was to give you an
idea on the math behind the union (vs a 1:1 simplification of my current
database design - apologies if this was misleading). I understand your point
to have an overall Input table that can handle different transaction types.
In fact my orders table handles different transaction types (outflows) , but
not all.
Without getting into too much detail here, you would have to imagine that
there are basically two types of products:
1) the first category is very straightforward: it's defined through an
ItemID, quantity & price
2) the second category is very complex, as it has a detailed set of
additional attributes other than its ItemID to qualify it (most importantly
the BoxID, DesignID, ColorID and SizeKey fot the total quantity, price). In
short: for this item category, additions to inventory are not made the same
way as deductions from inventory (orders). For example, 1 single ordered box
can contain different numbers of different designs (which can have different
prices) in different colors in different sizes.

That said, it works. I'll try to simplify the input of the union. I
mentioned that several queries were integrated several times into the union
on different criteria. I think that if the values for these criteria are
already calculated in their respective queries, the number of unions will be
reduced dramatically (each query is only integrated once into the union). I
really appreciate your willingness to help

Thanks & rgds

bronson

 

[Tony Toews]

I'm using Access 2003. A union query based on 12 queries is causing trouble
(error 3048) if I split my database. However it works fine when the database
is not split (single mdb file).

I agree with Allen's suggestion. I've had this same problem myself
when my UNION queries got so complex. But I'd add one suggestion.

See the TempTables.MDB page at my website which illustrates how to use
a temporary MDB in your app.
http://www.granite.ab.ca/access/temptables.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Guest]

Hi Tony,

Thanks for your help. I solved my problem without temp table, but I'll
definitely learn this technique. In this case, as I told TC, I integrated a
couple of calcs in the underlying queries of the union so that each query
would only appear once in the union. Now the application works (again) in
split mode.

Many thanks again to everybody for their help & ideas

rgds,

bronson