Solved

Ghost in the Machine

Posted on 2013-02-05
10
207 Views
Last Modified: 2013-02-18
We have a custom MS Access 2003 DB that uses a custom importer from MS Excel 2003.  The problem that is occuring is that random errors are being created via the import process.  Attempts to duplicate only show that it's random.  Can anyone offer any suggestions as to how we may diagnose it further or create consistant errors?
0
Comment
Question by:KnowledgeWare
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
What code is involved, and what are the exact error messages?
0
 
LVL 26

Expert Comment

by:jerryb30
Comment Utility
Answer above^
Check/Change data types in a copy of the excel worksheet.
Induce/Look for null values.
When I hear 'random' values, I suspect differences between single and double values, or anything which can create binary fractions.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
if you are getting "type conversion error", this mean that a certain column have a mixture of numbers and alpha values.
when you import an excel spreadsheet, the process checks the first few rows to determine the data types for the table. If the first few rows have numeric value in a column that allows number and alpha characters, access will define the table field type as numeric even if the field is define as text data type, hence you get the "type conversion error".

possible work arounds to this problem
*import the excel file in a no existing table (tempTable)
* create an append query with the proper conversion of data type using the tempTable records to append the records to the final table
* delete tempTable

if tempTable is not possible
* if the column contains both alpha numeric values, sort the excel file using that column so that the alpha or alphanumeric values will be on the top rows.

* insert alpha characters in  an alphanumeric columns
* insert numbers in any columns define as numeric
-- after you import the excel file, delete the extra rows inserted from the table.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
In addition...

<We have a custom MS Access 2003 DB that uses a custom importer from MS Excel 2003.>
"Custom Importer"?
...Then obviously you will have to post the details of this "importer".
...Or contact he developer who created it...

"random errors are being created"
    "Random errors"?
...again, can you be a bit more specific...? (Error number/description, ...etc)

You also did not provide any details on your Access environment
What is being imported?
Under what circumstances?
Does this happen on all machines or only one machine
...etc

Witt no supporting info on this it is difficult to propose anything targeted.
Could be anything from the source file to Networking issues, or Service Packs, ......etc


JeffCoachman
0
 

Author Comment

by:KnowledgeWare
Comment Utility
Let me see if I can address the questions...
<mbizup>
The code is VBA, and was written in-house several years ago, by a programmer that is no longer with the company.  The way that the code works, is it takes the spreadsheet data row by row, column by column, and imports into the correct fields in the database.  If the code hits a row that has data that it cannot interpret for any reason, it returns "code 10" which simply means that the data could not be imported, with no further explanation.

<jerryb30>
Following your suggestion, we have tried verifying the data types in the Excel spreadsheet, and looking for obvious bad data.  While the data set is showing fewer errors, we are still getting large amounts of errors.  For example, there are values being imported, that simply do not appear in the spreadsheet.  Almost all of the problems are related to string fields and date fields.  There are no single or doubles in the value set.

<capricorn1>
We have checked all of the data to be imported, and we are not getting any type conversion errors.

<boag2000>
The problems are happening on several machines, on unrelated networks, with different environments.

To try and further explain, we have a custom application that we have created.  It is an MS Access 2003 front-end application with an MS Access 2003 back-end database.  The client is able to import data into the database from an Excel 2003 spreadsheet using custom code that we wrote for the program.  The program has been evolving over more than 15 years, and has seen many different programmers over the years.  A couple of our clients, and we have been able to reproduce in our office, have recently started noticing data discrepancies between the data that was in the Excel spreadsheet, and the data that appears in the database after the import has occurred.  These discrepancies are not consistent.  The program allows for the same spreadsheet to be imported multiple times, looking for a key column for a data match.  If a data match is found in this one column, the rest of the data in the database is supposed to be updated with the data from the spreadsheet.  What we are finding is that if we re-import the exact same spreadsheet a second time we are getting different information into the database.  Since it is the exact same data that was just imported, there should be no data changes in the database, but this is not the case.  In fact, data that does not exist in the spreadsheet in any cell, is being recorded into the database during the import.  We cannot determine where this mystery data is coming from.
We have tried recreating the spreadsheet, recreating the database, manually checking every cell in the spreadsheet, checking the formatting of every cell in the spreadsheet and we cannot find any ideas as to where these strange results are coming from.  We have already spent several days trying to solve this issue, and we have clients complaining loudly.  Short of completely rewriting the entire import module, we are running out of ideas to try to determine where the problem lies.
Since this problem exists entirely within custom written code, written in-house by programmers no longer with the company, it is very challenging for us to talk to the programmers to ask for help.  What we are hoping for is for some suggestions of testing methods to try and narrow down the problem.

Thanks
Brad
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 125 total points
Comment Utility
So I am sensing that because this is a "Custom" app you are unable to post any code...?

If so, I can't see how we can troubleshoot with any certainty.

I certainly see no reason that the data from the same source would magically "change".
The only thing I can think of is some sort of Temp/staging table is involved and that sometimes the app is looking at the temp table (old data)  and sometimes it is seeing the actual data.

...or this could be a timing issue, so try peppering the code with strategically placed "DoEvents" lines, and see what happens...

Let's see what other Experts may post...


JeffCoachman
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 125 total points
Comment Utility
possibly  a corruption.

do a compact and repair of the back end.
do the same for the front end

if problem persists after the compact and repair, create a New Db and import all the objects from the back end, do the same for the front end
0
 
LVL 26

Assisted Solution

by:jerryb30
jerryb30 earned 250 total points
Comment Utility
^What they said, and:

>Almost all of the problems are related to string fields and date fields.

What other data types show problems?

>A couple of our clients, and we have been able to reproduce in our office, have recently started noticing data discrepancies between the data that was in the Excel spreadsheet, and the data that appears in the database after the import has occurred.

What highlighted the problem? What, if anything, had changed in environment?

Do you have the ability to edit the vba?   Specifically, look for instances of:
On Error Resume Next
Set Warnings False

Can you remove current error checking, so as to see debug errors?

While your database is in Access 2003, are you using newer versions of Access which may reflect changes in allowable methods?

Are you making assumptions about data structure in both Excel and Access, from time of original program to now?

Do the errors occur early in processing (based on number of input records), late in processing, or both?

Do you have old versions of the program with old and new data sets to do testing and comparison of results?

I spent 10+  years as one of very few users of highly customized programs, written by a variety of programmers. As a non-programmer, I was still the one with institutional knowledge of all of the things which had gone wrong, and found myself frustrated by lack of:
Testing
Testing on a scale related to real world operations (Multiple and varied source data sets)
Regression testing after 'fixes'

So, I can only suggest you start as early as you can, with the oldest program and source data, and work forward until you can consistently see the same errors.  

It is not random.
All software is beta.
0
 

Accepted Solution

by:
KnowledgeWare earned 0 total points
Comment Utility
Thanks for the help everyone.  It was kind of a combination of the importer and the spreadsheet.  Turns out from several attempts and prolonged importing of bad data into the database, it corrupted.  We're implemented an integrity checker as part of the import procedure and feel that it should catch all the "bad data" during the import and in the database itself.
0
 

Author Closing Comment

by:KnowledgeWare
Comment Utility
No Real "fix".  Excellent answers...  the problem was just that it was and was trying to import bad data and after time, corrupted the db.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now