Access Split Database Issue

Mac1206

Board Regular
Joined
Jun 3, 2016
Messages
145
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
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,806
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,603
Office Version
2013
Platform
Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,089,740
Messages
5,410,157
Members
403,300
Latest member
ylee

This Week's Hot Topics

Top