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,743
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,527
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,078,500
Messages
5,340,746
Members
399,393
Latest member
farlow

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top