Access database problem

[lomaca]

Question to Ananda,
I have been using Access 2007 for a while now after many years of Foxpro, but lately I had so many problems with "database not recognised" errors that I'm despairing.
Only constant backups and keeping of the raw data in separate files is saving my bacon, I know you are an expert on Access, can you tell me what's going on or could you in all conscience say,
use an other database?

I'm not doing anything unusual, same as always.

Thanks

[anandasim]

Question to Ananda,
I have been using Access 2007 for a while now after many years of Foxpro, but lately I had so many problems with "database not recognised" errors that I'm despairing.
Only constant backups and keeping of the raw data in separate files is saving my bacon, I know you are an expert on Access, can you tell me what's going on or could you in all conscience say,
use an other database?

I'm not doing anything unusual, same as always.

Thanks

Hi Iomaca,

Apologies, I have been distracted with photography and work, on Access. I use both Access 2010, 2007 and previously 2003. I know the quirks when a file is used between different versions but overall, am not seeing your issue.

Can you give me details of what is happening?

1. Are you switching between Access versions to open the file?

2. Are you using .accdb or .mdb. Unfortunately, although .accdb is a new file structure, Microsoft added some additional internal structures so Access 2010 can produce changes in the .accdb that Access 2007 is unhappy with. The .mdb is an old file extension although it has several generations, all with the same .mdb extension. There is a 2003, 2002, 2000, 97 .MDB

Can you say some words on when your file corrupts - what event causes that?

[lomaca]

1. Are you switching between Access versions to open the file?

No I'm not, Our main database is Oracle but I have to run Access mdb 2000 and 2007 versions because some of our clients only have MS Office, and some of them still running WIN 98SE believe it or not.

I don't seem to have any problem with the 2000 version although both databases contain exactly the same data. I keep them separate and update them separately every day.
It's the updating process (adding daily data) that is causing me trouble with the 2007 version, suddenly when the process is running I would get the message of "Can't find table , check the name "or somet like that, or the "Corrupt database" message. Usually I can run the repair utility once but if it happens again it won't fix it.
I try to keep it small by only keeping three years of data, it's the minimum I have to have. Currently the 2007 is just under 2GB, the older of course is smaller still because of the size limitation.

I only use the databases as storage, originally I wrote the front end in VB6, then updated in C# and the latest is in Perl. makes no difference which one I use, the problem seems to be with the 2007 version.

I wish I could use Mysql or similar, but the way we currently work with our clients it's just not possible.
Thank Ananda

[anandasim]

No I'm not, Our main database is Oracle but I have to run Access mdb 2000 and 2007 versions because some of our clients only have MS Office, and some of them still running WIN 98SE believe it or not.


Gee, some clients and PCs are really long in the tooth.

>I don't seem to have any problem with the 2000 version although both databases contain exactly the same data. I keep them separate and update them separately every day.

So the .mdb you use with Access 2007 is newer than 2000 era? Microsoft's Northwind 2000 .mdb (freely available) will not load without conversion in Access 2007 - so the .mdb for Access 2007 must be newer than 2000.

>It's the updating process (adding daily data) that is causing me trouble with the 2007 version, suddenly when the process is running I would get the message of "Can't find table , check the name "or somet like that, or the "Corrupt database" message.

>Usually I can run the repair utility once but if it happens again it won't fix it.

Severe corruption

>I try to keep it small by only keeping three years of data, it's the minimum I have to have. Currently the 2007 is just under 2GB, the older of course is smaller still because of the size limitation.

.mdb has always been 2Gb limit, then and now -
http://office.microsoft.com/en-us/access-help/access-specifications-HP005186808.aspx

You are walking too close to the fire. The JET engine, now called ACE works easily with 50Mb, 100Mb. Going to 1Gb and more is past the sweet spot of such a system. Remember, all temporary internal system mechanisms are within the 2Gb capacity limit. If you run an retrieval query, ACE has to create scrap data in the mdb so if you are already at 2Gb, there is no more room.

Note that the earliest Access .mdb e.g. Access 97 did not use Unicode to store text. Unicode is fatter than ASCII so the 2Gb capacity limit is now not as roomy because Access 2000 onwards store Unicode. To muddy the calculations of capacity, Unicode compression is inconsistent

http://www.wideman-one.com/gw/tech/accessjet/access2kunicode.htm

>I only use the databases as storage, originally I wrote the front end in VB6, then updated in C# and the latest is in Perl. makes no difference which one I use, the problem seems to be with the 2007 version.

Have you considered using a pair of .mdb in tandem?
http://lemingtonit.com/Services/Microsoft_Access/Access_Database_FAQs.aspx

This allows you to sail near to the 2Gb limit in the data store .mdb and have another 2Gb of system scrap space in the operating. .mdb

[lomaca]

So the .mdb you use with Access 2007 is newer than 2000 era? Microsoft's Northwind 2000 .mdb (freely available) will not load without conversion in Access 2007 - so the .mdb for Access 2007 must be newer than 2000.

I run them on two separate computers, the old one is on XP.

Have you considered using a pair of .mdb in tandem?
http://lemingtonit.com/Services/Microsoft_Access/Access_Database_FAQs.aspx

This allows you to sail near to the 2Gb limit in the data store .mdb and have another 2Gb of system scrap space in the operating. .mdb

Thanks Ananda I will try your suggestion.

Ps, yes I thought the 2GB limit played a part being very close to it now.
What are your thoughts on breaking up the database into two or even three and linking the tables? I know it would work I use it now, but would it affect the upper limit by linking?
I mean I could get away with using different methods, but while this part of our business is very important, I do not want to spend more time on it that necessary.

PPS, sorry Ananda I just read the second link provided, it answered my question.
Like the old saying, "when all else fails, read the instructions!"
Thanks for your thoughts.