Access UNION query won't run, data limits with a small file and almost nothing in it???

Maggie Barr

Board Regular
Joined
Jan 28, 2014
Messages
184
Greetings, and thank you in advance if you can assist.
I am using Microsoft Office Pro 2019, so Access and Excel Power Query 2019, on a PC, 64 bit, 16 gigs ram, windows 10.

I have a database that choked when I tried to run a UNION query. I saved a copy, deleted any other information other than the two linked files I wanted to UNION, and tried to run the query and it still choked. The databse size is showing 512 KB, the two txt files that are linked (not physically loaded) are 1,245,342 KB (or 3,504,398 records) and 797 KB (or 1717 records). I know there is a database size cap of 2 GB, but the database itself isn't even close to that, and the files in it are only linked, so it seems to me that another query to run a UNION of the two (not loading them to a physical table) should still run. I am pursuing other routes to try to merge these two txt files, but I am having no luck with that. I was able to merge them fine in Excel's Power Query, but there is no way to export that query as a txt file like in Access, which is what I want to do so I can get the data into my next database. I have a series of databases I have had to split as the dataset has grown. This just seems like it should not be a problem considering the data being used is only linked.

Can anyone tell my if my scenario should actually be causing a data cap limit, or if there could be something else wrong. I already have compact on close, and clear cache on close, enabled. I have done the compact and repair database multiple times. If I am really at capacity, can anyone recommend how to merge two txt files, as I am past the notepad limit for such.

Again, thank you in advance if you can help, I am in a bad spot trying to jump this hurdle.
Sincerely,
Maggie Barr
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,825
Office Version
  1. 2019
Platform
  1. Windows
I see. It is interesting. Almost certainly there is a path upward here (MySql being one good possibility since its free and has good driver support) ... but if there is a lot of investment in the current application (based in Access) it may be more than a weekend project to migrate (pun intended) to a new database system. Are you a paid programmer or a volunteer? I think I have seen your posts before ... the bird data sounds familiar.
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Maggie Barr

Board Regular
Joined
Jan 28, 2014
Messages
184
I see. It is interesting. Almost certainly there is a path upward here (MySql being one good possibility since its free and has good driver support) ... but if there is a lot of investment in the current application (based in Access) it may be more than a weekend project to migrate (pun intended) to a new database system. Are you a paid programmer or a volunteer? I think I have seen your posts before ... the bird data sounds familiar.
Loved the Pun! Well, I am not an official programmer at all. I got a degree in Environmental Sciences with a concentration in Conservation Biology in 97, but databases were not part of any curriculum. I work for a non-profit and have handled their data for years, generally smaller simple things in excel, dabbled in some database files, producing reports, helping with field guide development etc. We are working with other groups, (IF&W, eBird, Audubon, and others I am not that familiar with), putting together a Breeding Bird Atlas for Maine. All in all, out of the meetings between all the parties involved that I was not at, the data just wound up coming my way and I was asked if I could try to do what they needed, so I tried, and so far am able to. I actually don't even meet most of the people I provide data for, or even communicate with them, I get files, requests, and send it back along...sort of the closet data geek I guess. It has been a fun, educational, and very rewarding. I wish I had another lifetime to learn all the things I'd like to, but I like to say, it isn't what we know but what we are capable of learning, and perseverance and determination can make it happen. It may not always be the best way, but I manage to get there. There is a ton of investment in the current process, so I need to keep it rolling to keep the turn around time good for data requests, but if I get some breathing room at some point, I know I need to branch out. Thanks for your advice!
Best Wishes,
Maggie
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,825
Office Version
  1. 2019
Platform
  1. Windows
Okay. Glad to help in any way.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,825
Office Version
  1. 2019
Platform
  1. Windows
Note that Python + Pandas ( and possibly + Sqlite) might be right up your alley if you are working with scientists - you can do pretty good data munging and data storage with these tools also.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,847
Messages
5,627,239
Members
416,232
Latest member
Ash1432

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top