DB Elements size

[Christian1977]

Dear All,
how can I see the size of all elements on the DB, Tables, Queries, ecc...

[rpeare]

You'll have to be more specific, are you talking about a record count or something else?

[Christian1977]

Dear rpeare,


I'd like to see the size on MB or KB. I have a lot of DB that sometime go over 2,195 GB and this is a big problem.


Thanks and Regards, Christian

[rpeare]

Oh you mean the size in terms of memory usage for each table? I do not know a good way (or to be honesty *any* way) to do that, are you compacting your database regularly? Every time you use your access db a certain amount of memory is set aside every time you run a query, macro, form, etc and MS access does not release that saved memory until you do a compact/repair. Is your file size greater than 2gb even after compacting?

[Christian1977]

Hi,

I compact the DBs automatically every time that I close them.

In general the DB size after compacting is 1,5 GB. Thre are more or less 50 table, 100 queries and 10 Macro.

First I run a macro the DB with a macro with a filter that reduce the row of every tables, and than I close it. The new dimension is about 200MB.
After I run the Macro that I need and the DB after compacting is 1,5GB. I have to do the first step, otherwise I couldn't run my DB. Some table are about 1.000.000 row and 40,50 fields.

Thanks and Regards,

Christian

[rpeare]

Well, there are a couple of things you can try.

1. Make the data you want see in your access db a VIEW (basically a query) in SQL and link to the VIEW as your data source
2. Run some VB code that will connect to your SQL tables and extract only the records you want to look at to your local tables. This would likely require some processing time and I don't know if it would do what you want but at least it would limit your data to something smaller and more manageable.

[Christian1977]

OK,

I really appreciate your help!

Thanks

[Rainlover]

You could split the Back end into two back ends. Not a good fix but would get around your problem for now.

A better approach would be to change to SQL Server if you have it. I would however use MySQL. In both cases you would not need to worry about the 2 Gb limit.

[Christian1977]

Unfortunatly,


I can't use Sql Server or Mysql (Company Policy) but I'll try to explain them my problem and if I can fix it through this way.. maybe they could give me the permission.
There is any size limit with Mysql?

[Rainlover]

By the time you exceed MySQL your Database will be out dated and time for a new one.

If you really want to know you would need to Google for the answer.

More Databases would be written with MySQL than SQL Server. Most Web sites use MySQL so you can be assured that it is a good engine. WEB Sites don't use Access they normally use PHP.

It is very easy to upsize to MySQL and still use Access in a local environment. Finally it is Open Source so there is no cost.

[Christian1977]

OK,

Thanks and have a good night in Queensland!

Christian

[Rainlover]

Christian

You should now be looking for a reason as to why your Database is experiencing BLOAT.

Are you deleting the contents of a Table then repopulating it with new data.

If so try deleting that table altogether, create a new one then populate the new table. This can be done automatically and would only take a second or two longer.

If this is not the cause then you should be looking further a field. If you want to know why it is bloating explain the steps involved in your process.

[ssanfu]

I agree with Rainlover, but I thought I would throw out an alternative to SQL Server

You might think about SQL Server Express Edition. It is free and will run on a workstation. It has a 4GB file size limit.

[Rainlover]

I agree with Rainlover, but I thought I would throw out an alternative to SQL Server

You might think about SQL Server Express Edition. It is free and will run on a workstation. It has a 4GB file size limit.

Steve

There are license limitation to the Express Edition. With your experience you would have a better knowledge of them than me. I think the number of users is one. (I could be totally wrong here)

I have found MySQL much easier to use. Again I have limited experience. I did write a Database for a web page. It had over 200 tables and I had no problems with it. I did mot write the PHP.

This is just my limited experience. I read once that 80% of all servers use MySQL.

[ssanfu]

@Rainlover:

There are license limitation to the Express Edition.

We (my company) will be switching to SSQL (we need the security) this year (we hope), so I am doing the conversion from A2K to SSQLE as a test.
SQL Server Express is SQL Server, but throttled down. AFAIK, SSQLE 2012 can handle unlimited connections and has a 10 GB limitation (up from 4GB). It looks like SSQLE 2008 also has a 10 GB file limit.

I have found MySQL much easier to use.

Years ago, I started learning about MySQL and MSQL, but life intervened... never got back to them. I'll have to make time... (soon?)

I found two sites with some info, but not the number of users:
http://blog.sqlauthority.com/2009/08...ference-guide/
http://sqlmag.com/sql-server/sql-ser...press-editions


I'm not saying that anyone RDMS (MySQL/SQL Server Express/Alpha 5/???) is better or best, just that IF you wanted to use SQL Server, you don't have to spend bucks - the Express edition (now up to 10 GB) *could* do the job AND run on the local machine.


I also have not had/made time for Alpha 5 or Firebird (Open source RDMS by Mozilla)