XTreme Logo

If you appreciate these tips, please consider a small donation:

Database Tip - Intermediate

How to open an ODBC Direct Work Space and create an updatable recordset
Applies to: Visual Basic 5.0

This tip will show you how to open the publishers table of the pubs database on Microsoft SQL Server with ODBC Direct.  Many people get confused about ODBC Direct recordsets.  By default an ODBC Recordset is opened read-only, you must supply all options in the OpenRecordset method in order to create a read/write recordset.

Create a new project in VB5 with a Module and a form.
On the Form place 5 text boxes and 7 command buttons.

Paste the following code into the General Declarations section of Form1:

Private Sub Form_Load()
    ' Create an ODBC WorkSpace
    Set ws = CreateWorkspace("ODBCWorkspace", "admin", "", dbUseODBC)
    ' set the default cursor driver - this is important
    'or you will get errors when you try to save your record.
    ws.DefaultCursorDriver = dbUseODBC 
    Workspaces.Append ws
    ' Open the Database.  Replace MySqlServer with whatever your server name is.
    Set db = ws.OpenDatabase("", False, False, _
        "odbc;server=MySqlServer;uid=sa;pwd=;database=pubs;driver={Sql Server};")
    'Open the Recordset.  This is the part that trips up most people.
    'You need to set all options.
    Set rs = db.OpenRecordset("Select * from publishers", dbOpenDynaset, dbExecDirect, dbOptimistic)
    ' This is a user defined procedure.  The code for this is in the module.
End Sub

'Navigation Buttons - MoveFirst, MoveLast, MoveNext, MovePrevious.
'You will notice the FillFields procedure being called at the end of all the
'navigation button procedures.  This updates the text boxes with the new

Private Sub cmdMoveFirst_Click()
End Sub

Private Sub cmdMoveLast_Click()
End Sub

Private Sub cmdMoveNext_Click()
    'trap to see if you have gone past the last record
    If rs.EOF Then
    End If
End Sub

Private Sub cmdMovePrevious_Click()
    'trap to see if you have gone before the first record.
    If rs.BOF Then
    End If
End Sub

'Add, Edit, Save Functionality
Private Sub cmdAdd_Click()
    Text1.Text = ""
    Text2.Text = ""
    Text3.Text = ""
    Text4.Text = ""
    Text5.Text = ""
End Sub

Private Sub cmdEdit_Click()
End Sub

Private Sub cmdSave_Click()
    ' This is a user defined function to write the data from
    ' the fields into the recordset.  This procedure is in the module

End Sub

'Module Code, paste into the General Declaration of a standard Module:
Public ws As Workspace
Public db As Database
Public rs As Recordset

Public Sub FillFields()
    Form1.Text1.Text = rs("pub_id") & ""
    Form1.Text2.Text = rs("pub_name") & ""
    Form1.Text3.Text = rs("city") & ""
    Form1.Text4.Text = rs("state") & ""
    Form1.Text5.Text = rs("country") & ""
End Sub

Public Sub WriteFields()
    rs("pub_id") = Form1.Text1.Text
    rs("pub_name") = Form1.Text2.Text
    rs("city") = Form1.Text3.Text
    rs("state") = Form1.Text4.Text
    rs("country") = Form1.Text5.Text
End Sub

[ Back To The Top ]

Contact: web@xtremecomp.com
All contents copyright XTreme Computing unless noted otherwise.