Database Application

• • Reply

  15 years ago

  by ron_pher

  ron

  , Cook Islands

  Joined 15 years ago

      Hey guys.... m totally new to Vb and i havent yet madea database project of my own... i have alot of readings about ADo, DAO and DataEnvironment and User Control, .... but havnt done anything practical yet...
  
  
  I tried using data environment.... can get connection, but dnt know how to use it....
  I dnt know the procedure of using database in code .... I guess i have to establish connection, then create rescordsets and queries but i dnt kow how to go on doin that...
  
  I have a project in whish i need to save to a DB and then print from the database...
  
  can someone help me with the simplest method of database reading, updating and deleting... and also bout grids... how u we store stuff in columns and also can we stil use grid without using database(ie add to grid from variables)...
  
  thanks for your help guys....
  
  
  
  
  
  
  
  
  
  

  Report abuse
• • Reply

  15 years ago

  by init

  Adam Gunning

  , United Kingdom

  Joined 15 years ago

  hi this is the easiest way i have found to do it... does require abit of SQL! but if you can learn the alphabet you can learn sql!

  first things first, make a reference to "Microsoft ActiveX Data Objects Library 2.8" (or which ever version you have listed) to do this you press Alt+P then click refences.

  add three command buttons and a module!

  'stick this in a module...

  Public ReturnedDBData As ADODB.Recordset
  Public oConn As ADODB.Connection
  
  
  

  ' now create a function...

  Public Function Connect(dbpath As String) As String
     
  'CREATING CONNECTION
      Set oConn = CreateObject("ADODB.Connection")
      On Error Resume Next
      With oConn
          .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & _
              dbpath & _
              ";jet OLEDB:database"
          .Open
      End With
  
  
  
  
  
  
  
  
  
  
  

  End Function

  Public Function ViewData ()

  dim strSQL as string

  strsql = "Select * from TableName"   ' change TableName to the actual name of the table

   Set ReturnedDBData = CreateObject("ADODB.Recordset")  ' creates recordset

      Set ReturnedDBData = oConn.Execute(strSQL)  ' sends the sql statement

  If Not (ReturnedDBData.EOF) Then
     
      ReturnedDBData.MoveFirst
  
  

     Do While Not ReturnedDBData.EOF
    msgbox ReturnedDBData.Fields(0).Value & vbtab & ReturnedDBData.Fields(1).Value ' displays 'field 0 and 1 from the table.. do this as for as many fields as there is!
  

    ReturnedDBData.MoveNext
      Loop
  

  Else
          MsgBox "no records found"
     
  End If
  
  
  

  end function

  public function UpdateData()

  dim strSQL as string

   strSQL = "UPDATE TableName SET FieldName = 'Value' where AnotherFieldName = 'Something'"      '  for example  "update addresses set TelNumber = '999' where Forename = 'Emergency'"

   Set ReturnedDBData = CreateObject("ADODB.Recordset")  ' creates recordset

      Set ReturnedDBData = oConn.Execute(strSQL)  ' sends the sql statement

  ' call viewdata                   ' uncomment this line if you want it to automatically call the view again

  end function

  public function DeleteData()

  dim strsql as string

      strSQL = "DELETE FROM Tablename WHERE Fieldname = 'Value'"        ' for example "delete from Contacts where Email = '[email protected]'"

   Set ReturnedDBData = CreateObject("ADODB.Recordset")  ' creates recordset

      Set ReturnedDBData = oConn.Execute(strSQL)  ' sends the sql statement

  end function

  ' now goto your main form code..

  private sub form_load

  call Connect ("C:\desktop\database.mdb")

  end sub
  
  

  private sub command1_click()

  call viewdata

  end sub

  private sub command2_click()

  call updateData

  end sub

  private sub command3_click()

  call deletedata

  end sub

   

   

  ' ok this isnt tried and tested as im too tired.. but all should be ok! need ne more info i will help you out... jus giv us a shout

   

  init

  Report abuse