Corrupt Database 1

[Viv1]

I created a database in Access 2000 and had to convert it to 1997 so that other people could use it straight away. We have entered in all the data (several thousand records) and now I need to make some changes as we've discovered missing/too short fields etc. I have tried converting it back to 2000 as I can't make changes whilst it is in 1997 but the Convert Database option is greyed out. I sent it to someone else to check and they say that they get an error when they open it saying it is corrupt. I am not familiar with the problems using Access and am not sure what to do next. We can use it fine with Access 2000 now and to use it doesn't show any problems but obviously something is wrong for the Convert option to not be available.

Any ideas much appreciated!

Thank you, Viv

 

[hymn]

If you have a working db in 2000 why not take a backup and then convert that one to 97 and test

Hope this helps
Hymn

 

[Viv1]

I have made a copy of it and saved it elsewhere but the option is still missing, is this what you meant?

Thanks, Viv

 

[hymn]

try
1. compact and repair
2. Open a new db and import all tables, Queries, forms etc into the new db and see if you can then convert it to 97

Hope this helps
Hymn

 

[Viv1]

Hi,

I have done this and it appears the new database is in 2000 without me having to convert it. Everything imported ok and all the VBA is there so fingers crossed this solves my problem.

Many Thanks!

 

[hymn]

Pleasure

Hope this helps
Hymn

 

[willir]

Viv

First, consider splitting the data from the application component if you have not done so - front end (application / user interface - forms, reports, queries, modules) and back end (data tables). Although you spent a lot of time in development, the integrity data is the most important.

This way...
- If you do have corruption, you can isolate it to the Front or Back end. If you are working with different versions of Access, I would not be surprised if the Application / Front End was problematic.
- I have not tested this, but I believe you can use either Access 2000 or 97 to access the same back end database (Leave the back end in Access 97 format until all end users have updated)
- You can develop your application without impacting users and the data. And then instead of importing and cleaning up afterwards, you can just replace the old fornt end with the new front end.

...Moving on
Have you tried creating a new blank database, and then importing the tables, forms, etc into the new database?

Things to expect when moving from Access 97 to 2000.
My biggest issue here is that DAO is the default for Access 97, and ADO is the default for Access 2000 and newer. Consequently, if you have code that uses DAO, you have to
- add the DAO reference. ALT-F11, efrom the menu, "Tools" -> "References", and add Microsoft DAO 3.6 Object Library
- Specifically reference ADO or DAO objects in your code where applicable. Eg.
Dim dbs as Database, rst as Recordset 'generic
Dim dbs as DAO.Database, rst as DAO.Recordset 'DAO
Dim dbs as ADO.Database, rst as ADO.Recordset 'ADO

- Access 2000 perfers Unicode compression for memo fields. Conversion back to 97 may result in lost data.
- MDAC version and to a lesser concern, DCOM, JetEngine


Interesting links...
How to Convert an Access 2000 Database Back to Access 97
Sharing Access Databases Across Language Versions
Troubleshooting Your Upgrade to Access 2000
Using an Access Database with Multiple Versions
ACC2000: Conversion White Paper Available in Download Center

Richard