Thread: Queries
View Single Post
  #4 (permalink)  
Old September 8th, 2003, 08:58 AM
Bob Bedell Bob Bedell is offline
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

As far as your ADO recordset is concerned, be sure that your connection string knows how to locate your .mdb file, and that your cursor type and lock type properties are set to return an updateable cursor (the default cursor type is forward-only, and the default lock-type is read-only). Try something like this:

Private Sub Command0_Click()
  Dim cnn As ADODB.Connection
  Dim rst As ADODB.Recordset
  Dim strSQL As String

  Set cnn = CurrentProject.Connection
  Set rst = New ADODB.Recordset

  strSQL = "SELECT * FROM tblCustomers"

  ' Open an updateable recordset
  rst.Open strSQL, cnn, adOpenDynamic, adLockOptimistic

  ' Insert a new record
  rst.AddNew

  With rst
    !CustomerID = 1
    !FirstName = "John"
    !LastName = "Doe"

    ' Save changes to recordset
    .Update
  End With

  rst.Close

End Sub

Using adOpenDynamic with adLockOptimistic actually return an updateable keyset cursor (not a dynamic cursor). Jet doesn't support dynamic cursors. This just means that you can update the recordset, but you can't see changes made by other users in a multi-user environment until you refresh the cursor (e.g., reopening the recordset).

If you are using a literal character string for you connection string, make sure the path is correct:

Private Sub Command0_Click()
  Dim cnn As ADODB.Connection
  Dim rst As ADODB.Recordset
  Dim strSQL As String
  Dim strProvider As String
  Dim strDataSource As String

  Set cnn = New ADODB.Connection
  Set rst = New ADODB.Recordset

  strProvider = "Microsoft.Jet.OLEDB.4.0"
  strDataSource = CurrentProject.Path & "\MyDatabase.mdb"

  cnn.Open "Provider = " & strProvider & "; Data Source = " & strDataSource

  strSQL = "SELECT * FROM tblCustomers"

For your SQL criteria string, notice where the additional apostrophe's are placed in the following:

strCriteria = "[LastName] = '" & txtLastName & "'"

The original criteria expressioin:

"[LastName] = '" & txtLastName & "'"

evaluates as

"[LastName] = '" & "Doe" & "'"

which, when concatenated, evaluates as:

"[LastName] = 'Doe'"

If your criteria is numeric, use:

strCriteria = "[CustomerID] = " & txtCustomerID

The original criteria expression

"[CustomerID] = " & txtCustomerID

evaluates as

"[CustomerID] = " & 1

which, when concatenated, evaluates as:

"[CustomerID] = 1"

For date fields, use:

strCriteria = "[OrderDate] = #" & txtDate & "#"

HTH,

Bob

Reply With Quote