• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

Database question

Private Sub chstatus(tmp)
  Dim findAt As Long
  Dim SearchStr As String
  Dim ReplaceStr As String
  Dim FileName As String
   stat = 0
   If List3.Text = "Normal" Then
       stat = 1
   End If
   If List3.Text = "Friend" Then
       stat = 2
   End If
       tmptxt = List4.Text
       If tmptxt = "none" Then
           tmptxt = ""
       End If
  SearchStr = List1.Text & " " & List2.Text & stat & " " & Chr(34) & tmptxt & Chr(34)
  If Combo1.Text <> "auto added" Then
       stat = 0
       If Combo1.Text = "Normal" Then
           stat = 1
       End If
       If Combo1.Text = "Friend" Then
           stat = 2
       End If
       ReplaceStr = List1.Text & " " & List2.Text & stat & " " & Chr(34) & tmptxt & Chr(34)

       FileName = App.Path & "\db.txt"

       With RichTextBox1
           .LoadFile FileName, rtfText

              findAt = .Find(SearchStr, 0)               If (findAt < 0) Then Exit Do
                   .SelText = ReplaceStr
           .SaveFile FileName, rtfText
       End With
end if
       Combo1.Text = "auto added"
       Command2.Visible = False
       Command3.Visible = False
       Combo1.Visible = False
       ldmain (0)
   End If
End Sub

Well this is the function that I use now thx to supunr
when you look you see there are 4 columns:
each column is displayed in a listbox, when I select an item a combo box apears were I can set the status.
These 4 columns are in a text file like this:
specificusernumber "namesoftheperson" statusnumber "comments"
123 "john,stoffer,john and stoffer" 1 "this is an example of a row in my text file"

So it would be very usefull to be able to just put column1 up to 4 in a database.
But I dont find that file to add a component for using databases and I have no idea how to write and read from a database.

Please help me.
  • 5
  • 5
1 Solution
Here is an example how to open a database, add a record and read from a recordset. You need to add an reference to the microsoft DAO 3.6 object library . I hope this helps a little bit.

   Dim db As Database
   Dim rstTemp As Recordset
   Dim strComment As String
   Set db = OpenDatabase("C:\temp.mdb")
   Set rstTemp = db.OpenRecordset("Select * from TableName", dbOpenDynaset)
    'adds a new record
    rstTemp!specificusernumber = 1234
    rstTemp!namesoftheperson = "Gertjan"
    rstTemp!statusnumber = 4
    rstTemp!Comments = strComment
    'read records
    Set rstTemp = db.OpenRecordset("Select * from Table", dbOpenDynaset)
    Do Until rstTemp.EOF
        strComment = rstTemp!Comments
Hi Stofferken,
in vb6 u can create ur database using the visual data manager. you can go to it from Add-Ins->Visual Data Manager.
A new Window will appear u have then to create a new database then two windows inside will appear one 4 database window and another one 4 sqlstatement. right click on any free space in the database window and select new table. then type in the name of the table then add fields (equals to columns in ur code) in each field you have to type it's name and the type of data which will be stored in the field. in you case the data will differ from a record to another record so check variable field.You can allow Zerolength if the field may not cotain any data. If the field is required (You don't want to save a record if this field doesn't contain any data).after you finish click on ok.
add your 4 columns by this way and then click on close then build table.
You can add indexes which will help you when searching but i don't think that it will be good 4 ur case because the field which you use with an index can't contain 2 fields with the same data(If it is 4 a name You can't store another record with the same name).After All of that you now are finished with database.Note that you can use the table you created in the visual data manager by double clicking on the table name and clicking on add then type the data then click on update.
To Use the database in the code to edit delete or add or search you first have to add the DAO 3.6 follow the steps: Project ->refrences -> check Microsoft DAO 3.6 Object liberary.

Important note: You can't use the database if it doesn't contain any records so you may add a record using the visual data manager be4 continuing as mentioned.

Now You project is ready to use the database but you have first to tell it where ur database is located so we first have to make 2 variable one for the database and one for the table you created. you can declare (may be in the general decleration) them be the following line:
dim db as database
dim rs as recordset

You have now to set the database to be the database you created. and the record set to be the table you created(you can put these lines in the form_load procedure).In the following lines i will work as if the database in the application's path and is named "Stofferken.mdb" and the table is called "Stofferken":

set db = dbengine.opendatabase(App.path&"\Stofferken.mdb")
set rs = db.openrecordset("Stofferken",dbopendynaset)

Note: Because we didn't use indexes we made the type of the recordset dbopendynaset but if we did used indexes we will have to make the type dbopentable this will change the type of searching in the database.

Note: The Fields you made in the field are reffered to using the it's position in the table beging from 0(zero).

Now you want to know how to add a record.In the next lines i will work with 4 fields and which will contain string but the last one will contain integer. You can do the using the following line:

rs.fields(0) = "Here You put the data of the first field"
rs.fields(1) = "Here You put the data of the first field"
rs.fields(2) = "Here You put the data of the first field"
rs.fields(3) = 0

To retrieve the data from the record you can directrly use rs.fields(0) you can change the number according to the field you want. look at the next line:
text1.text = rs.fields(0)

The previous line will put the text which is stored in the first field in the text field to get the second field you can use rs.fields(1) and so on.

to edit the data stored you can use:
rs.fields(0)= "Stofferken"
rs.fields(3) = 10

the previous lines will change the data in the first field to "Stofferken" and the data in the last one to 10.
To search in the database using:
search_name = "Stofferken"
if rs.nomatch = true then msgbox("Not found") else text1.text = rs.fields(0)

the previous lines will search the table for the first record that contains "Stofferken" in the first field. You note that we put the name of the field here not the position of it using findfirst property this property begins the search from the beging of the table to the end of.

Note:       If You want to search from the end to the beging use findlast.
      if You want to search from the current record to the end use findNext.
      If You want to search from the current record to the beging of the table use.

I hope you to succeed in you first database program :):)
If you have any comments or you couldn't make any step plz tell me or just send me e-mail   alielgamal@hotmail.com
StofferkenAuthor Commented:
I get an error on this line:
Set rs = db.OpenRecordset("wonids", dbOpenDynaset)

Compile error:
Method or data member not found
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

You Didn't define db as DataBase
Check the Declaration line:

Dim db As Database
StofferkenAuthor Commented:
I did define it

Private Sub Command1_Click()
    Dim db As Database
    Dim rs As Recordset

    Set db = DBEngine.OpenDatabase(App.Path & "\db.mdb")
    Set rs = db.OpenRecordset("wonids", dbOpenDynaset)
rs.Fields(0) = "123"
rs.Fields(1) = "name1,name2"
rs.Fields(2) = 0
rs.Fields(3) = "bla"
End Sub

I still get an error, same when I define it in form_load
StofferkenAuthor Commented:
Ok, now i works (dunno what I did)

But how to read the first line, then the 17th line and so one?
StofferkenAuthor Commented:
rs.getrows or something like that?
nope like this:


the previous line will move to the record number 17
StofferkenAuthor Commented:
When I give this program to someone else for instance, how that what code do I have to add so the program will create that database all by himself without having to edit in visual data manager?
if you r online now plz send me your e-mail ar alielgamal@hotmail.com
I forgot to tell you that the line i told you causes u to move 4 17 record if you are in the record number 2 u will be moved to the record number 18 not 17

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now