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

[Maggie Barr]

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]

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.

 

[Maggie Barr]

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]

Okay. Glad to help in any way.

 

[xenou]

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.