Can I increase the number of rows Access scans to determine data type?

0
votes

I have an issue where I'm importing data from an Excel spreadsheet into Access 2016. I have several fields that contain either a mix of text and number records and another with a mix of date format and blank records. From what I've read, Access only scan a certain number of rows (8?) to determine data type. Even if I manually change the column with date format to short date in Excel, when I import it into Access it changes the data type to text if the first rows are not in date format. Is there a way to change how many rows Access scans before determining a field's data type? It's very frustrating to have to sort multiple columns in Excel as I have to import several files per day...

1
excelms-access
Please edit you question to clarify: (1) Are you importing the spreadsheets using a macro or VBA code in Access? (2) Are you repeatedly importing spreadsheets with the same structure (number of columns and their data types)? – Gord Thompson

1 Answers

0
votes

Access it changes the data type to text if the first rows are not in date format

Of course. A Date field can hold only a valid date value or Null.

So link the data and use IsDate to filter those values that can read as text. Then convert these in a second query that updates your table.