Switch off auto data type identification

[Freedox]

Hi, apologies in advance if this quesiton was previouly raised, but I couldn't find the answer anywhere.

Does anyone know if there is a way to turn off or idealy set auto data type identification to "Short Text"? I believe MS Access looks at the first 24 rows and applies data type for the table. In my case first 24 (in fact 1000s ) of first column data might be a Number but I need to be Short Text as some of the numbers are alphanumeric.

So I'm importing data first to a temp table then passing data to another table via ODBC connection. So is there a way to make sure that in temp table "tblrsSLPARENTTEMP" data type will always be set as "Short Text"?

Set db = CurrentDb
On Error Resume Next: db.TableDefs.Delete "tblrsSLPARENTTEMP": On Error GoTo 0
db.TableDefs.Refresh


DoCmd.TransferText TransferType:=acLinkDelim, TableName:="tblrsSLPARENTTEMP", _
FileName:=strPathFile, HasFieldNames:=True
db.TableDefs.Refresh

[Minty]

A number of ways to counter this
1. Change your temptable to only be short text fields ?

2. Insert a dummy row of data that contains a text string ("a" would do) in to the first row of data (I'm guessing this is excel) then remove it after you have imported it.

3. The final one would be to create an import spec and use that, but if your fields change that won't work.

[Gicu]

Instead of importing try to link the Excel file then use pass your data from that.

Cheers,

[CJ_London]

So I'm importing data first to a temp table

your code is not importing, it is linking

another method is to set HasFieldNames to false which will bring through the (text) headers so all fields will be text- then just ignore that record which should be easy to do since you are linking to the data to process on via odbc

[Freedox]

A number of ways to counter this
1. Change your temptable to only be short text fields ?

2. Insert a dummy row of data that contains a text string ("a" would do) in to the first row of data (I'm guessing this is excel) then remove it after you have imported it.

3. The final one would be to create an import spec and use that, but if your fields change that won't work.

1. I have tried importing table (not linking), then deleting values, changing data type to short text and re-importing, but once re-imported it changes data type to int which throws an error
2. This should be automated process so this means, I don't intend to open the file and edit before import, so that won't work :/
3. Yep, spec would work, but columns might change

[Freedox]

Instead of importing try to link the Excel file then use pass your data from that.

Cheers,

Unfortunately it's CSV file

[Freedox]

your code is not importing, it is linking

another method is to set HasFieldNames to false which will bring through the (text) headers so all fields will be text- then just ignore that record which should be easy to do since you are linking to the data to process on via odbc

You're right it was link not import, however, changing to import didn't do any good

the problem with has fields is that I need to know field name as columns will not necessary be in the same squence, like name and date columns might be swithced between columns A and B

[Minty]

1. I have tried importing table (not linking), then deleting values, changing data type to short text and re-importing, but once re-imported it changes data type to int which throws an error
2. This should be automated process so this means, I don't intend to open the file and edit before import, so that won't work :/
3. Yep, spec would work, but columns might change

2 - You CAN automate this, just takes a few steps.

1. Create an excel file of your CSV, you can do this in VBA from Access.
2. Now you can check how columns of data you have, and simply insert a dummy row of text "abc123" as the first line of data in all columns, again all in VBA.
3. Now you import to a temp table, and everything is a text value.
4. Delete the dummy data.
5. Delete the temp Excel file.

[Freedox]

2 - You CAN automate this, just takes a few steps.

1. Create an excel file of your CSV, you can do this in VBA from Access.
2. Now you can check how columns of data you have, and simply insert a dummy row of text "abc123" as the first line of data in all columns, again all in VBA.
3. Now you import to a temp table, and everything is a text value.
4. Delete the dummy data.
5. Delete the temp Excel file.

Good idea! But instead of creating I might just add a line or two to CSV file and that hopefully will do the trick

[CJ_London]

did you try my suggestion in post#4?

[isladogs]

For info, Access checks the datatype of the first 8 rows (not 24..unless its changed recently)
I would continue to link to the Excel file or better still CSV file as that is text.
Then create a routine to import to your Access table in which you ensure the correct datatype by using CStr(FieldName) in your append or update query / SQL string / recordset procedure.
I have done this for years and it ALWAYS works.
Similarly you can use e.g. CCur, CDate, CLng etc for fields where appropriate

[Freedox]

Thanks everyone for suggestions, finally got around it, used similar approach that Minty suggested:

1. Created VBA in Excel to open CSV and insert 50 lines (just in case) with dummy "text123"
2. Save new file
3. Perform linked import

It did created database with short text data types.

Again, thanks all for suggestions!

[CJ_London]

seems a long way round, but if it works for you

good luck with your project