DOCmd.TranferText

[accessmanager]

I have been using DoCmd.TranferText for a while now.
Recently I have had to amend many records and links owing to loss of data during transfer.

I have replicated this problem both in Access 97 and 2000

Create a database with a single table (Test) with two columns of type text (Test1,Test2)
Create a form (Form1) with a single command button with an on click event as follows.

DoCmd.RunSQL "Delete Test.* FROM Test;"
DoCmd.TransferText acImportDelim, , "Test", "C:\Trial.csv", -1

Create the text file Trial.csv as follows.
Test 1, Test 2
A6123, KKJU
A6124, KKMT
A6125, KKSV

On running the above, data will transfer to the table Test with no problem.
However, if you change the data in the first column to the following format Knnnn then strange things start to occur. You can try all sorts of combinations of nnKnn and still have problems.
Beware and if anyone knows of any other nasties please post them.
I really do not think I should be double checking all my data has transfered correctly each time.

 

[FoFa]

Use a predefined import specification and this should not be an issue. I have never had any issues (other than bad data) using the import spec.

 

[accessmanager]

Use a predefined import specification and this should not be an issue. I have never had any issues (other than bad data) using the import spec.

Your suggestion works fine, thanks. The only points I would like to make are that if problems occur with data being changed on import from a delimited text file then the spec file should be enforced by access with no option for accepting the default, obviously I have already fallen into the trap. Further more a short cut to be able to create the spec file would be helpful instead of invoking the import table wizard. Thanks once again for the help.

 

[fuzzygeek]

schema.ini

You could create a schema for 97 & 2000. Go to the knowledge base and type schema.ini.
The following link shows how to programmatically create a schema.ini. I have used these in the past.

http://support.microsoft.com/default.aspx?scid=kb;en-us;210001&Product=acc

 

[Happy YN]

I can create a schema file but when I use it as a specification in a transfertext export I get an error?
How did you use it
Thanks

 

[fuzzygeek]

I last used it in 2000. Once set up, it must reside in the same directory as the database. Maybe these article will help:

http://support.microsoft.com/default.aspx?scid=kb;en-us;149090


http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp

Again, I think they have discontinued using the schema.ini file, but it was nice for making quick changes to the file. The import specification seems to be the way they have decided to accomplish this.

 

[Happy YN]

Thanks. The import spec is useless if field names have been added or edited. I have now learnt to create a schema.ini immediately before the docmd transfertext so it uses that information
Thanks
Only thing is that text is all surrounded in quotes. With a specification this can be altered but does not seem to have that possibility via ini

 

[clerics]

thanks for your suggestion. but follow this code :

Public Function CreateSchemaFile(bIncFldNames As Boolean, _
sPath As String, _
sSectionName As String, _
sTblQryName As String) As Boolean
Dim Msg As String ' For error handling.
On Local Error GoTo CreateSchemaFile_Err
Dim ws As Workspace, db As DAO.Database
Dim tblDef As DAO.TableDef, fldDef As DAO.Field
Dim i As Integer, Handle As Integer
Dim fldName As String, fldDataInfo As String
' -----------------------------------------------
' Set DAO objects.
' -----------------------------------------------
Set db = CurrentDb()

' -----------------------------------------------
' Open schema file for append.
' -----------------------------------------------
Handle = FreeFile
Open sPath & "schema.ini" For Output Access Write As #Handle
' -----------------------------------------------
' Write schema header.
' -----------------------------------------------
Print #Handle, "[" & sSectionName & "]"
Print #Handle, "ColNameHeader = " & _
IIf(bIncFldNames, "True", "False")
Print #Handle, "CharacterSet = ANSI"
Print #Handle, "Format = CSVDelimited"
' -----------------------------------------------
' Get data concerning schema file.
' -----------------------------------------------
Set tblDef = db.TableDefs(sTblQryName)
With tblDef
For i = 0 To .Fields.Count - 1
Set fldDef = .Fields(i)
With fldDef
fldName = .Name
Select Case .Type
Case dbBoolean
fldDataInfo = "Bit"
Case dbByte
fldDataInfo = "Byte"
Case dbInteger
fldDataInfo = "Short"
Case dbLong
fldDataInfo = "Integer"
Case dbCurrency
fldDataInfo = "Currency"
Case dbSingle
fldDataInfo = "Single"
Case dbDouble
fldDataInfo = "Double"
Case dbDate
fldDataInfo = "Date"
Case dbText
fldDataInfo = "Char Width " & Format$(.Size)
Case dbLongBinary
fldDataInfo = "OLE"
Case dbMemo
fldDataInfo = "LongChar"
Case dbGUID
fldDataInfo = "Char Width 16"
End Select
Print #Handle, "Col" & Format$(i + 1) _
& "=" & fldName & Space$(1) _
& fldDataInfo
End With
Next i
End With
MsgBox sPath & "SCHEMA.INI has been created."
CreateSchemaFile = True
CreateSchemaFile_End:
Close Handle
Exit Function
CreateSchemaFile_Err:
Msg = "Error #: " & Format$(Err.Number) & vbCrLf
Msg = Msg & Err.Description
MsgBox Msg
Resume CreateSchemaFile_End
End Function

**********************************************************

?CreateSchemaFile(True,"C:\Program Files\Microsoft Office\Office\Samples\","EMP.TXT","Employees")

I see that Employees is a table which in CurrentDB.This mean i'd had to create it or had it before (just what i m thinking) So , if i want this function see the database in ".cvs" file and create Schemafile automaticly ! How can i do that? Please help me ! thanks first.

* Set db = CurrentDb() <--- change this, right ?

 

[Happy YN]

You can create a table on the fly. Lookup CREATE TABLE statement. Good Luck

 

[clerics]

code: (Exam)

Dim db As Database, tbl As TableDef
Set db = CurrentDb()
Set tbl = db.CreateTableDef("Newtable")

tbl.Connect = "Text;DATABASE=H:\Documents and Settings\"
tbl.SourceTableName = "abc.csv"
db.TableDefs.Append tbl
db.TableDefs.Refresh

Thanks friend, is this right ?