Issue With Linked Table Excel Workbook Data Type

[AlexB123]

Hi all,

I have a linked table that is used by an update query to update records in the main table of my database. Everything has been running smoothly ... Normally, there are between 100 - 500 records that are updated at one time, on a daily basis, by this query. They join on a UID that is created by the database; ie., an AutoNumber field. When there are several hundred records run, it works fine. But today, a user forgot a single record, and attempted to run the update with that one record in the linked table, and the query failed - due to an incorrect data type error?

From what I've read, because Excel has no strong data types, Access (by default) looks at the first 8 rows to determine the datatypes of the columns?

When I ran the update with the single record, the query failed, and the linked table (shown in design view) used text for the "DBID" field. When I ran the update with 600 records, including the single record which previously failed, the query runs and the linked tables shows "DBID" as a numeric field.

What is going on? Can I not force Access to always interpret the data type of my "DBID" field to be a number?

Note - I tried formatting the actual spreadsheet in Excel to be a Number data type, but this had no effect when I only updated the single row.

Thanks!

 

[Joe4]

Yes, I have run into this issue many times myself. Access and Excel try to get too smart and figure out the data type and don't let you override it.
Because of this, I seldom work with linked Excel tables in Access. I instead export the Excel files to a text file (i.e. Tab-Delimited or CSV), where I control the data type.
I usually write VBA code for the export/import process, to avoid manually having to do this.

 

[welshgasman]

I suppose you could use VAL([DBID]) on the query to get a numeric value, however if there is ever text in there, the result would be 0 ?
So why would Access think it was text?

 

[Micron]

Some will copy linked data over to an Access table that has been properly formatted. While I've never had to do this (at least as far back as I can recall) it supposedly solves the issue. Maybe you ought to be checking that there is at least one record (you say user forgot). I realize this wouldn't fix your problem, but it sounds like your code shouldn't run anyway if a user forgot to input critical data.

however if there is ever text in there, the result would be 0

Not unless it encounters text before any number. Any numbers that come after any text will not be found either.

 

[xenou]

Is the linked table only used for inserting new data? Have you found a good workaround yet?