notepad manipulation

[leovicAustria]

Hi guys, can you please help me. Is it possible to get data from NOTEPAD,Store each data into a new table of MS-Access, Thanks..God bless

[choudhuryshouvi]

look at the following code.hope it'll help you.
before running the code just create an access database named "np.mdb" under your project folder. just take a command button on a form.

'this is a global variable for storing how many no. of tables have been created so far

Dim tot_table As Variant

'creating a new table with two fields and naming as per it's position no.
Public Sub CreateNewTable()
Dim db As Database, td As TableDef, fd As Field

Set db = OpenDatabase(App.Path & "\np.mdb")

tot_table = GetSetting(App.Title, "Table", "Count")
If Trim(tot_table) = "" Then tot_table = 0 Else tot_table = Val(tot_table)
tot_table = tot_table + 1
Set td = db.CreateTableDef("Table" & tot_table)
Set fd = td.CreateField("Rec_ID", dbLong)
fd.Attributes = dbAutoIncrField
td.Fields.Append fd
Set fd = td.CreateField("Rec_Desc", dbText, (255))
td.Fields.Append fd
db.TableDefs.Append td
db.Close
SaveSetting App.Title, "Table", "Count", tot_table
End Sub

'extracting each data from the notepad file and transferring the same into the table just created
Public Sub DoNP2DB()
Dim db As Database
Dim rs As Recordset
Dim rec As Integer
Dim str As String

Set db = OpenDatabase(App.Path & "\np.mdb")
Set rs = db.OpenRecordset("Table" & tot_table, dbOpenTable)
If rs.RecordCount > 0 Then rs.MoveFirst

rec = 1

Open App.Path & "\DBRecord.txt" For Input As #1
Do While Not EOF(1)
Input #1, str
rs.AddNew
If rs.EditMode = dbEditAdd Then
rs("Rec_ID") = rec
rs("Rec_Desc") = str
rs.Update
End If
rec = rec + 1
Loop
MsgBox "Transformation complete."
Close #1
End Sub

'creating a sample notepad file filling with all the month names
Public Sub CreateTextFile()
Dim i As Integer

Open App.Path & "\DBRecord.txt" For Output As #1
For i = 1 To 12 Step 1
Print #1, MonthName(i)
Next i
Close #1
End Sub

'calling all the sub-routines
Private Sub Command2_Click()
Call CreateTextFile
Call CreateNewTable
Call DoNP2DB
End Sub