DoCmd.Transfertext in ADPs 1

[DinosBarandGrill]

I need to allow users to import CSV files into a SQL database via a project file without exposing them to any dialogs other than a file select (I then pass the filename to the transfertext command).

However i cannot create or specify an import specification and therefore Access creates and populates a table but incorrectly types some of the fields that it imports. e.g. incoming data may have a field with leading zeros (01,02...) but Access creates an INT field and therefore the leading zeros are lost.

Even if i try to force Access to create text fields only by inserting a dummy record of 'X's comma separated it seems to ignore this an decide itself what to do.

Has anybody else come across this? Thanks for any hints or pointers!

 

[Nowell]

Hi,

Why don't you use the import data functionality in SQL server, then you can create a table before hand and just append to that table.

 

[cmmrfrds]

As Nowell suggests, look an SQL Server facility. DTS (Data Transformation Services) is an excellent product within Enterprise Manager to transfer data between many different data sources including, of course, sql server. You could create a DTS package and invoke the package from your Access app.