Retrieve records using Select statements in VB6

This article describes how to create a database in a program without using Access, and then read data from standard ASCII text files to the database. The original article is an article in the Microsoft Knowledge Base, but it was written for VB3 at the time, so the code here is a bit outdated. For example, if no Table object exists in DAO, replace it with a Recordset object. The following is the modified code, which is successfully debugged in VB6.

Add a reference to MicrosoftDAO3.51Library in the project.

Add three command buttons and two msflexgrids to the form.

Set and control attributes according to the following table:

Control property value

Command1Caption "Create a text file and display it in a grid"

Command2Caption "Transfer Data and create a database"

Command3Caption "Displaying Data in new database"

Grid1Cols5

Grid1Rows35

Grid2Cols5

Grid2Rows35

Add the following code to the declaration section of the form.

Dimnums (30) AsLong

Dimnames (30) AsString * 20

Dimaddresses (30) AsString * 25

Dimss_nums (30) AsString * 12

ConstDB_LONG = 4

ConstDB_TEXT = 10

ConstDB_LANG_GENERAL = "; LANGID = 0x0809; CP = 1252; COUNTRY = 0"

Add the following code to the Load event of the form.

SubForm_Load ()

Show

Grid1.ColWidth (1) = 1000 'forempid

Grid1.ColWidth (2) = 2000 'forempname

Grid1.ColWidth (3) = 3000 'forempaddr

Grid1.ColWidth (4) = 2000 'ForEmpSSN

Grid1.Col = 1

Grid1.Row = 0

Grid1.Text = "EmpID" 'headerforempidfromtextfile

Grid1.Col = 2

Grid1.Row = 0

Grid1.Text = "EmpName" 'headerforempnamefromtextfile

Grid1.Col = 3

Grid1.Row = 0

Grid1.Text = "EmpAddr" 'headerforempaddrfromtextfile

Grid1.Col = 4

Grid1.Row = 0

Grid1.Text = "EmpSSN" 'headerforempssnfromtextfile

Grid2.ColWidth (1) = 1000 'forempid

Grid2.ColWidth (2) = 2000 'forempname

Grid2.ColWidth (3) = 3000 'forempaddr

Grid2.ColWidth (4) = 2000 'ForEmpSSN

Grid2.Col = 1

Grid2.Row = 0

Grid2.Text = "EmployeeID" 'headerforempidfromdb

Grid2.Col = 2

Grid2.Row = 0

Grid2.Text = "EmployeeName" 'headerforempnamefromdb

Grid2.Col = 3

Grid2.Row = 0

Grid2.Text = "EmployeeAddr" 'headerforempidfromdb

Grid2.Col = 4

Grid2.Row = 0

Grid2.Text = "EmployeeSSN" 'headerforempnamefromdb

EndSub

Add the following code to the commandmediaclick event:

SubCommand1_Click ()

Fori = 1To30

Nums (I) = I

Names (I) = "JohnDoe #" Str $ (I)

Addresses (I) = Str $ (I) "MockingBirdLane"

Ifi <9 Then

'* Enterthefollowingfourlinesasone, singleline:

Ss_nums (I) = Trim $ (Str $ (I )) "-" Trim $ (Str $ (I 1) Trim $ (Str $ (I 1) "-" Trim $ (Str $ (I ))

Trim $ (Str $ (I )))

Else

'* Enterthefollowingtwolinesasone, singleline:

Ss_nums (I) = Trim $ (Str $ (999) "-" Trim $ (Str $(88 )) "-" Trim $ (Str $(7777 )))

EndIf

Nexti

Open "Testdata. DAT" ForOutputAs #1

Forj = 1To30

Print #1, nums (j)

Print #1, names (j)

Print #1, addresses (j)

Print #1, ss_nums (j)

Nextj

Close #1

Fori = 1To30 'displayresultsfromtextfile

Grid1.Col = 1

Grid1.Row = I

Grid1.Text = nums (I) 'loadempids

Grid1.Col = 2

Grid1.Row = I

Grid1.Text = names (I) 'loadempnames

Grid1.Col = 3

Grid1.Row = I

Grid1.Text = addresses (I) 'loadempaddrs

Grid1.Col = 4

Grid1.Row = I

Grid1.Text = ss_nums (I) 'loadempssns

Nexti

EndSub

Add the following code to the Command2_Click event.

SubCommand2_Click ()

DimnewdbAsDatabase

DimnewtbAsRecordset

DimnewtdAsNewtabledef

DimnewidxAsNewIndex

Dimfield1AsNewfield 'ForEmpnums

Dimfield2AsNewfield 'forempnames

Dimfield3AsNewfield 'ForEmpaddresses

Dimfield4AsNewfield 'ForEmpss _ nums

Screen. MousePointer = 11 'displaythetimetobuild

Setnewdb = CreateDatabase ("NEWDB. MDB", DB_LANG_GENERAL)

Newtd. Name = "Emp_Table" '* Newtablename

Field1.Name = "Emp_ID" '* HoldsEmployeeIDnums ()

Field1.Type = DB_LONG

Newtd. Fields. Appendfield1

Field2.Name = "Emp_Name" '* HoldsEmpnames ()

Field2.Type = DB_TEXT

Field2.Size = 20

Newtd. Fields. Appendfield2

Field3.Name = "Emp_Addr" '* HoldsEmployeeaddr ()

Field3.Type = DB_TEXT

Field3.Size = 25

Newtd. Fields. Appendfield3

Field4.Name = "Emp_SSN" '* Holdsempss_nums ()

Field4.Type = DB_TEXT

Field4.Size = 12

Newtd. Fields. Appendfield4

Newidx. Name = "Emp_ID_IDX" '* Youhavetohaveanindex

Newidx. Fields = "Emp_ID"

Newidx. Primary = True

Newtd. Indexes. Appendnewidx

Newdb. TableDefs. Appendnewtd

Setnewtb = newdb. OpenRecordset ("Emp_Table ")

Open "Testdata. dat" ForInputAs #1

BeginTrans

DoWhileNot (EOF (1 ))

Newtb. AddNew

LineInput #1, tmp1 $ 'retrieveempl _ id

LineInput #1, tmp2 $ 'retrieveempl _ name

LineInput #1, tmp3 $ 'retrieveempl _ addr

LineInput #1, tmp4 $

Newtb ("Emp_ID") = Trim $(tmp1 $) 'placinfield1

Newtb ("Emp_Name") = Trim $(tmp2 $) 'placeinfield2

Newtb ("Emp_Addr") = Trim $(tmp3 $) 'placeinfield3

Newtb ("Emp_SSN") = Trim $(tmp4 $) 'placeinfield4

Newtb. Update 'savetotable

Loop

CommitTrans

Close #1 'closetextfile

Newtb. Close 'closedb' stable

Newdb. Close 'closedb

Screen. MousePointer = 0 'setbacktoshowdone

EndSub

Add the following code to the Command3_Click event.

SubCommand3_Click ()

DimdbAsDatabase

DimtAsRecordset

Dimcounter

Setdb = OpenDatabase ("NEWDB. MDB ")

Sett = db. OpenRecordset ("Emp_Table ")

Counter = 1 'startcounteratrow = 1

DoUntilt. EOF

Grid2.Col = 1

Grid2.Row = counter

Grid2.Text = t (0) 'loadempid

Grid2.Col = 2

Grid2.Row = counter

Grid2.Text = t (1) 'loadempname

Grid2.Col = 3

Grid2.Row = counter

Grid2.Text = t (2) 'loadempaddr

Grid2.Col = 4

Grid2.Row = counter

Grid2.Text = t (3) 'loadempssn

Counter = counter 1

T. MoveNext

Loop

T. Close

Db. Close

EndSub

The preceding section describes how to use the Select statement to retrieve records in VB6.