Will Access database crash?

[d-u]

I'm having tensions with my access database as it has already grown to around 60mb, it records lots of pictures.The system I programmed automatically back-ups the database when it exits. It also compacts and repairs the database for any errors at startup and when exiting...

I'm just wondering what are the tendencies that my database would crash (be corrupted or sort of...) and what maybe the possible factors that would cause the database from crashing....

Please help.....

[erickwidya]

see this Access Limitation

just a suggestion..why don't u store the path to the picture rather than save all of it..

[cjard]

i know of an access database concurrently used by 20+ users, and holds in excess of a million records. i shudder to think what filesize it is.. i know we have problems with ours when it grows to around 60mb. one of our competitors has a product based on access, and the stock database is 400mb. I'm impressed, especially with the speed of lookup; only a few seconds on a pentium 133 with 32mb of ram

[erickwidya]

cjard do u know by any chance what method that they used for lookup speed that u mention about?

[d-u]

Am I right that converting a .bmp file to a .jpg file will have a reduction in filesize? Maybe I can convert those .bmp's to .jpg before saving them into the database. I'm also thinking of making a program that will transfer the data in my Access database into an SQL Server database, do you think it will take a lot of work in modifying my application which is employing DAO to connect to the Access database? By the way, can a DAO connect to an SQL Server? I'm a newbie to SQL Server.

[Dmorley]

Converting to jpg or gif will reduce filesize, I think bmp is one of the largest file types.

You should use ADO with the SQLOLEDB provider to connect to a SQL Server instance, DAO is tied in to Access Jet.OLEDB provider.

Its takes a bit of time to convert your code but it's worth it!

[George1111]

Size of Database

60MB is a baby size database for Access, especially if you are storing picture files. The main reason Access gets upset is when you are adding lots of new records without compacting (but you are doing this already)
As a precaution, why don't you keep a backup of your database file as part of the compacting procedure
a) Copy the database to DbaseName.bak (delete old copy first)
b) Now compact the database
This ensures that if you have problems during the compacting procedure, you still have a copy to go back to

Database Corruptions

I have problems from time to time with a database being corrupted (it is in Access97 format)
What I do is try to repair it - no chance !
I then convert it to Access 2000 format - repair it
Then convert back to Access97 format - all is fixed!

Speed of Access

SLOWEST METHOD
Using SQL commands like Select * from xxx where aaaa = nnn is the slowest method of accessing your database
Furthermore, the notion that if you say select field1,field2,field3 from xxx where aaaa = nnn is going to be faster, is complete rubbish
I have run this specific test where I have about 250 fields in a table and have found that it matters not how many fields you access at one time
(It may make a difference with SQL Server, but we are talking about an Access database)
Also, whether the fields are indexed or not also makes no difference with the select statement (It may make a difference with SQL Server, but we are talking about an Access database)

FASTER
FindFirst logic works but is not the fastest as it does not use an index

FASTEST
The SEEK command is amazingly fast - probably 10-20 times faster that the other two methods (it uses an Index)


That's my experience - hope that is helpful

[cjard]

does that rule about access still apply? i thought that SEEK stopped being faster than SELECT at around Jet 3.5..

[cjard]

Converting to jpg or gif will reduce filesize, I think bmp is one of the largest file types.

for the information it conveys, it possibly is.. but some bitmaps employ RLE, so it is not quite as big as uncompressed text



what i actually want to know is, if the op is concerned about database size, then why does he put the picture inside the db at all? better to declare a text field of size 255 and store the picture on disk, putting the PATH to the picture in the db..

storing pictures in a db is just a silly idea.. what happens when the database goes corrupt? bye bye pictures. if you have them all on disk as separate files.. well, at least you have the pictures!

on england, we call it "putting all your eggs in one basket"

[Dmorley]

does that rule about access still apply? i thought that SEEK stopped being faster than SELECT at around Jet 3.5..

I agree with that, have recently converted an old system using DAO \ seek to ADO SELECT statements and have really noticed the performance benefits.

[d-u]

The pictures are part of the records that I have to save, they are images that has been analysed/marked, will it be really better if I just save their paths? Actually I have thought of doing that but my application will really produce lots of figures and there is a tendency that those figures might accidentally be 'lost' don't you think? If they are saved in the database backing them up is a lot easier I think or am I wrong with my thinking?

And anybody who could provide me a simple example of a program that connects to an SQL Server? I would really appreciate it if you could provide an example in Stand-Alone and Client-Server environment. I have already learned how to connect to an SQL Server via self-study but perhaps it might be better if I'll have an example from you gurus who are experts in SQL Server. A million thanks in advance....

[George1111]

The more information stored in a database = the more chance of things going wrong.
If you can separate your images out then your database should become a nice little tidy database with minimul data corruption possibility (that was your initial concern)
Also, distributing images is a lot easier I would have thought rather than having to get them in and out of a database - I would be saving the images separately , one by one in a directory, particularly since you say that they need to be marked.
Also being separate you would have more flexibility with image format, I would have thought (bmp, jpg, tif or gif)
Changing the image inside the database (Read, Remove,Insert New) has got to be causing Access a lot of concerns (with image handling) and also will cause the need to reorganise the database frequently
Having the image separate (and just storing the path) is a much more reliable way to go (not much is changing in the database)

If an image corrupts then you may lose one image
Previously, if an image corrrupts you may lose the whole database

I work on the basis that Access will corrupt - guaranteed - just don't know when it might happen!

I therefore keep data that changes very frequently in a different database to a more static data - in this way, if I lose anything, it will not lose everything

One example is when you are building recordsets as temporary work areas then use a temporary working database to do this

Have your program go through the motions of

1 - Create a Working Database (bypass if already exists)
2 - Create the working Recordset(s)
3 - Use them
4 - Delete Recordsets at end (and possibly delete the working database also)

This has saved me so many times with database corruptions

[cjard]

cjard do u know by any chance what method that they used for lookup speed that u mention about?

putting everything interesting into one massively long but incredibly thin table with a very unique key, basically.. and that is linked to a very very large, detailed table..

the lookup of the key is incredibly quick, and hence pulling the related row out of the huge table is easy. other products ive seen, by comparison, rely on several large tables, with indexes. searching these tables seems to take significant amounts of time, and the real criminal move is one where several huge tables are joined merely so that the product descriptions can be searched in one query..

[cjard]

The pictures are part of the records that I have to save

not really.. in a normalised database you split the storage of information up anyway, so what is the actual conceptual difference between normalising a database and putting pictures on disk linked with a unique code?

the relation of a person to a picture is one to many.. one person may have many pictures, but each picture is only of one person.. that's a candidate for a relationship if there ever was one, and you might as well reduce database bloat my not storing the pictures inside the mdb file..
its a moot point in a way, because everything in a computer is a database; the hard disk is a huge, indexed collection of data earmarked for easy retrieval..

the only thing you find is that hard disks and filesystems usually do a better job of storing data than an access database


interstingly, the new filesystem from microsoft is based on sql server, and effectively turns your whole hard disk into a sql server database.. from what i'm given to understand, that is..
not that this is anything revolutionary if you think about the basic role of filesystems through the ages, but it's an interesting realisation of a long standing alternate perception

[jp140768]

I have an Access database here, which has over a million records in it, its currently 1.4 gig in size, and have not had a problem with it crashing. I have found that the SEEK definitly works faster than the select statement, even if all fields in the Where statement are within an index.

I know this database should be a SQL / Oracle one at this stage, but getting the data into a new database could be tricky, due to referential integrity. All my records are keyed by an auto number, and tables use the auto number field as a reference to another table, however as records get deleted, it leaves gaps / holes in the sequence number, which when I try and write to another table starts using these missing numbers.

Using a server side cursor is faster than using a client side one - which is interesting, since I started reading about ADO.Net and the book seemed to imply that it would mainly only provide support for a client side cursor.