Transfer Text - Missing Records 1

[ooch1]

Hello,

I am trying to import some csv files into access and am running int a few problems with missing items.

In the dataset below, following the import, records 3 and 7 will be missing within the table, even though it is set as a text field. If anything i would have thought that it would be the numbers that would be missing not the text items.

SiteRefNum
31000002
31000004
31000004A
31000005
31000006
31000007
[clor red]31000007A[/color]

My import code is as follows:

Sub ImptAst()
Dim cm As ADODB.Command
Dim StrUPT As String

 'import hull cot file
 DoCmd.TransferText _
    transfertype:=acImportDelim, _
    tablename:="A01-NBS_GAS_ROOTDATA_20040923", _
    filename:="D:\02-EXCEL_DOCs\Gas_Aston_COT_PortAnlys.csv", _
    hasfieldnames:=True
        
'create recordset objest
Set cm = New ADODB.Command
cm.ActiveConnection = CurrentProject.Connection

StrUPT = "UPDATE [A01-NBS_GAS_ROOTDATA_20040923] SET [A01-NBS_GAS_ROOTDATA_20040923].[NA_SOURCE_STATUS] = 'AST'" & _
                "WHERE ((([A01-NBS_GAS_ROOTDATA_20040923].[NA_SOURCE_STATUS]) Is Null));"

With cm
        .CommandText = StrUPT
        .Execute
End With
    
DoCmd.DeleteObject acTable, "Gas_Aston_COT_PortAnlys_ImportErrors"
    
End Sub

Please can someone help me with this problem, as i cannot continue with these reference numbers missing???

OOch

 

[PHV]

Use an explicit SpecificationName created with the imort wizard.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ooch1]

PHV - Ok, but do you know how i can then call it up within the module, or do i have to record a macro and call that up. Either way, please can you help with code to acheive this as it does work fine when the wixard is used??

OOch

 

[PHV]

DoCmd.TransferText _
transfertype:=acImportDelim, _
SpecificationName:="Name of saved spec", _
tablename:="A01-NBS_GAS_ROOTDATA_20040923", _
filename:="D:\02-EXCEL_DOCs\Gas_Aston_COT_PortAnlys.csv", _
hasfieldnames:=True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ooch1]

Thanks PHV, you've rescued me once again!

OOch