Page 1 of 1

General Databasing Title says All Rate Topic: ***** 5 Votes

#1 Shinilolz  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 8
  • View blog
  • Posts: 238
  • Joined: 30-November 06

Posted 09 December 2006 - 07:22 AM

You will be Learning About Data Acess Objects in this tutorial. Were going to be opening a Database along with managing Data in tables(Adding,Deleting,Ect). We are going to use a Acess Database format (.Mdb) in this tutorial. We will be making a Employee 'Status' Page.

**Simple VB Knowledge Required!**

Alright the first thing we need to do is open a DB where our tables will be stored. We need to declare a Variable to Hold the DB first.
Dim dbMyDB As Database




This gives you a variable/object that can hold a reference to your database. To open a simple Access database named "Shinigamiexample.mdb" type the following..
Set dbMyDB = OpenDatabase("Shinigamiexample.mdb")



Normally i would suggest typing out the complete path to the database, but if your directory is where the database is at, this will work.

Alright so we got a Database Wohoo! Now This isnt going to give us any Information(Data) so we need to open a Table. Were going to open a Single Table, You could do more but we wont get into that..

Lets Use VB's Object Named Record Set To Hold our Table.
Dim rsMyRS As RecordSet

Set rsMyRS = dbMyDB.OpenRecordSet("MyTable", dbOpenDynaset)




Yeay! we delcared a recordset Variable and used Openrecordset to get a Dynaset Type Table. This is not the only way to open a Record set, And online tutorials(other then this) and books(ect) can give you more iinformation. For the rest of this tutorial we will use the Dynaset Mode.

Ok we opend a Table, now lets access some data on it. The RecordSet object lets us move
in some records using Movefirst, Movenext, Move Last, Move previous.

Ok to get this tutorial working, lets make a DB, Make sure you have acess, And call it "Shinigamiexample.mdb" with a table named "ExampleTable" in it. Lets have the Fields,
"ID" (counter Type) Set this field to be the primary key. Then the field, "Name"(Text type) and field "Number of Days Working"(text type), Lets add some records to this Table and put a list box on a form named "Actions"(for previous wrong doings and what not)

Dim dbMyDB As Database
Dim rsMyRS As RecordSet

Private Sub Form_Load()

Set dbMyDB = OpenDatabase("Shinigamiexample.mdb")
Set rsMyRS = dbMyDB.OpenRecordSet("ExampleTable", dbOpenDynaset)

If Not rsMyRS.EOF Then rsMyRS.MoveFirst
Do While Not rsMyRS.EOF
	lstRecords.AddItem rsMyRS!Name
	lstRecords.ItemData(lstRecords.NewIndex) = rsMyRS!ID
	rsMyRS.MoveNext
Loop

End Sub



Alright the code should make the listbox fill up with records when the form loads. I added some new concepts in this part of the tutorial, we talked earlier about opening a table, the line "If not rsMyRS.EOF then rsMyRS.M oveFirst" Tells the DB to move the first record just incase there are not any records. EOF is a 'boolean property' that is true if your selected record is the last, or if there are no records in the Recordset.

Then we make the program add the "Name" field of all records to the list box by adding the current records field "Name" and moving to the next record. You ask for a field of a RecordSet by putting a ! between the name of the RecordSet object and the name of the field. The while loop checks to see if there are more records to add.

Alright, Hopefully you know that the reason the value of field "ID" is in the list box for the ItemData property is so that we would know the primary key for the records, so we could search for one =D

Put a text box somewhere on the form and call it "DaysWorked". Then copy the following code to the project.
Private Sub lstRecords_Click()

rsMyRS.FindFirst "ID=" & Str(lstRecords.ItemData(lstRecords.ListIndex))
daysworked.Text = rsMyRS!Number Of Days Working

End Sub



This will display the phone number of the selected person when clicking in the list box. It uses the FindFirst method of the RecordSet object. This takes a string parameter that is like what is after WHERE in a SQL expression. You state the field that you want to search in (here "ID"), then the evaluation criteria (here "=") and last the value to search for (here the ItemData of the selected item in the list box). Phew, Hope i Didnt loose you there, as we port over to the more complicated Bit =O.

So what we did was to search for the record with the "ID" field value that was the same as the ItemData property of the selected item in the list box. Then we show the value of the "Number Of Days Worked" field in the text box.

Ok so you most likly want to update one of the values of whatever field when doing Databases. You can do this with Edit and Update, we will attempt to change the value of "Number of Days Worked" by editing the text in its box and clicking a button =)

Put a command button on the form and name it "cmdUpdate". Then copy the following code to the project.

Private Sub cmdUpdate_Click()

rsMyRS.Edit
rsMyRS!Number Of Days Worked = daysworked.Text
rsMyRS.Update

End Sub



Hmm well that was easy, We changed the Number of Days worked for that employee (Hope you didnt Cheat him out of money >=O). now when we type
rsMyRS!Number Of Days Worked = daysworked.Txt


We will replace the value of "Number of Days wored" box with the value that is in the Text box.

Ok deleting records is really simple, to delete the current record we will use the Delete method of the RecordSet obect.Make another command button named "CmdDelete" and the following coe will do all the work of deleting our selected employee ( YOUR FIREED!!!!111oneone!...sorry i had to..)

Private Sub cmdDelete_Click()

rsMyRS.Delete
lstRecords.RemoveItem lstRecords.ListIndex

End Sub



Ok Adding somone is just like updating, but we use Addnew. Lets add anotherrrr Command button to the program. Lets call it "Cmdnew". Now once again add the code for adding a new record.
Private Sub cmdNew_Click()

rsMyRS.AddNew
rsMyRS!Name = "A New Employee"
lstRecords.AddItem rsMyRS!Name
lstRecords.ItemData(lstRecords.NewIndex) = rsMyRS!ID
rsMyRS!Number Of Days Worked = "0"
rsMyRS.Update

End Sub



And Boom, this wraps up my tutorial. I hope i have left you with enough knowledge to mess around and maybe create a DB for use in your Simple Life while i go Jump in the pool of my mansion...(joke). Hope you found this guide usefull.

**Shinigami**
DIC's Newest Forumer!!

This post has been edited by Shinigami: 09 December 2006 - 04:07 PM


Is This A Good Question/Topic? 0
  • +

Replies To: General Databasing

#2 MarkoDaGeek  Icon User is offline

  • Dirty Technophile
  • member icon

Reputation: 11
  • View blog
  • Posts: 11,158
  • Joined: 13-October 01

Posted 09 December 2006 - 12:10 PM

Nice, Thanks.
Was This Post Helpful? 0
  • +
  • -

#3 dippy1  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 69
  • Joined: 29-November 05

Posted 02 June 2008 - 02:14 AM

excellent tutorial, thanks, enjoy your swim. :rolleyes:
Was This Post Helpful? 0
  • +
  • -

#4 haidan_dan  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 14
  • Joined: 29-May 08

Posted 03 June 2008 - 09:33 PM

What if i want to display data from SQL server database on the combo box?
i have a problem, it don't display anything. Pz help me..
This is my code

Dim MyConnObj As New ADODB.Connection 'ADODB Connection Object
Dim myRecSet As New ADODB.Recordset 'Recordset Object
Dim sqlStr As String ' String variable to store sql command

MyConnObj.Open _
"Provider = sqloledb;" & _
"Data Source="";" & _
"Initial Catalog=tebrauteguh;" & _
"User ID=sa;" & _
"Password=sa;"

sqlStr = "select * from tblMLeave "

myRecSet.Open sqlStr, MyConnObj, adOpenKeyset

MsgBox "Total Number of records = " & myRecSet.RecordCount

'Dim i As Integer 'variable to keep count
'i = 1

'Print "#"; Tab; "LCode"; Tab; "LName";
'Print ""
With myRecSet

While Not myRecSet.EOF ' Loop until endd fo file is reached

ReaApp.Items.Add (tblMLeave.Item("LName"))
.MoveNext
Wend
End With

MyConnObj.Close
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1