Real Newbie to DB

[phreaky]

I'm a real newbie to use DB, I mean, using them in coding, I know everything about its theories, but I need to know how to use them in VB, I already did the connection so I discovered how to make the realtionship between VB-ACCESS, but, what I need to know is how to work with the (accesing them, erase records, insert records, select records) without having to code every step of it, I have read lots of DB tutorials, and I know that using ADOs I don't have to do almost anything about those topics of inserting, deleting, etc., but, what I really need is an explanation of how to set ADOs, and how to use them correctly (combinating them with MSACCESS DBs), a good tutorial could help, I haven't found the info that I really need, the tutorials I have found doesn't work at all for my needs, they explain for SQL server or they don't explain as well as I need (I haven't worked with DB in VB), so, any help?

[a_jam_sandwich]

Using ADO the connection is the major bit about the type of database your connecting to the Record Set is the same for both

 

You create a Record Set by ...

 

' Create your connection
Dim MyConn as ADODB.Connection
' Create your Recordset 
Dim MyRs as ADODB.Recordset

MyConn = New ADODB.Connection
MyRs = New ADODB.recordset

' Open the Connection
MyConn.Open(Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\" & DatabaseName & ";Persist Security Info=False)

' Open The Recordset based on the SELECT Query
MyRs.Open("SELECT * FROM MyTABLE", MyConn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic)

While Not MyRs.EOF
' Code to look at the returned recordset
' e.g. console.writeline(MyRs.Fields("LASTNAME").Value)
Wend

' Close all
MyRs.Close
MyConn.Close

 

Hope this helps

[Robby]

Shouldn't we move away from using COM?

 

here a simple function that Updates a record in Access,

You can alter the function to Insert Into or Delete records.

   Private Function SetTableValue(ByVal nID As Integer, ByVal sField As String, ByVal nValue As Integer, ByVal sTable As String) As Integer
       Dim SqlCMD As OleDbCommand
       Dim SqlCN As New OleDbConnection(Conn)
       Dim recordsAffected As Integer, strSql As String
       Try
           strSql = "UPDATE " & sTable & " SET " & sField & " = " & nValue & " WHERE ID = " & nID
           If SqlCN.State = ConnectionState.Closed Then SqlCN.Open()
           SqlCMD = New OleDbCommand(strSql, SqlCN)
           recordsAffected = SqlCMD.ExecuteNonQuery()
       Catch
           recordsAffected = -1
       Finally
           If SqlCN.State = ConnectionState.Open Then SqlCN.Close()
           If Not SqlCMD Is Nothing Then SqlCMD.Dispose()
       End Try
       Return recordsAffected
   End Function

[Robby]

by the way, you need to "Imports System.Data.OleDB"

 

 

Also, most of the samples you find may use SQL Server, you can usually just change SqlCommand to OleDbCommand or SqlConnection to OleDbConnection, etc...

[a_jam_sandwich]

Thax for the information as regards not to using COM object iv'e only start VB.NET 3 days ago still learing the fundimentals

 

:)