Hi !

I am designing a program (written in C, using MS ODBC) that has to do some stuff with a MS Access database.
I don't have much experience with databases, the ones I used stored dates as 3 numerics fields : one for the day, one for the month, and one for the year.

Now, MS Access has a specific field type for dates... but includes the time/hour too, which is an useless information in my case...

Based on your experiences and in your opinion, what is the most efficient and professionnal way to store the dates inside the database ? A single field, or using three fields ?

Thanks.
Posted on 2003-03-18 03:55:52 by JCP
use the DATE type field. It's made for that purpose. As for how its data gets out you can choose but for storage you normally should opt for your database' closest inherent datatype to the type of data you wish to store.
Posted on 2003-03-18 04:10:29 by Hiroshimator
Ok, thanks ! I tried it, and it seems to be fine.

Now, I assume that the time in this date field takes some room... the date will be in every record of the main table of the database : something like 4 000 000 records by year.
If this field takes, say, 8 bytes, it will take big space... when I only need the first fourth bytes for my date (it is an example, I don't really know the size of the Access Data types).

Is it possible to restrict the Date/Time type to only have Date ? Of course, if sizes are the same, it is not too much important.

Thanks.
Posted on 2003-03-18 05:16:55 by JCP
if you're worrying about size then you should not use access.

a lot of databases keep dates as a timestamp, I don't know about access but it might be the same thing. In which case it's just a big number, such things do not take a lot of place.
Posted on 2003-03-18 06:28:42 by Hiroshimator
The DATE type in Access is 8 bytes...
Too bad since SQL Server has a short date type (without time) 4 bytes long...

Thanks for your answers.
Posted on 2003-03-18 07:36:34 by JCP
Or, get / create a library which converts a date to/from a timestamp, then store them as integers in Access. That way, should you need to switch db engines someday, you can save yourself the trouble of converting from Access' DateTime type. IMO (i.e, I haven't bothered to do any tests whatsoever), ints are lot faster to sort or search through than date types.
Posted on 2003-03-18 07:41:17 by pixelwise
use MSDE

it's a mini SQL server
Posted on 2003-03-18 08:30:05 by Hiroshimator
Pixelwise: good idea but my database will also be accessed by the outside world with tools like SQL Query for linking with Excel... in this case, it won't work. :-(

Hiro: thanks for the idea of MSDE, but I guess you are right and it would be like a B52 to kill a fly... I will try to save space somewhere else to compensate.
Posted on 2003-03-18 08:36:31 by JCP
are you really space impaired?
then you should go for the bigger solution and not use a desktop database.
things like access handle many records very poorly.
Posted on 2003-03-18 08:41:08 by Hiroshimator
Many years ago, back when mainframe disk drives were only 28 Meg each, I worked on a system that saved dates in a 16 bit compressed binary format. We used it on things like transaction dates, where thousands of transactions were generated each day. The compressed date looked like this:

YYYYYYYMMMMDDDDD

We had a couple of macros to compress and expand the date to normal MM/DD/YY character format, for printing, display, etc.

One nice thing about it was it made sorting by date easy, being in the YMD format. Also remember, this was long before the Y2K problem was a consideration. But an easy Y2K "fix" would have been to change the macros to add/subtract 1900 from the year. At least this would have put the problem off until the year 2027. :grin:
Posted on 2003-03-18 19:59:48 by S/390
Readiosys

Perhaps i'm reading your post wrong, does that number of records have six zeros... as in four million? If thats the case, I wouldn't use Access even if my life depended on it, I just don't think its up for the task. Considering your creating a new record approximately every 7.8 seconds, not to even mentioning any other connections to the database (including your "outside" world queries), this looks like a job for a real SQL server.

Even MSDE isn't really suited for a such a task (or so Microsoft claims), since its recommended to have no more than five connections to a MSDE system at one time.

Or... maybe (and probably) i'm completely wrong :tongue:

-----
Domain
Posted on 2003-03-19 16:35:17 by Domain

The DATE type in Access is 8 bytes...


Sounds like the FILETIME structure. If it is, there are several API functions that can be used to manipulate it.
Posted on 2003-03-19 16:42:52 by Berninhell

Readiosys

Perhaps i'm reading your post wrong, does that number of records have six zeros... as in four million? If thats the case, I wouldn't use Access even if my life depended on it, I just don't think its up for the task. Considering your creating a new record approximately every 7.8 seconds, not to even mentioning any other connections to the database (including your "outside" world queries), this looks like a job for a real SQL server.

Even MSDE isn't really suited for a such a task (or so Microsoft claims), since its recommended to have no more than five connections to a MSDE system at one time.

Or... maybe (and probably) i'm completely wrong :tongue:

-----
Domain


In fact, yes, there will be millions of records in it, but it is for several years. We would like to keep at least the three or four last years inside the database to do comparisons to know how our business evolves.

If I use Access, it is because, as far as I know, the ODBC driver can be freely redistributed using MDAC. This software may be used in ten countries and our budget priority is not in spending thousands of Euros in licenses of SQL Server and hiring experts to maintain such a system.
As for database traffic, it will be relatively low : never more than three users at the same time, and most of the time only one and not requesting much data.

Do you know any database driver that is better than Access but relatively cheap ?

Thanks.
Posted on 2003-03-20 03:37:53 by JCP
mysql, postgresql, firebird, ocelot sql, berkeley db... there's quite a list :)
Posted on 2003-03-20 04:07:52 by Hiroshimator


Do you know any database driver that is better than Access but relatively cheap ?

Thanks.



definately use mysql, its free and gpl ? and developing alot..., much better than access.

*DEFINATELY* do not use access if you plan on having over 1 million records ( even over 100,000 )...
Posted on 2003-03-20 04:43:13 by abc123
Thanks for the advice, but mySQL is indeed GPLed and for a software that will be used in enterprise... it is not really the best license.

By the way, does it makes a difference if the software is not sold ? This software will not be sold, it is for internal use... does it changes something ? Can we consider it is a "commercial" project since it is not making money ? It is quite like a "personal use"... isn't it ?
Posted on 2003-03-20 06:36:42 by JCP
you do not need to distribute sourcecode when it's for internal use.

but mysql might not be the best solution. You'll need to go over a checklist of features that your database will need to function for your application to determine which one you'll need.
Posted on 2003-03-20 07:05:33 by Hiroshimator
Hrmm, well I'm totally ignorant when it comes to legalise, so I haven't a clue about licensing concerns with mySQL. I've had very brief experience with it in the past however, and it seems to perform very well, although it is (or maybe was since the last time I used it), missing some of the features found in the commerical SQL products ( Microsoft SQL 2000/Oracle 8 ).

You may be able to get away with MSDE, I just don't know how well it performs as the database becomes larger... ( although, it seems that the files it uses are bit by bit identical to those found in SQL 2000 ).

You might also check out postgreSQL, it is OSS software, but falls under the BSD license, which has far fewer restrictions. Unfortunately, thats all I know about it....

-----
Domain
Posted on 2003-03-20 13:55:36 by Domain