Any way to tell which tables are taking up the most room in a database?

[Access9001]

I find myself hitting the 2 GB limit pretty frequently with a few of my backends, so I want to split them up. But how can I tell which things to split off first?

 

[boblarson]

I don't know about how to get individual table sizes, but perhaps moving to SQL Server Express 2008 is an option. It can handle 10Gb now.

 

[Access9001]

What would I have to re-learn?

I've got so much infrastructure set up in my Access framework (dynamic module processes, PDF printers, etc) -- would I be able to still use all this stuff if I switch to SQL Server Express?

 

[boblarson]

What would I have to re-learn?

Not a lot if you wish not to. You don't need to use stored procedures or anything if you don't want to. You can if you want, which can enhance some speed depending on what you have, but it isn't necessary.

You link your tables and then you can basically treat them as if they were in Access. There might be a few minor changes necessary but for the most part there usually isn't.

I've got so much infrastructure set up in my Access framework (dynamic module processes, PDF printers, etc) -- would I be able to still use all this stuff if I switch to SQL Server Express?

Again, most of that would be able to be used as before. Of course there might be some changes (can't think of any right now) but most apps don't need much to change to use it. I work with a company right now which uses SQL Server 2005 as the main backend and then we also have some Access backends linked in (depending on the application). So you can even mix them. You will need to ensure that your tables have primary keys in them so you can update the data.

Hope that helps. And here's some more info.

 

[Access9001]

Could I, for instance, use automated Access frontends with SQL Server backends for the sake of holding up to 10 GB data?

How might this differ with the .mdb structure? Does SQL Server leverage the same file formats/associations?

 

[boblarson]

Could I, for instance, use automated Access frontends ...

Not sure what you mean by automated Access frontends but the frontends basically act as if nothing is different between a SQL Server backend and an Access backend.

How might this differ with the .mdb structure? Does SQL Server leverage the same file formats/associations?

I don't know what you mean how it might differ with the mdb structure, nor about the file formats/associations part you are asking about. Can you give me an example of what you are thinking about?

 

[Access9001]

Well, currently I use Windows Task Scheduler to kick off some automation-oriented Access frontends that kick off some Make-Table queries that create tables in some backend mdb Access databases. The problem is that these backends can blow up to 2 GB pretty easily sometimes and cause issues with the automation.

It would be ideal to leverage a backend structure with a larger size capacity without screwing up the main infrastructure that's already in place.

 

[boblarson]

Okay, so make table queries would be a bit of a problem. Why are you using make table queries instead of some other method? What is the purpose of those queries and tables?

Second, you could still use Task Scheduler to open your Access frontends just like before and let it run whatever - to a point. Again make tables are going to be problematic in SQL Server (it isn't that it can't be done but it can't be done - as far as I know - the same way you can in Access. I could be wrong about that as an Insert Query might actually be able to create it or you can change to a stored procedure which would do it).

 

[Access9001]

The purpose of the tables is to fuel other queries/reporting procedures/export functions. They're just recreated everyday because the raw data changes from day to day, and Access is the medium in which such reporting is conducted.

A delete-and-insert approach would accomplish the same thing, but problems would arise if the insert query can't be completed for any reason. I would have deleted the contents of the tables, and it's typically better to at least use one-day-stale data than no data at all.