• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

Can't find rows in MS Access 2007

We have MS Access 2007.  A couple days back we noticed that a series of rows or product can't be found.

We would like to know if there is a way to certified that the products has been deleted?

Or is there any other way to search for the products?
0
rayluvs
Asked:
rayluvs
5 Solutions
 
rayluvsAuthor Commented:
To be more specific, we have an inventory table that all of a sudden specific products doesn't exist.

We are not expert in MS Access so we would like to troubleshoot this problem to determine if the items really doesn't exist in the Access DB.

We have a VB apps that works with the Access DB.  When rhe apps indicated it couldn't find the items, we search directly on Access table to see if exist by clicking in the table item, right-clicking a row and selecting the filtering option as to "equals to", "between", etc. and the item just don't exist.

Since we are not expert in Access and since we have been told that MS Access is not as reliable as MS SQL, we need expert advice and guidance on this matter.

Thanx
0
 
rayluvsAuthor Commented:
FYI: we ran the repair option by running compact and repair
0
 
Christopher Raymond MendozaCommented:
Do you have a backup of the file? Hopefully the it still contains the records you are looking for.

Way back when we were using MS Access, whenever we encounter problems similar to yours, we immediately make a copy of the (corrupted) file and use the repair tools built into MS Access itself.

We also check the backups to have an idea when the problem occurred.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<We would like to know if there is a way to certified that the products has been deleted?>

Access does not track these sorts of things, unless you have added customized code that could do it for you (and that doesn't sound likely in this case). As indicated earlier, the only way to confirm would be to review a recent backup.

However, depending on your specific application it's entirely possible that you're looking in the wrong places. Access is a relational system, and in some cases it's not very clear to a novice exactly how the data is stored and "related" - so you could expect to find your products in a table named "Products", but the system might be storing them in a table named "Inventory", or something like that.
0
 
rayluvsAuthor Commented:
We do have a backup; we'll check it out

Besides compact & repair, what other MS tools is there in Access?

We thought about the possibility that the items be in another table.

In MS SQL we have cnstructed a script (with EE assistance) that would sear the entire database for a specific value.  

Besides the additional MS tools asked previously, can we have a similar script un Access? or how can we search for specific data within the entire DB?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<In MS SQL we have cnstructed a script (with EE assistance) that would sear the entire database for a specific value.  >

Is your data stored in SQL Server? If so, then you may be able to review the logs and see if those values were deleted.

<Besides compact & repair, what other MS tools is there in Access?>

I'm not sure what you mean .... C&R is pretty much the only real tool in Access that works to "fix" the database, but there are some other things that can be done (like Decompile, for instance) that can affect the code base.

<Besides the additional MS tools asked previously, can we have a similar script un Access? or how can we search for specific data within the entire DB? >

You can search an Access database for a specific item, but it's not nearly as simple as it is in SQL Server. Basically, you must iterate through each table, and determine which Fields in those tables are appropriate (you wouldn't search a Numeric field for a value of "Scott", after all), and then buildup SQL to search those tables and return the values to you thorugh some interface.





0
 
rayluvsAuthor Commented:
So basically there is no script or internal opion for MS Access to search for a specific data; we have to do the search ourselves table by table?

You mention that there are some other things that can be done like Decompile; how does decompile help to recover or search the tables for lost items?
0
 
peter57rCommented:
"...we have been told that MS Access is not as reliable as MS SQL..."

that is true in strict terms but it does not meant that Access is UN-reliable.
Thousands of us have made our living building Access apps over many years. If it was not a reliable product that would just not be possible.

Having said that it is possible to encounter corruption due to bad network connections, or user's switching off in the middle of an operation and of course it possible to design bad applications which process data incorrectly.

But as said by others, there is no tool which searches all fields in all tables.

You can search all fields in a single table just by using the Find (binoculars) with the table open.
0
 
rayluvsAuthor Commented:
Thank you.

What about Decompile? Can it be used to help to recover or search the tables for lost items?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
No, Decompile cannot be used for that. Your request for "other utlities" was not clear, and therefore I assumed you were asking for general "other utilities". There are no builtin utilities that will recover lost data, and in fact using Compact and Repair often renders other data recovery processes invalid. As I stated earlier, there are no transaction logs or other methods internal to Access that track what has happened in the database (unlike larger database engines).

But you have stated that you have "constructed a script" in MS SQL, so my question remains: Are you storing the data in Access tables, or are you storing the data in SQL Server tables?

"So basically there is no script or internal opion for MS Access to search for a specific data; we have to do the search ourselves table by table?"

That is correct. You would need to build this functionality into your database, or perhaps use a 3rd party utility (like Rick Fisher's Find and Replace: www.rickworld.com) to locate this information.

Access was intended to be a desktop-type database product, and as such does not have many of the features of enterprise-level database engines like SQL Server.
0
 
rayluvsAuthor Commented:
Thank you very much for your info.
0
 
rayluvsAuthor Commented:
Thanx
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now