dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 28

Thread: Will Access database crash?

  1. #1
    Join Date
    Sep 2004
    Posts
    292

    Will Access database crash?

    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.....

  2. #2
    Join Date
    Jul 2004
    Location
    Jakarta, Indonesia
    Posts
    596

    Re: Will Access database crash?

    see this Access Limitation

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

    1st NF - a table should not contain repeating groups.
    2nd NF - any fields that do not depend fully on the primary key should be moved to another table.
    3rd NF - there should be no dependency between non key fields in same table.
    - E. Petroutsos -


    eRiCk

    A collection of "Laku-abis" Ebook, Permanent Residence

    Access Reserved Words, a Classic Form Bug, Access Limitation, Know run Process and the Lock they hold in, Logging User Activity in MSSQL

  3. #3
    Join Date
    Oct 2003
    Location
    .NET2.0 / VS2005 Developer
    Posts
    7,104

    Re: Will Access database crash?

    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
    "it's a fax from your dog, Mr Dansworth. It looks like your cat" - Gary Larson...DW1: Data Walkthroughs 1.1...DW2: Data Walkthroughs 2.0...DDS: The DataSet Designer Surface...ANO: ADO.NET2 Orientation...DAN: Deeper ADO.NET...DNU...PQ

  4. #4
    Join Date
    Jul 2004
    Location
    Jakarta, Indonesia
    Posts
    596

    Re: Will Access database crash?

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

    1st NF - a table should not contain repeating groups.
    2nd NF - any fields that do not depend fully on the primary key should be moved to another table.
    3rd NF - there should be no dependency between non key fields in same table.
    - E. Petroutsos -


    eRiCk

    A collection of "Laku-abis" Ebook, Permanent Residence

    Access Reserved Words, a Classic Form Bug, Access Limitation, Know run Process and the Lock they hold in, Logging User Activity in MSSQL

  5. #5
    Join Date
    Sep 2004
    Posts
    292

    Re: Will Access database crash?

    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.

  6. #6
    Join Date
    Aug 2003
    Location
    London
    Posts
    515

    Re: Will Access database crash?

    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!

  7. #7
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    1,883

    Re: Will Access database crash?

    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

  8. #8
    Join Date
    Oct 2003
    Location
    .NET2.0 / VS2005 Developer
    Posts
    7,104

    Re: Will Access database crash?

    does that rule about access still apply? i thought that SEEK stopped being faster than SELECT at around Jet 3.5..
    "it's a fax from your dog, Mr Dansworth. It looks like your cat" - Gary Larson...DW1: Data Walkthroughs 1.1...DW2: Data Walkthroughs 2.0...DDS: The DataSet Designer Surface...ANO: ADO.NET2 Orientation...DAN: Deeper ADO.NET...DNU...PQ

  9. #9
    Join Date
    Oct 2003
    Location
    .NET2.0 / VS2005 Developer
    Posts
    7,104

    Re: Will Access database crash?

    Quote Originally Posted by Dmorley
    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"
    "it's a fax from your dog, Mr Dansworth. It looks like your cat" - Gary Larson...DW1: Data Walkthroughs 1.1...DW2: Data Walkthroughs 2.0...DDS: The DataSet Designer Surface...ANO: ADO.NET2 Orientation...DAN: Deeper ADO.NET...DNU...PQ

  10. #10
    Join Date
    Aug 2003
    Location
    London
    Posts
    515

    Re: Will Access database crash?

    Quote Originally Posted by cjard
    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.

  11. #11
    Join Date
    Sep 2004
    Posts
    292

    Re: Will Access database crash?

    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....

  12. #12
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    1,883

    Re: Will Access database crash?

    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

  13. #13
    Join Date
    Oct 2003
    Location
    .NET2.0 / VS2005 Developer
    Posts
    7,104

    Post Re: Will Access database crash?

    Quote Originally Posted by erickwidya
    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..
    "it's a fax from your dog, Mr Dansworth. It looks like your cat" - Gary Larson...DW1: Data Walkthroughs 1.1...DW2: Data Walkthroughs 2.0...DDS: The DataSet Designer Surface...ANO: ADO.NET2 Orientation...DAN: Deeper ADO.NET...DNU...PQ

  14. #14
    Join Date
    Oct 2003
    Location
    .NET2.0 / VS2005 Developer
    Posts
    7,104

    Re: Will Access database crash?

    Quote Originally Posted by d-u
    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
    "it's a fax from your dog, Mr Dansworth. It looks like your cat" - Gary Larson...DW1: Data Walkthroughs 1.1...DW2: Data Walkthroughs 2.0...DDS: The DataSet Designer Surface...ANO: ADO.NET2 Orientation...DAN: Deeper ADO.NET...DNU...PQ

  15. #15
    Join Date
    Jun 2002
    Location
    Clane, Ireland
    Posts
    766

    Re: Will Access database crash?

    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.
    JP

    Please remember to rate all postings.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)