Access Split Database Issue

Mac1206

Board Regular
Joined
Jun 3, 2016
Messages
125
I have a Front_DB_A with a table called RS and another Front_DB_B with a table called RS_Hist, both are split databases and have tables linked to the Front_DB_A database. My dilemma until we can move it to SQL Server which we are waiting on is that I have a run a query that will combine both tables in a MKTbl_AB which puts me at 5,464.319 rows of data and over the 2g capacity mark...

Is there any work around this issue until we can get the tables into Sql Server which probably is going to be a few weeks until they can get to it...Like instead of the TblTempAB, create a recordset of the combine data in a form view and run the queries against that? You assistance is greatly appreciated...Thanks
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,742
The details are a bit confusing. If I ignore the word Front and suppose these are back ends (because they have tables RS and RS_Hist) and they are split. But they have tables linked to Front_DB_A ??

Regardless, it may not be pertinent. That is 5 million records and not 5 thousand and some fraction, yes? If the records are in 2 separate back end files and each file is well under 2Gb and front ends are linked to them, then I don't understand the problem. Surely you are not trying to load 5 million records into a query, form, recordset or report all at once, or creating temp tables (TblTempAB)?
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,510
Why do you need to put the two tables into a single temp table? Can't you work with two tables?


For instance:

Code:
select * from Table1 where Product = "ABC"
union all
select * from Table2 where Product = "ABC"
presumable that would be equivalent to

Code:
select * from
(
select * from Table1
union all
select * from Table2
) X
where X.Product = "ABC"
 
Last edited:

Forum statistics

Threads
1,077,828
Messages
5,336,622
Members
399,093
Latest member
chado4250

Some videos you may like

This Week's Hot Topics

Top