Thanks for Allen for your suggestion, but i've almost tried every single
method mentioned in this forum and it still doesn't work.

I've tried to convert the columns in the csv file to text before the
transfertext method as follows

xlwkbk.worksheets(1).columns("A").numberformat = "@"  
DoCmd.TransferText acImportDelim, , "tmpTable", CurrentProject.Path & "\test.
csv", no

and it doesn't work.  then i tried to create tmpTable with all text fields
and insert "ABC" in the first line of every column in the csv file then
execute transfertext... but still it doesn't work.  These two methods will
generate the type conversion error table after execution.

Then i moved on to use the following function BuildJetTextSource written by
John Nurick with the following statements and this time I didn't get the type
conversion error table but some of the values in the columns (the ones that
generate the conversion error previously) were missing in the target table.

strSQL = "insert into targetTable (ACCOUNT_ID,DEPT_NAME) SELECT f1,f2 from "
& _
'BuildJetTextSource("C:\test.csv", False) _
'& ";"

Function BuildJetTextSource(ByVal FileSpec As String, _
ByVal HDR As Boolean) As String

'Takes a filespec and returns a string that can be used
'in the FROM clause of a Jet SQL query.
'  E.g.
'    C:\My Folder\MyFile.txt
'  returns
'    [Text;HDR=No;Database=C:\My Folder\;].[MyFile#txt]
'  The HDR argument controls the HDR parameter in string returned.
'
'  By John Nurick 2005
'  Revised 2007 to remove call to Dir()

Dim fso As Object 'FileSystemObject
Dim strFolder As String
Dim strFileName As String
Dim strFileExt As String
Dim strTemp As String

'Parse FileSpec
Set fso = CreateObject("Scripting.FileSystemObject")
  With fso
    FileSpec = .GetAbsolutePathName(FileSpec)
    strFolder = .GetParentFolderName(FileSpec)
    strFileName = .GetBaseName(FileSpec)
    strFileExt = .GetExtensionName(FileSpec)
  End With
Set fso = Nothing

'Build string
strTemp = "[Text;HDR=" _
  & IIf(HDR, "Yes", "No") _
  & ";Database=" _
  & strFolder & "\;].[" _
  & strFileName & "#" _
  & strFileExt & "]"

BuildJetTextSource = strTemp
End Function

Can anyone please help me with this problem?  Or is there a way such that i
can programatically execute the import method in the menu bar.  Because
manually importing the same csv file does not encounter any errors.  

Thank you....