Access 97 tables corrupted after conversion to 2003

"When I try to open and convert using Access 2003, certain"

Be sure .. before you try to convert, that your A97 mdb will Compile.  And of course, do a Compact and Repair prior to converting.

mx

> "Both their field names and types get changed"

Wow! Never experienced that! Make sure you have installed SP2 ("help / about"), and try to import the tables instead of converting the database (new blank database and "file / get external data / import...").

Decompile would not help in this case, as the BE probably doesn't have any VB code anyway. You seem to have data corruption, not VB corruption.

If you sill have a computer with A97, try mx's other suggestions, though (on a scratch copy, as stated).

(°v°)

I think I tried everything in the first post but to no avail.  I previously had in fact done  a compile but through the module editor.  I also checked references.  Step 4 "Open the mdb and do a Compact and Repair from the ..." is incomplete statement .  

I suggested Decompile because of " Access 97 database (front and back end).
FE & BE ...

mx

Opps ... I need to fix # 4 ... thanks.

mx

1) Compact and Repair the MDB:
Open the MDB, then from the menu >>Tools>>Database Utilities>>Compact and Repair ...

4) Open the mdb and do a Compact and Repair (#1 above).

I try to spell out compact and repair, because people do not know what that is or how to do it.

mx

Thanks for the replies and your suggestions mx.  I've done plenty of compiling and c&r but still data corruption on conversion, and accessing the data file with 2003.  Consider this:  
1.  I have installed both access 97 and 2003 on one machine.
2.  I have copied the front end (quotes.mdb) and the back end  (quotesdata.mdb) into a temporary directory.  
3.  If I open the quotesdata w. 97, and then the table "quotestemplates" there are three fields: id (number) description (text) and Notes (memo).  All entries display fine.  
4.  If I open the same file i.e. the backend only - quotesdata, with access 2003, i get the message that I cannot make changes because it was created in an earlier version, but then opens.  However, when I go to open the same table "quotestemplates" the field names are now: Gen_notes (number), s_generation (number), and s_GUID  ( autonumber), in that order, instead of ID, Description, and Notes.  And the data in the fields themselves of course is all wrong.  Changed to numbers.   eg. {1265F1-123-1264F25-156968F441} instead of just 1 (ID) or 48 instead of "gravel template"
    I could just try to re-make the table but wanted to be able to access the data end from either computers with access 97 or with those with 2003.  According to microsoft, this shouldn't be a problem as long as they each have their own front end.  

I hope these specifics will help towards furthering a solution.  I have done a few databases in access but am self taught and not your level so maybe you could tell me, does converting, and compiling not just affect the front end not a simple table with just data?

I tried opening  and converting using 2000 but same results.  Tried to upload back end but limit of 4mb and this is almost 8 even with a bunch of the data removed and zipped.  Is there a way to get you a file this size or do I keep trying to trim it down?

I assume you did a C&R and then zipped it up?   I guess you will have to trim it down.

mx

DJMTEK:
You are not seeing that GUID in the A97 mdb, right ?  Only after conversion?

mx

I can't get the file smaller than 4 meg but you can download a zipped copy from this link:  http://z33.zupload.com/download.php?file=getfile&filepath=33649.  Let me know if you want the front end as well.

MX
I don't see the GUID in the one quotetemplates table when brought up on A97, but I did notice it in one of the other tables.  Not sure if this is only on the copy I brought home with me this weekend or if it is the same on the server.  Will check on Monday.  Also, trying to get through what harfang posted.  He may be on to something.

So ... maybe the source data does have all of this?

mx

So ... maybe the source data does have all of this?

Not in the quotetemplate table.  Further, the sales guys have no problem with the any of database in a97, just when we introduced 2003.  Its possible some of the data became corrupted in the other tables when I took it off the server and started playing with it.  But again, I will check on Monday.  In the meantime, any explanation where the quotetmplate table is getting its corrupted data?  

Appreciate all the time you are giving this!  

harfang
I thought I had compacted the database before posting it (in 97)?   Sorry about that.  I don't have 2002.  The database seems to work seamlessly for the guys using a97.  I'm very curious if the original is the same.  Anyway, will get to work on your suggestions and post back.

Thanks a million!

Sorry ... I will check this out later tonight ... I have to drive a couple of hours ... back later ... I have the download.

mx

About compacting: it fails on my version of A97, but the compacted db is created in the same folder. It just isn't renamed due to the error at the end of the process. I guess this is why you didn't see it. No big deal, anyway, as you found a way to post the file.
(°v°)

mx, you links database is impressive! (^v°)

I checked the database on the server today.  The same, (errors in tables) except that there was no "products backup" table .  Interesting.  Talked to one of the sales guys who uses it every day and says they never have any problem with it.  Strange.  Anyway,  much reading to do here and stuff to try.  Thanks so much to bothe of you.  Just got home, so give me a bit to go through this.  

MSysTombstone ... certainly one of the more creative names ... it's for holding deleted records.

mx

Just a note to let everyone know I am still on this.  My apologies for the time lapse.  I need two of me.

First of all thanks again for the links.  Read through all and several underlying links.  Tried conversion tool but it failed saying this was not the "Design Master".  When I get back, I will do a search of the server to see if the design master exists somewhere.  In the meantime am trying to rebuild from the ground up and will then try importing as suggested by harfang.  

Won't be able to post until Saturday.  

I need four of me, lol ... no problem.

mx

I've been working on the idea of Creating a new database and tables.  Initially I ran into the problem of the new database tables did not have autonumber fields, just replication number.  I discovered that I had to open a non-replicated database prior to opening my new database to get the autonumber field to show (and not a replication field).  Interesting.

With that, I followed harfang's instructions to create tables in my new database of "Quotesheader" and QuotesdetailCharges", getting rid of all the replication fields.  The ID field QuotesHeader links to QuoteID in QuotesDetailsCharges in the old database.  I'ts field typ is autonumber-replication number in QH and number-replication number in the QDC table.  So I created an autonumber field in the QuotesHeader table (QH).  Then I created new ID field (longInteger type) in the new QDC table.  Then I linked the two new tables(without referential integrity) in the new database.  So now the question:  How do I assign the newly created ID in "QuotesHeader" table to the newly created ID in "QuotesDetailsCharges" table?   I need to compare the replication id in Quotesheader to the Old ID (which also contains a binary number) in the "QuotesDetailscharges" table and insert the new ID from the Quotesheader  table into the new ID field of the QuotesDetailscharges table.

Re my own post.  Stupid question.  Just use an update query.  Must be time for a break.

I'll drink to that :-)

mx

Definitely time for the NASCAR race ... rain has stopped.

mx

Everything seemed to go well with transferring tables into new database.  Got relationships  done.  Then opened the original front end and linked to the new back end.  Database seems to work fine.  Tried to compile in preparation for a conversion and got compile error: "Variable not defined".  How do I define this variable?  

Why do I have the feeling you are going to need more info than this?

LOL.

Perhaps not... You have basically changed your tables, nothing else. Now, consider this piece of VB code:

    Something = 42

'Something' can be many things (a property, a variable, a control on a form, etc.), but if it isn't found in the "name space", you get the message "Variable not defined".

In fact, I suspect strongly that 'Something' is on of the fields you deleted. In the code behind a form, both the form's controls and the recordset's fields are available like that, often prefixed with 'Me.', but that isn't required (it's only more readable in some cases).

Have you found *which* 'Something' VB is missing, or do you need help to find it?

(°v°)

This is where it takes me when I click "ok" -
Private Sub Form_Load()
    Title.Caption = ProductName
    TitleShadow.Caption = ProductName
    SatelliteLabel.Visible = (SATELLITE = 1)
End Sub
and the word SATELLITE is highlighted.  When I close the page, a "Nucleus Splash" form pops up, containing the company that wrot the database (and is now defunct).  Looking at the properties, there is an event on load which takes me back to the above subroutine.  Since I am working with a copy, I decided to just delete the subroutine, then tested the database.  It seemed to work fine so tried a compile all.  It compiled and saved without diffiuculty.  I then opened a copy of access 2000 and tried to convert.  It did so without errors but when I opend the database, clicked "build new quote" i got essentially a blank table with some numbers.  Went to the quotes template table and there be the replica fields again!  How could I get this from a non replicated database?  I reconstructed the back end from scratch - almost.  I did drag some tables over if they didn't have the replication fields.  Could they have been hidden from me.  Other tables showed them.  Help!

> "SatelliteLabel.Visible = (SATELLITE = 1)"

So SATELLITE is now missing. By looking at the label, you can try to guess what it was or what it is supposed to be.

> "... the company that wrote the database ..."

So you have in fact little idea on what goes on in the database, right? You don't know if the application was *meant* for a replica or what table definitions might get tweaked by it.

> "... I opend the database, clicked 'build new quote' ..."

You can't really understand what's happening by using a broken and now unreliable interface. Instead, you should step though the code behind the button, and try to understand what the programmer(s) *intended* for that button.

> "Could they have been hidden from me."

Yes, they could. I don't know how you did the import and whether you were able to run all make-table queries correctly. From your comments, you seem to have used too much drag-n-drop instead of going through the pain of creating one query for each table...


All in all, I suggest a rewrite of the application. Find a good Access specialist and have him/her rewrite the whole thing. Transfer only the data, not any part of the application. Working with code from a "defunct" company will never be reliable, even if you manage to make it work superficially. Meanwhile, use the working A97 version for production.

Cheers!
(°v°)

I am re-importing ALL the tables and trying again.  
- So you have in fact little idea on what goes on in the database, right? You don't know if the application was *meant* for a replica or what table definitions might get tweaked by it.

I know only what I have analyzed in the database.  I was not around when the original database was created so it could have been "meant" to be a replica.

- You can't really understand what's happening by using a broken and now unreliable interface.

Its not the button that is the problem here.  Its the data that is in the table.  Bringing up the table alone, wihtout using the button, displays the replication fields instead of the fields I exported.  I thought I had done a "create table querry" on this table but when I tried converting, all these replciation fields appeared again.  

I realize this has gone on a while but I'm not willing to throw in the towel yet. I will give it one more go and not drag/drop any tables this time.   If it doesn't work I will assume this replicated table cannot be un-replicated unless anyone has any other suggestins.  Thanks to bothe of you for your patience.

The solution I was after was never achieved (successful upgrade of existing database) but much insight was shed on the problem by both harfang and databaseMX so I am splitting the points.  My sincere thanks to both of you.

you are very welcome ...
thank you ...

mx