MS Access delete item from table not working 2

[coolcarls]

Good Afternoon:
I have a problem with our database software that has stopped us from working for the last week. I know nothing abobout Access either. So here goes...
We have 3 parts to our software. 1. is the application that controls our data 2. is the actual database (the data) and 3. is the ini file (directs the application to the folder where the data is kept)
In the database there are a bunch of Tables, one of these tables called tblItems has the problem (I think).
If I open the Table (by opening the database, not using the application)and scroll down to entry #2158 it says "error" in every cell accross that row. If I click on any cell that says error a message/error box pops up with "Invalid Argument". I can't get rid of this row. I want to right click on the whole row and choose delete but the error box comes up every time. Now, even if it didn't come up and I chose delete record, I think another error would pop up telling me it can't be deleted due to other relationships, maybe...I don't know.
So, I tried going to database utilities/compact and repair database and all that did is remove some of the relationships with other tables and the deleted the error. i cannot seem to duplicate the relationships after that fact. The original relationships were "one-to-many" and had the "enforce referential integrity" check box checked. so I just made a relationship without that to all the original places, but that just doesn't work either.
HELP!!!Please.
Anyone have any suggestions/first steps?
Many thanks.
Carl

 

[PHV]

Anyone have any suggestions/first steps
What about restarting from a valid recent backup ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[coolcarls]

I we hadn't backed up in months.

 

[Bong]

I'm not sure sure what to do in VBA (or whether you'd want to given the one-time nature of your predicament), but if your problem is associated with a single row (record), can you copy all the records from 1 to just before the bad record to, say, a spreadsheet? Then, copy from just after the bad record to the end into the same spreadsheet? Then, import (or just assign) the spreadsheet to your database?

_________________
Bob Rashkin

 

[PHV]

You may try the following:
Create a make table query based on tblItems excluding entry #2158.
Recreate the entry #2158 in the new table.
Rename the corrupted table.
Rename the newly created table as tblItems.
Recreate the RelationShips.

Did I say to test the above in a copy of the DB ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[coolcarls]

It won't let me paste, the same error message comes up because I'm trying to mess with that same row. I was wondering if there is some sort of macro that could be written to delete record set #2158 from tblItem prior to opening the table? I could place it in the application and run it from the switchboard?? Any suggestions??
Thanks for even reading all of my garbage!!!
Carl

 

[coolcarls]

PHV, thanks for the answer, it even sounds like it may work. But I have no idea how to make a query or do any of the other things you suggested. Any way to speel it out for the layman?

 

[PHV]

Have you tried my suggestion ?
Do you know the PrimaryKey value of record #2158 ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[coolcarls]

PHV,
I have tried your sugestion to some extent. The problem is that I can't seem to creat the same relationships. The original relationships have that check box checked that says "enforce referential integrity" and also the type of relationship says "one-to-many", if I check the box another warning box comes up saying it can't do that for some reason, I can only get it to say "intermediate". I recreated the table and the relationships (without the check box) but it doesn't work.
Arrggghhh
Thanks for the help.
Any tips on the querie thing? Understand that I know nothing about access, so I may not be able to win this war.
Thanks again

 

[PHV]

Did you recrate all the index in the recrated table ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[coolcarls]

Sorry PHV, I forgot to address one of your questions.
How would I find the primary key?

 

[PHV]

Open the original table in design view and display the index window.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[coolcarls]

What do you mean "all the index"? I renamed the tblItem to tblItem1 and then made a new blank table called tblItem, then copied every row above the error and pasted it into the new table, the all of the rows below the error and pasted them in the new table. The had issues creating relationships.
I don't know,
Thanks PHV

 

[coolcarls]

Primay key is "idItem"

This may start to make sense soon!

 

[PHV]

Seems we post at the same time ...
So, to be sure:
Open tblItem1 in Design View, display the index window.
Open tblItem in Design View and create all the previously displayed index.
Close the tblItem1 table.
Save the tblItem table.
Open tblItem in DataSheet view, create the missing row, close and save it.
Try to rebuild the RelationShips.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[coolcarls]

Thanks a million
Carl