XTreme Computing VB Tips: Updatable ODBC Direct Workspace

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)
   rs.MoveFirst
    ' This is a user defined procedure. The code for this is in the module.
  FillFields
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
'information.
Private Sub cmdMoveFirst_Click()
rs.MoveFirst
FillFields
End Sub

Private Sub cmdMoveLast_Click()
rs.MoveLast
FillFields
End Sub

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

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

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

Private Sub cmdEdit_Click()
rs.Edit
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
    WriteFields
    rs.Update
    rs.Requery
    FillFields
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