How to use Instr to search for the first tab

Seems to me that the best way to parse a tab delimited string would be to use the Split() function. This function will take each piece of the string that is delimited by a tab and place it in an array for you. You can then access each element of the array as you like.

Example:

Dim my_string As String

my_string = "dog" & vbTab & "cat" & vbTab & "frog"

Split(my_string, vbTab)(0) ....will = dog
Split(my_string, vbTab)(1) ....will = cat
Split(my_string, vbTab)(2) ....will = frog

Oh, and you can also use the Split command like this (saves calls to split, which are processor expensive).

Dim sR() as String
Dim my_string As String

my_string = "dog" & vbTab & "cat" & vbTab & "frog"

sR = split(my_string, vbtab)

'sR(0) now contains "dog"
'sR(1) now contains "cat"
'sR(2) now contains "frog"

Knowing the value in a database field does not tell you what it's data type is.

For example, a date can be stored as a date or a string.

What you need is a way to examine the table definitions of your database. At the moment, I can't recall the syntax for doing this with an Access database. Perhaps someone else can provide for you.

You'll never be able to automate EVERYTHING.

Assuming a data type based on the contents of fields in a text file sounds like a risky proposition at best. Just developing the rules/logic behind discerning whether a value should be stored as a date, numeric [integer, double, long], string/text or any of the other data types would seem to me to be quite a daunting task. And then you need to code all that.

If I were you, I'd review the text files you are pulling from to determine patterns or sets of data, if any are readily apparent. Then you can define your tables and validate individual pieces of data to make sure they are the proper data type before inserting them into your tables.

Conversely, you could store everything as strings and then convert them to other data types in your code if you needed to do something with them that can't be done with strings. For example, store all numbers as strings, but then convert them to integers via code after retrieving them from your database if you need to sum the values for a particular field.