将光标移到/点击文章中的句子上,可以查看译文。      显示繁体中文内容    显示简体中文内容

MS-Access: Too many tables and way too big of a file - Multiple linked-tables query?
MS Access: 太多的表和多个链接表查询的太大?

0 0

So just FYI, i have very limited SQL training, and my new position is requiring that i learn so please be gentle with me : )

First off, i saw this :

Multi-table query in Microsoft Access

And that is exactly what i need in principle; however, my data structure is a little different. i have roughly 24 databases each averaging around 1gb of information over 8 years across 420 institutions (4 databases for each year - about 16 million observations in total). every field is identical across databases.

I need to run analyses on all of this information, but ms-access databases are limited to 2 gb, so i'm trying to figure out a workaround. my idea is to link each table into a master database, and then run queries using the selection query from the above link. my question is whether or not this will actually work. my computer has 32gb of physical memory, so i feel like i should be able to load all of it into active memory while performing any queries, but i still don't know. is there a more efficient way?

Basically, what i need to be able to query institutions over years. right now that's impossible and it's causing issues. because institutions aren't subjected to any scrutiny regarding the information they report, we need to understand how reporting trends evolved within and between them over time.

I was given a list of about 40 questions that all involve different queries that we need to run, so i'm hoping against hope that i can figure out a solution that doesn't involve me losing my mind.

Thank you all for your help!

-David

时间: 原作者:

0 0

It is perfectly acceptable to link all the tables into one master database. in my previous experience i had one master database with over 80 backends, many with one large table. the issue with querying them is that a single Access query can also only be 2 Gb. so if it any time in the processing your query exceeds that limit you'll get an error. it can be very annoying. for me the best way to work around this is temp tables and driver/parameter tables. use driver/parameter table to store what data you'll be pulling from a source. this creates more efficient queries. especially if you index on the join fields. in my master database i always had a form with VB code to load the driver/parameter tables.

Now all that said you'll be much happier in a different environment, even SQL Server Express. then you'll be able to use the 32Gb of memory. otherwise you'll always be dealing with not only the Access 2Gb file/query limit but also that Access is a 32-bit application and it can't address all the memory you have.

...