Custom Table Import Error Routine? 1

[kjv1611]

Is there a way to customize the way Access handles the import routine, in regards to table errors? If there is a way, I'd like to change the error table naming from what is currently used.

Here is why: I have a database where I want to allow the import of multiple workbooks at the same time, each of which can contain multiple worksheets.

Though it is rare that someone will need to import multiple files, it would be at least possible.

Initially when testing I did not get all the errors, but now, once I started telling Access that yes each worksheet should have field names, it's been creating error tables.

I previously setup some code to loop through these tables and correct the table errors after the imports are complete in another database, and so I can adapt that code to this database.

The problem comes in with this one b/c of the table naming.

In order to differentiate what sheet came from what workbook, currently, I have the table naming scheme like this:

[blue]WorkbookName_SheetName[/blue]

That works fine for everything else, except for the error tables. Apparently when there is an error, Access uses solely its own naming scheme for the tables.

When there are any errors found, Access creates the Import Error Tables that look like this:

[blue]SheetName[/blue]$_ImportErrors

There may be a better method all around to do this, and now I'm wondering again whether I chose the best method... Because a workbook or worksheet name could already contain the [blue]_[/blue] character, so...

But the question is specifically:
How do I tell Access to use a different naming scheme for Import Error Tables?

Any suggestions/thoughts/references?

Thanks

--

"If to err is human, then I must be some kind of human!" -Me

 

[lameid]

I don't believe you can. You do however know what the name will be based on the sheet name, unless it already exists in which case it will stick a number in. To prevent this you should delete your error tables after processing them.

In general, I try to ensure that there will be no errors. I'm not sure how you can handle these errors programatically that would be less trouble than programatically fixing the file.

Another way to go is to save the file to text format before importing. Then you can make a text import specification. Text is more forgiving than Excel for importing.

 

[kjv1611]

Well, the thing is that the people who actually use this database on a recurring routine (well they're currently using an older version) would have to have very detailed instructions for exporting to text. I realize it's not hard, but at least some of them would have a very difficult time with that.

The reason this is of concern is that b/c if they import more than one workbook at a time, each workbook could have a sheet named the same. So, I'll have that numbers thing going after just one import.

Also, as a note, I have this database to not store ANY of the tables permanently. It has a few it keeps permanently, but none of the user tables. It's just a tool for randomizing data without going to SQL Server, then picking a sample size of those random accounts based on set changeable audit criteria.

That was what I was afraid of - don't think can change it. I'll have to see if I can find another way around it, I suppose.

Thanks for your input.

--

"If to err is human, then I must be some kind of human!" -Me

 

[lameid]

This code will save strFilePath to strNewPath as an Excel document... You will need to set a reference to Excel. This should be easily modified to save to text. Ironically, I save a tab delimited file with an XLS extension to an Excel file so that I can reformat it for import purposes.

    Dim XLAPP As Excel.Application
    Dim XLWorkBook As Excel.Workbook
   'set following variables in code or change to parameter
    Dim strFilePath
    Dim strNewpath as string

 
  Set XLAPP = CreateObject("Excel.Application")
       With XLAPP
          '.Visible = True  'When you debug, sometimes it helps to see what is going on
           .Workbooks.Open FileName:= _
               strFilePath
	   .ActiveWorkbook.SaveAs FileName:=strNewPath _
                    , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
                    ReadOnlyRecommended:=False, CreateBackup:=False
           .close
           .quit
       End With
     Set XLWorkBook = Nothing
     Set XLAPP = Nothing

 

[kjv1611]

Thanks, I'll take a look at that. It makes sense that it can be automated that way, I just never thought about it!

I'll give that a try and post back probably later today with the results..

--

"If to err is human, then I must be some kind of human!" -Me