TransferSpreadsheet to Access local table suddenly begain to log errors (1 Viewer)

Rx_ · Jan 6, 2017

Can anyone suggest any ideas as to why this would be causing errors?
For a few months, a copy of a Spreadsheet named ProjectSummary has been transferred to a local Access table for use in query operations.
Recently it was noticed that an ProjectSummaryS_ImportErrors table began to appear.
This is the code:

Code:

 DoCmd.TransferSpreadsheet _
            acImport, _
            acSpreadsheetTypeExcel5, _
            "ProjectSummary", _
             Myfullname, _
            True, _
            WorksheetInvIssName & "!"

This is the error:
The first 36 are OK
ProjectSummary$_ImportErrors Error Field Row
Type Conversion Failure Task Number 36
Type Conversion Failure Task Name 36
Type Conversion Failure Task Number 37
Type Conversion Failure Task Name 37
Type Conversion Failure Task Number 38
.... this continues for hundreds of rows

In the local access Table ProjectSummary, the TASK Number is a type Short Text (255, format @
The Same for the Task Name column

On the spreadsheet, the Task Number and Task Name up to row 35 are numbers such as 3715 (for each column).
On the row the error begins the data is like:
000307-2 and ABC Open Construction
It would seem as if the Text of 255 would work for the text.

Lucky, these columns are not actually needed for the data analysis.
However, this process runs many times a day so the error tables are just piling up.

000307-2

Rx_ · Jan 6, 2017

Guess one could have looked a little longer before posting?
Found this on MSDN:

The import procedure determines the data type of a column by looking at the first few rows. If those rows contain numbers, the field is imported as a number field and hence the text values cause an error.
Creating a dummy record at the top with the correct data type in each column, as you have done, is the way to avoid this problem.

My Comment: Of course, adding data to the process is not the best solution.

Since the error isn't actually hurting anything, maybe a routine to delete all tables with the name like import errors?
Still, its a bitter pill to swallow.
Any other suggestions?

Rx_ · Jan 6, 2017

Validated: Changed the two columns in Excel data type from General to Text.
That prevented the conversion error.

Code:

Objxl.ActiveSheet.AutoFilterMode = "False"
Objxl.Columns("L:M").Select
Objxl.Selection.NumberFormat = "@"
Objxl.ActiveWorkbook.Save
 DoCmd.TransferSpreadsheet _
            acImport, _
            acSpreadsheetTypeExcel5, _
            "InvIss", _
             Myfullname, _
            True, _
            WorksheetInvIssName & "!"

This code runs from MSAccess so the Objxl is an object reference set to Excel.
The MS Access imports multiple Excel worksheets generated from an Oracle output. Places the data in local Access tables, joins to SQL Server tables, completes data processing and transfers the output into SQL Server.
Just an example of MS Access providing unique business rule interface for a small but important office organization.

TransferSpreadsheet to Access local table suddenly begain to log errors (1 Viewer)

Rx_

Nothing In Moderation

Rx_

Nothing In Moderation

Rx_

Nothing In Moderation

Similar threads

Users who are viewing this thread