Database question

Posted on 2003-03-22
Medium Priority
Last Modified: 2013-11-26
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.
Question by:Stofferken
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5

Expert Comment

ID: 8187539
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

Accepted Solution

MrDark earned 200 total points
ID: 8187612
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

Author Comment

ID: 8189774
I get an error on this line:
Set rs = db.OpenRecordset("wonids", dbOpenDynaset)

Compile error:
Method or data member not found
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

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

Dim db As Database

Author Comment

ID: 8190604
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

Author Comment

ID: 8190617
Ok, now i works (dunno what I did)

But how to read the first line, then the 17th line and so one?

Author Comment

ID: 8190641
rs.getrows or something like that?

Expert Comment

ID: 8190656
nope like this:


the previous line will move to the record number 17

Author Comment

ID: 8190661
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?

Expert Comment

ID: 8190662
if you r online now plz send me your e-mail ar alielgamal@hotmail.com

Expert Comment

ID: 8190667
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month8 days, 10 hours left to enroll

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question