10 Years
Discussion Span
Last Post by 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
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
If rs.EditMode = dbEditAdd Then
rs("Rec_ID") = rec
rs("Rec_Desc") = str
End If
rec = rec + 1
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

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.