Deleting Error Import Table issues

[bcurrey]

I'm using Access 2007. Most of the times when I import a file at my company, I get an error message because the item description is too long and it creates a table. I found a topic on how to delete the error tables in a macro, however I can't get it to work. Here's my code:

<code class="vb plain">Application.DisplayAlerts = </code><code class="vb keyword">False</code>
 
<code class="vb keyword">Function</code> <code class="vb plain">DeleteImportErrTables()</code>
<code class="vb keyword">Dim</code> <code class="vb plain">z </code><code class="vb keyword">As</code> <code class="vb keyword">Integer</code>
<code class="vb keyword">Dim</code> <code class="vb plain">db </code><code class="vb keyword">As</code> <code class="vb plain">DAO.Database</code>
 
<code class="vb keyword">Set</code> <code class="vb plain">db = CurrentDb</code>
<code class="vb keyword">For</code> <code class="vb plain">z = db.TableDefs.Count - 1 </code><code class="vb keyword">To</code> <code class="vb plain">0 </code><code class="vb keyword">Step</code> <code class="vb plain">-1</code>
<code class="vb keyword">If</code> <code class="vb plain">InStr(1, db.TableDefs(z).Name, </code><code class="vb string">"ImportError"</code><code class="vb plain">) > 0 </code><code class="vb keyword">Then</code>
<code class="vb plain">DoCmd.DeleteObject acTable, db.TableDefs(z).Name</code>
<code class="vb keyword">End</code> <code class="vb keyword">If</code>
<code class="vb keyword">Next</code> <code class="vb plain">z</code>
<code class="vb keyword">End</code> <code class="vb keyword">Function</code>

The error that I get says "Error Number 2950. Condition True, Arguments: DeleteImportErrTables()"

I'm a bit new at this, but I went to the "Data tools" tab on the ribbon. Clicked on Visual Basic, and then chose New - Module. I then copy and pasted the code above to the blank sheet.

Then went to Macro, and chose "RUN CODE" and told it the function name was DeleteImportErrTables ().

 

[Joe4]

Two things:

1. Get rid of the first line in your module before the function name ("Application..."). It really has no place outside of a function/procedure.

2. In the VB Editor, click on Tools drop-down menu and select "References" and make sure the "Micrsoft DAO 3.6 Object Library" reference is selected.

Then save and try running it again.

 

[Norie]

Why not try fixing the problem causing the error?

Then you wouldn't need this code.

 

[bcurrey]

I removed the first line.

I tried clicking the box in the references section, but I get an error message saying "Name conflicts with existing module, project, or existing library."

 

[bcurrey]

Why not try fixing the problem causing the error?

Then you wouldn't need this code.

Because the files are sent from another company, and the errors are that the Item Descriptions are too long in some cases.

 

[Joe4]

Do you have that Function listed in two different places/modules?

You may want to try doing a Compact & Repair database to make sure you do not have a corruption issue happening.

 

[Norie]

What I meant was fix the import of the data so you don't get the error.

You wouldn't need to change the data just how it's imported.

PS I don't think you actually need to add a reference, it sounds like it' already there.

 

[Joe4]

What I meant was fix the import of the data so you don't get the error.

You wouldn't need to change the data just how it's imported.

I am curious, how exactly would you do that?

We have the same problem, we get files from hundreds of clients, and you cannot obviously control all the garbage that they may send you. I use the same sort of logic to delete ImportError tables from some text fields which are not important to our processing (informational only).

The OP told you exacly what the import issue is, do you have a proposed solution for it?

Because the files are sent from another company, and the errors are that the Item Descriptions are too long in some cases.

 

[bcurrey]

Is it possible to "trim" or "truncate" the data in specific columns at time of import?

 

[Norie]

Joe4

Use a Memo field.

or

Use an alternative method of import.

or

Import all fields bar the problem one and then use an alternative method to import the problem field.

That's the kind of thing I was thinking of.