I guess I need to learn about how data types get stored in an Access 2003 database. We use Access to store configuration data and system activity data for an alerting system that we sell. It has always served our needs just fine, no need for anything bigger or more powerful. The configuration data hardly ever changes. We are adding system activity data all the time.
The main host computer in the system sends messages about what it is doing (including any system errors) over to the Admin Station where the Access database lives. There is a Service there that parses the messages, gets the parameters out, and inserts pertinent stuff to the database, so we have a pretty good record of what the system does all day. The file grows over time, of course, but has never been a problem before. After 6 months or so I generally see the file getting to maybe 100 to 200 mb, and then I can purge older data, which isn't of that much use any longer. I'm aware of the 2 gb limit, and never really thought of it as a possibility.
Recently, for our newest version of system software, we decided to change the field types for several fields. We use lots of enumerations in our code, and the messages are built out of pipe-delimited enumeration values. We have tables in the database with fields that store those enumeration values. We've been nervous about the problems that could come from any changes to the enumerations, any additions that would make the number 1005 no longer stand for what it used to. We have agreed that the enumeration text items shall not ever be changed even if the number values change, and since we have access to that information at the Admin end of things, it's not hard to store the text version of enumeration values instead of numbers. Sounded like a good thing to do, so we did.
Now I discovered today that a week after deploying the new software at a customer site, the database grew by over 300 mb during the week. WOW!
I made other changes, added some tables, changed some of the code in the Service, but nothing that would account for it that I can think of except the change from numbers to text versions. I'm thinking that it probably takes what, 2 or 4 bytes for a 1005 and LOTS more for 'ErrorDiagCommunicatorRingNoAnswer'.
I looked in Access and found that all the text values were set to 50 characters in Properties. And Unicode Compression was set to no. I've tried to find information about storage needs for data types, and found one place where it said that unless you explicitly call for compression, each character will get 2 bytes. So 'ErrorDiagCommunicatorRingNoAnswer' would get 66 bytes? WOW again!
So I'm looking for references to more in-depth stuff about data storage. I'm no database expert, but I had more experience than anyone else on the team at the time so I became the database guy, and have learned what I have to as we go along.
Also I was astonished when I compacted the database at the customer site. It dropped to literally 10% of its previous size. I know that deletes leave deadwood in the file that needs to be cleaned up by compacting, but there is almost no delete code in my application. Almost all the queries are inserts, well over 99% of the queries I would say.
Any guidance? Thanks so much.
The main host computer in the system sends messages about what it is doing (including any system errors) over to the Admin Station where the Access database lives. There is a Service there that parses the messages, gets the parameters out, and inserts pertinent stuff to the database, so we have a pretty good record of what the system does all day. The file grows over time, of course, but has never been a problem before. After 6 months or so I generally see the file getting to maybe 100 to 200 mb, and then I can purge older data, which isn't of that much use any longer. I'm aware of the 2 gb limit, and never really thought of it as a possibility.
Recently, for our newest version of system software, we decided to change the field types for several fields. We use lots of enumerations in our code, and the messages are built out of pipe-delimited enumeration values. We have tables in the database with fields that store those enumeration values. We've been nervous about the problems that could come from any changes to the enumerations, any additions that would make the number 1005 no longer stand for what it used to. We have agreed that the enumeration text items shall not ever be changed even if the number values change, and since we have access to that information at the Admin end of things, it's not hard to store the text version of enumeration values instead of numbers. Sounded like a good thing to do, so we did.
Now I discovered today that a week after deploying the new software at a customer site, the database grew by over 300 mb during the week. WOW!
I made other changes, added some tables, changed some of the code in the Service, but nothing that would account for it that I can think of except the change from numbers to text versions. I'm thinking that it probably takes what, 2 or 4 bytes for a 1005 and LOTS more for 'ErrorDiagCommunicatorRingNoAnswer'.
I looked in Access and found that all the text values were set to 50 characters in Properties. And Unicode Compression was set to no. I've tried to find information about storage needs for data types, and found one place where it said that unless you explicitly call for compression, each character will get 2 bytes. So 'ErrorDiagCommunicatorRingNoAnswer' would get 66 bytes? WOW again!
So I'm looking for references to more in-depth stuff about data storage. I'm no database expert, but I had more experience than anyone else on the team at the time so I became the database guy, and have learned what I have to as we go along.
Also I was astonished when I compacted the database at the customer site. It dropped to literally 10% of its previous size. I know that deletes leave deadwood in the file that needs to be cleaned up by compacting, but there is almost no delete code in my application. Almost all the queries are inserts, well over 99% of the queries I would say.
Any guidance? Thanks so much.
