Database size issues

[unmarkedhelicopter]

Hi All, I recently was advised (in an Excel Forum) that I should NOT use Integers in VBA stuff as Excel does the calculation as if it were a Long anyway and then converts it to Integer and therefore this takes longer.
My question is ... Am I still correct to try to use the smallest fields in a database, eg binary, integer, long, single, doulble. In THAT order ?
To try to minimise the amount of space I consume with the mdb. I realise that with the db's I'm talking about (circa 2k records) it may not make much difference, but as a general priciple ?

Thanks in advance for your comments.

unmarkedhelicopter

[Marcster]

I should NOT use Integers in VBA stuff as Excel does the calculation as if it were a Long anyway and then converts it to Integer and therefore this takes longer.

I didn't know VBA auto converted Integer variables to Long, so checked MSDN.
Yep,
Variables of type Integer are indeed auto converted to Long.
So it makes sense to declare Integer variables as Long.

Marcster.

[unmarkedhelicopter]

I'm sure that's what I said ! :)
Now, what about the question ? ;?
It seems pointless to me to use Longs, then when you need to assign them to an integer field in a db you either have to check them or risk an error !
Is it good db practice to use appropriately dimensioned variables, regardless of where they were derived ? :>

[matthewspatrick]

Well, there are two different issues here:


Managing MDB size
Managing VBA code
In setting up your schema, feel free to use smaller data types if it is appropriate for your solution. Just be careful about what you do: setting an ID field for a table that is expected to have a dozen or so records to Integer may be fine, but should never be done on the OrderDetails table in an order tracking database (because it is easy to see that table growing to over 32,767 records).

In your VBA code for that app, just use Long instead of Integer, because VBA will do implicit conversions to Long and then back to Integer if you don't.

[unmarkedhelicopter]

Thanks for the response,
I appreciate the VBA side of things, as I said; I was advised; I did the research and integers did not make the cut .... but ...

All I'm asking is; from the database point of view, (this is a database section of the forum ?) I'm not talking about key fields, I'm not talking about Order Number Fields, I'm not even talking about date fields (lets use two digits for the year ! who cares about Y2k !) I'm talking about data in the data table. I have some tables with 20-30 fields and half of them are integer, is that stupid ? or does it save database size and how does it affect performance of SQL queries run against it ? etc.
STOP TALKING ABOUT VBA CONSTRAINTS ! DOES ANYONE KNOW ANYTHING ABOUT DATABASES ?

[GaryB]

Everyone connected with this forum is here through the grace of their generosiity and kindness. I have been helped and have helped on numerous occasions. Might I suggest a little more diplomacy and a little less shouting when posting a request. We all have a common goal to help each other and it can be done with a civil amount of respect.

Garyb

[matthewspatrick]

I'm talking about data in the data table. I have some tables with 20-30 fields and half of them are integer, is that stupid ? or does it save database size and how does it affect performance of SQL queries run against it ? etc.
STOP TALKING ABOUT VBA CONSTRAINTS ! DOES ANYONE KNOW ANYTHING ABOUT DATABASES ?

Did you bother to read my post? :whip

I said in pretty clear English that you should feel free to use smaller data types in the table design even while using Long instead of Integer in the VBA code.

Now, undo that Caps Lock and try being a little less belligerent, OK?

[unmarkedhelicopter]

I appreciate that people have bothered to respond.
I was not shouting with anger, I was shouting with frustration.
I stated why I was asking the question and then I asked the question.
In the responses I recieved I was lectured why I should do what I "Already" do (re integers / longs) and then completely avoided (or at least skirted) my question. If I wanted that I'd ask my mother. I would like a meaningful discussion about database design and use. Can any of you say 'hand on heart' that you feel that is what was recieved ?
I would like to move forward on this but I am working against a time limit to design a db to work with a new application. So any HELP would be more than gratefully recieved, any hurt feelings I appologise, but can we please keep it on subject ?

[matthewspatrick]

I was not shouting with anger, I was shouting with frustration.


Oh, that makes me feel so much better. It's OK to yell at people if you're frustrated. I'll have to remember that :devil2:

[stanl]

The original question sounds like a take home 'final test'. VBA is loosely typed - period. Access is good at what it does, not great.

.02
Stan

[Norie]

unmarkedheli

The long/integer thing really has nothing to do with database design.

It's a VBA thing.

Are you using VBA?

[unmarkedhelicopter]

Norie,
I'm using Excel as the front-end client application for a co-operative data base (an mdb stored in a central location). The reasoning for this is that in 'most' cases, 'most' people have Excel, 'most' people do 'not' have Access. I want up to ten people to simutaneously access and update 'different' record's, then when all clients are ready, a 'master' client processes all input and allows the clients to continue to the next phase. The database size will grow over time from circa 100 records to circa 3000 (worst case) then new records added will be matched by previous transactions acted upon and discarded. This will allow people in small companies (without Access on their desktops) and also people with home networks (again w/o Access) to use the application seemlessly. (And with no data base knowledge). The database I have is designed to consume as little data storage space as I can get away with, in my mind smaller is better, more data per page file, quicker access and better response. I am using MS Jet and Replication Objects 2.6 Library with ADO and Batch Updates, Also Array grab and Dump techniques for speed, Custom Arrays for specific data areas etc. So Yes I'm using VBA. I'm not going (and I don't expect any of my users) to spend any money on SQL servers or anything fancy, I just want a cheap, cheerful and hassle free implementation. I don't expect major performance gains for this as I'm pretty much of the opinion that this one is now cast in concrete. Future implementations, should however try to use whatever I manage to learn, my experience with 'non-local' database applications (and mdb stuff in particular) is limited, so I thought I'd try and find someone who has some experience they would be willing to share, so I went to an Access forum.

If you think long / integer is nothing to do with database design then I can just put whatever I want in (given VBA issues Longs rule ! and Variants too !); then and I can stop giving the DB cognesceti here any hassle, I honestly did not wish to offend, I honestly just wanted an answer to my question.

[unmarkedhelicopter]

Well ... I think I'm glad you're feeling better ... If you don't wish to accept my apollogy for any percieved insult I may have left you with then I thank you for your words of wisdom and humbly acknowledge your right to sound forth, or not, as is your inalienable right.

[unmarkedhelicopter]

You are correct, again I appologise. If my words offended (intentionally or not) "Half the communication is in the speaking and half in the listening." In this I am not without sin and the format of simple typed text is ambiguous at the best of times without the visual clues of meaning and intent from personal contact. So much is gained from experience of a contact, and of that with you and you with me, we have none.
If my question was not anwered it was because I did not express it in the language, schema and experience set of my audience. Again I appologise !

[unmarkedhelicopter]

I am not familiar with the 'final test's of which you speak ! Your advice on how to tighten up my typing of 'VBA' would be appreciated. If you would care to peruse my response to 'Norie' it may elucidate my intended aims and you would then be able to enlighten me as to a superior course of action if you feel able and willing. Thanks in advance !

[stanl]

Speaking for myself, no apologies are necessary... of course I've never been called a "cognesceti" before. I tried to look it up... maybe it's a really, really, really, really bad thing and can't be printed.:dunno

Stan

[geekgirlau]

As Patrick stated in an earlier post, always use the smallest appropriate data type in your table structure - the behaviour noted for VBA does not apply. Obviously a little forethought will go a long way - keep in mind the potential number of records that are going to be required, particularly for primary key or indexed fields.

[unmarkedhelicopter]

So just use common sense ! :)
Thanks !

[geekgirlau]

Let me know if you'd like me to mark this thread as "solved" (there's a problem with this action at the moment).