Access Split Database Issue

[Mac1206]

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]

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]

Why do you need to put the two tables into a single temp table? Can't you work with two tables?


For instance:

select * from Table1 where Product = "ABC"
union all
select * from Table2 where Product = "ABC"

presumable that would be equivalent to

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