' i have done throgh command button ----
'1 .sub CMDNOTIS and
'2. Public Function
Public Sub CMDNOTIS_Click()
Dim NOTISCn As New ADODB.Connection
Dim NotisFile As String
NOTISCn.ConnectionString = "Provider=" & Provider & ";Data Source= " & Mdbfilepathname & ""
NOTISCn.Open
NotisFile = MdbDatabasePath + "NotisTrades.txt"
'x Call CreateTableAdox(NOTISCn, "tmp_NSETrades")
Call ImportTextFile(NOTISCn, "tmp_NSETrades", NotisFile)
end sub
Public Function ImportTextFile(Cn As ADODB.Connection, _
ByVal TBLname As String, FileFullPath As String, _
Optional FieldDelimiter As String = ",", _
Optional RecordDelimiter As String = vbCrLf)
Dim cmd As New ADODB.Command
Dim RS As New ADODB.Recordset
Dim sFileContents As String
Dim iFileNum As Integer
Dim sTableSplit() As String
Dim sRecordSplit() As String
Dim lCtr As Integer
Dim iCtr As Integer
Dim iFieldCtr As Integer
Dim lRecordCount As Long
Dim iFieldsToImport As Integer
Dim DelTmpSql As String
Cn.CursorLocation = adUseClient
If Cn.State = 0 Then Cn.Open
DelTmpSql = "Delete * from tmp_NSETrades"
Cn.Execute DelTmpSql
'These variables prevent
'having to requery a recordset
'for each record
Dim asFieldNames() As String
Dim abFieldIsString() As Boolean
Dim iFieldCount As Integer
Dim sSQL As String
Dim bQuote As Boolean
On Error GoTo errHandler
'If Not TypeOf cn Is ADODB.Connection Then Exit Function
If Dir(FileFullPath) = " " Then Exit Function
'rst.CursorLocation = adUseClient
Cn.CursorLocation = adUseClient
If Cn.State = 0 Then Cn.Open
Set cmd.ActiveConnection = Cn
cmd.CommandText = TBLname
cmd.CommandType = adCmdTable
Set RS = cmd.Execute
iFieldCount = RS.Fields.Count
RS.Close
ReDim asFieldNames(iFieldCount - 1) As String
ReDim abFieldIsString(iFieldCount - 1) As Boolean
For iCtr = 0 To iFieldCount - 1
asFieldNames(iCtr) = "[" & RS.Fields(iCtr).Name & "]"
abFieldIsString(iCtr) = FieldIsString(RS.Fields(iCtr))
Next
iFileNum = FreeFile
Open FileFullPath For Input As #iFileNum
sFileContents = Input(LOF(iFileNum), #iFileNum)
Close #iFileNum
'split file contents into rows
sTableSplit = Split(sFileContents, RecordDelimiter)
lRecordCount = UBound(sTableSplit)
'make it "all or nothing: whole text
'file or none of it
Cn.BeginTrans
For lCtr = 0 To lRecordCount - 1
'split record into field values
sRecordSplit = Split(sTableSplit(lCtr), FieldDelimiter)
iFieldsToImport = IIf(UBound(sRecordSplit) + 1 < iFieldCount, UBound(sRecordSplit) + 1, iFieldCount)
'construct sql
sSQL = "INSERT INTO " & TBLname & " ("
For iCtr = 0 To iFieldsToImport - 1
bQuote = abFieldIsString(iCtr)
sSQL = sSQL & asFieldNames(iCtr)
If iCtr < iFieldsToImport - 1 Then sSQL = sSQL & ","
Next iCtr
sSQL = sSQL & ") VALUES ("
For iCtr = 0 To iFieldsToImport - 1
If abFieldIsString(iCtr) Then
sSQL = sSQL & prepStringForSQL(Trim(sRecordSplit(iCtr)))
' MsgBox sSQL
Else
sSQL = sSQL & sRecordSplit(iCtr)
' Debug.Print sSQL
End If
If iCtr < iFieldsToImport - 1 Then sSQL = sSQL & ","
' Debug.Print sSQL
'MsgBox sSQL
Next iCtr
sSQL = sSQL & ")"
'Debug.Print sSQL
Cn.Execute sSQL
'DoCmd.RunSQL sSQL
Next lCtr
Cn.CommitTrans
'rs.Close
Close #iFileNum
Set RS = Nothing
Set cmd = Nothing
ImportTextFile = True
Exit Function
errHandler:
On Error Resume Next
If Cn.State <> 0 Then Cn.RollbackTrans
If iFileNum > 0 Then Close #iFileNum
If RS.State <> 0 Then RS.Close
Set RS = Nothing
Set cmd = Nothing
End Function
'Please find above code which you can import text file
'but need to Pass some value in provider , 'Mdbfilepathname ,MdbDatabasePath
