Ghost in the Machine

Posted on 2013-02-05
Medium Priority
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?
Question by:KnowledgeWare
  • 3
  • 2
  • 2
  • +2
LVL 61

Expert Comment

ID: 38857716
What code is involved, and what are the exact error messages?
LVL 26

Expert Comment

ID: 38857882
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.
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38857946
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.
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38860011
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

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


Author Comment

ID: 38862292
Let me see if I can address the questions...
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.

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.

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

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.

LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 500 total points
ID: 38862511
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...

LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 500 total points
ID: 38862519
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
LVL 26

Assisted Solution

jerryb30 earned 1000 total points
ID: 38862650
^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 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.

Accepted Solution

KnowledgeWare earned 0 total points
ID: 38885550
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.

Author Closing Comment

ID: 38900943
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.

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
As a person who answers a lot of questions, I often see code that could be simplified, made easier to read, and perhaps most importantly made easier to maintain if the code was modified to use the Select Case statement. This article explains how to…
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…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

589 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