Adding, Querying, Changing, and Deleting Data??

• • Reply

  18 years ago

  by Tijger

  Jess H

  Pennsylvania, United States

  Joined 19 years ago

  My computer class is learning Visual Basic and the book we have teaches how to use SQL, but the computers we have don't have SQL installed and we are using Microsoft Access 2000 for our databases.  My teacher asked us to figure out how to add, query, change, and delete data but the book doesn't tell us what to use for Access and we can't find anything on MSDN.  I looked on this site as well, but no luck there either.  Can anyone help me please?  Thanks  

  Report abuse
• • Reply

  18 years ago

  by Nigorr

  Nicholas Orr

  Brisbane, Australia, Australia

  Joined 19 years ago

  SQL is not a program but a language just like HTML, VB and stuff and stands for Structured Querry Language.
  

  
  As for adding, querying, changeing, and deleteing data.
  

  
  Check out the following from MSDN.  I really get annoyed with that site as it doesn't give you what you want unless you ask it exactly what you are looking for.
  

  
  MS Access SQL Reference
  

  
  So if you want to create a table in a database all you need to do is:
  

  
  CREATE TABLE tableName
      (columnName varchar(20),
       columnName char(20),
       columnName integer)
  

  
  Add stuff to table:
  

  
  INSERT INTO tableName
      (columnName, IntegerColumnName)
      VALUES ('value', numericValue)
  

  
  

  
  

  Report abuse
• • Reply

  18 years ago

  by Tijger

  Jess H

  Pennsylvania, United States

  Joined 19 years ago

  That would help me if I was using SQL databases..  But I need to know how to edit Microsoft Access Databases with Visual Basic..
  

  
  My teacher found some stuff that helped.. But he also put us back in a rut..
  

  
  He wants us to find out if we could use a variable in place of a value.
  

  
  Public Sub AddData()
     Dim oCN As New Connection
     Dim oRS As New Recordset
     Dim sConnect As String
     Dim sSQL As String
     Dim sCompanyName As String
     
     sCompanyName = "Microworld"
     
     sConnect = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
     "Data Source =h:\visualbasic\database\northwind.mdb;" & "Persist Security Info =false"
     
     sSQL = "INSERT INTO Customers(CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax)" & _
     "VALUES ('MWRLD', sCompanyName, 'Bob Jones', 'President', '123 Anywhere Street', 'SomeCity', 'MO', '19346', 'USA', '(222) 555-1212', '(222) 555-1313')"
     
     Set oCN = New Connection
     oCN.Open sConnect
     oCN.Execute sSQL
     
  End Sub
  

  
  We want to know if sCompanyName would work as a value..
  Can anyone help me with this one?
  

  
  

  Report abuse
• • Reply

  18 years ago

  by jquzak

  jquzak

  , United States

  Joined 19 years ago

  Try this code on for size.  
  

  Quote:

  
  Public Sub AddData()
     Dim oCN As New Connection
     Dim oRS As New Recordset
     Dim sConnect As String
     Dim sSQL As String
     Dim sCompanyName As String
     
  '// Start of changes to add varible code
     Dim sProvider as string
     Dim sDatasource as string
     Dim sSecurityInfo as string
  

  
     sCompanyName = "Microworld"
     
     sConnect = "Provider = " & sProvider & _
     "Data Source =" & sDatasource & " & "Persist Security Info =" & sSecurityInfo
  '// End of changes
  

  
     sSQL = "INSERT INTO Customers(CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax)" & _
     "VALUES ('MWRLD', sCompanyName, 'Bob Jones', 'President', '123 Anywhere Street', 'SomeCity', 'MO', '19346', 'USA', '(222) 555-1212', '(222) 555-1313')"
     
     Set oCN = New Connection
     oCN.Open sConnect
     oCN.Execute sSQL
     
  End Sub
  

  Report abuse
• • Reply

  18 years ago

  by Tijger

  Jess H

  Pennsylvania, United States

  Joined 19 years ago

  What I need is to know if sCompanyName would work as a value.. to add a record with "Microworld" as the Company Name using the sCompanyName variable..  Is that even possible?
  

  
  

  Quote:

  Thanks for the help, but we've figured it out

  Report abuse
• • Reply

  18 years ago

  by Thorny

  K. R. Thorson

  Florida, USA, United States

  Joined 19 years ago

   sSQL = "INSERT INTO Customers(CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax)" & _
    "VALUES ('MWRLD', "  & sCompanyName  & ", 'Bob Jones', 'President', '123 Anywhere Street', 'SomeCity', 'MO', '19346', 'USA', '(222) 555-1212', '(222) 555-1313')"
  

  
  With the sCompanyName broken out as shown, the contents of the variable sCompanyName will be included int the insert command.
  

  
  ('')
  

  Report abuse