Controlling Data Types When Using Transfertext Method?

[moorejp]

I'm using VBA (TransferText & TransferSpreadsheet) to automatically import various types of files into temporary tables in Access 97. The problem is that Access makes guesses about the data types of the columns based upon the first few hundred records it reviews prior to import. For instance, Access will import an alpha-numeric ID field as Long Integer if the first 500 records all appear to be numeric... during the import, errors are received for IDs containing alpha characters.

Is there any way to force all columns to be imported as text using the TransferText or TransferSpreadsheet methods?

Or, is there a way to force Access to review the all records instead of just the first few hundred prior to assigning data types and importing? I seem to recall that being an option in the Advanced Import Wizard for earlier versions of Access, but don't see it in either 97 or XP.

The files I'm importing do not have any set formats, so I can't set up data import specifications to resolve this headache. Any suggestions will be much appreciated !!

 

[Taff07]

By not having a set format, do you mean you don`t know how many fields are going to be imported each time as well?

ian

 

[moorejp]

Unfortunately, that's exactly what I mean: There's no way of knowing how many fields or the type of data sent. The customers sending the data are adamant that they cannot be held to a data format standard <arrrrgh!>.

What I'm doing is allowing the user to map the fields from the temporary table (after its been loaded) to the fields in the permanent SQL Server table, then building the SQL Insert statement based upon that mapping.

 

[MisterC]

Without knowing the format of the file to be imported, you will either have to
1. rely on Access's &quot;guesses&quot;, or
2. manually specify the data format <import specification>
for each and every import, or
3. if there are a limited number of formats, design an
import specification for each then select from the
available choices, or
4. Make your users comply to a specific data format.

Number 4 is by far the best solution. I'm sorry if you are stuck with Number 2 - lots of extra work :-(

 

[Taff07]

Do you start the import process manually or is it an automatic thing at set times?

If you are clicking something to import each time then maybe you can have a prompt to ask how many fields are in the import file and then create a table to import the data to with the corresponding number of fields all set to text.

Maybe if anyone know how to import just a single line of a file you could determine the number of fields at this point and do it automatically.

Ian

 

[moorejp]

Thanks for the suggestions, Ian and MisterC. I'm thinking of going in a slightly different direction now:

Create a link to the data file, then use the linked table's TableDef to build a format file for bulk copy to SQL server, since the data will eventually end up on the server anyway. From testing last night, it appears SQL server is much more flexible in imports than Access.

Failing that, I might try to create the temporary import specs you're both suggesting based upon the link's TableDef.

If I find either of these works, I'll throw out the details in case anyone in interested.

John