Issue With Linked Table Excel Workbook Data Type

AlexB123

Board Regular
Joined
Dec 19, 2014
Messages
207
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!
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Is the linked table only used for inserting new data? Have you found a good workaround yet?
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top