Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ADO AddNew not Adding New (VB6 / Access 97)

    I've attached the Sub in which I'm having the problem. It completes successfully, however, it does not add the data to the underlying Access database.


    Private Sub AttachFile()

    Dim rsPath As New ADODB.Recordset

    Dim FSO As New FileSystemObject

    Dim OpenFile As New cFileDialog

    Dim rs As New ADODB.Recordset

    Dim cn As New ADODB.Connection

    Dim FileName As String

    Dim iSeq As Integer


    If Me.tdbMRN = "" Or IsNull(Me.tdbMRN) Then
    MsgBox "Medical Record Number Required to Attach Record", vbCritical, "Required Field"
    Exit Sub
    End If


    OpenFile.Filter = "*.PDF"

    OpenFile.Show (True)

    FileName = OpenFile.FileName

    cn.Open "Provider=Microsoft.jet.oledb.3.51;" _
    & "Data Source=CatabaseOncology.mdb"

    rsPath.Source = "tblPath"

    rsPath.Open , cn, adOpenDynamic, adLockBatchOptimistic


    'Set rsPath = CurrentDb.OpenRecordset("tblPath")

    If FileName = "" Then
    Exit Sub
    End If


    With rsPath
    .AddNew
    !MRNumber = tdbMRN.Text
    !seq = 9999
    !Path = FileName
    ' .Update
    End With


    'If MsgBox("Open file " & FileName & " ? ", vbYesNo, "SheaWare 2001") = vbYes Then


    'End If




    If LoadFormData(tdbMRN.Text) Then

    Me.lblStatus.Caption = "File Attached to MR # " & tdbMRN.Text & " Successfully"

    Else

    Me.lblStatus.Caption = "Error Attaching File"

    End If

    Set rsPath = Nothing
    Set cn = Nothing
    Set FSO = Nothing
    Set OpenFile = Nothing


    End Sub

  2. #2
    Star Lounger
    Join Date
    Nov 2001
    Location
    Illinois, USA
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO AddNew not Adding New (VB6 / Access 97)

    Hi Mike,
    After your recordset is opened try a debug.print or msgbox to show the values of rs.cursortype and rs.locktype. Compare the values retrieved with the constants adOpenDynamic and adLockBatchOptimistic as shown in the Object Browser. Check out MSDN library info on ADO and the Jet OLEDB provider. If you ask for a type of recordset not supported by Jet it will substitute something else; this may be causing your problem. Also, did you mean to leave the .Update statement commented out? I think this will take you to a good starting point in your search: http://msdn.microsoft.com/library/default....date_topic5.asp

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ADO AddNew not Adding New (VB6 / Access 97)

    Just because you didn't get an error message, doesn't mean you didn't get any errors. One of the more frustrating aspects of ADO is that you have to monitor the connection object's errors collection, which is not the same as the database application's errors. You need to add error handling to test for cn.Errors.Count > 0 and then loop through the collection to see any errors. Then you need to clear the errors collection because unlike the Jet err object, it doesn't clear itself on a resume instruction.
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO AddNew not Adding New (VB6 / Access 97)

    Thanks, Dottie and Charlotte.

    I will try the debug and see what is actually going on with the recordset object pertaining to the locking and adOpenDynamic like you suggested. I'll also look up some methods of handling errors occurring with the connection object, which is really good to know that this can occur without any feedback from the application. I will check out that URL you sent me.

    I've actually just dove into ADO, trying to force myself to use it to get up with the times. I've been creating and using the same connection object in multiple places and am wondering if that might be the source of my problem. I've attached my entire code below. If you wouldn't mind just looking at how I'm using the connection objects, I'd be interested in any input from you and Charlotte.

    Also, Dottie, I uncommented the .Update method. I had it commented out because my reference book said that ADO would do the update automatically and was just trying different things.

    Code is as follows:



    Private Function LoadFormData(sMRN_Number As Long) As Boolean

    Dim rs As New ADODB.Recordset

    Dim cn As New ADODB.Connection

    Dim Itm As ListItem


    On Error GoTo ErrorHandler

    'If lvwRM.ListItems.Count > 0 Then
    lvwRM.ListItems.Clear
    'End If


    cn.Open "Provider=Microsoft.jet.oledb.3.51;" _
    & "Data Source=CatabaseOncology.mdb"

    rs.Source = "SELECT DISTINCT tblPath.MRNumber, tblPath.Seq, tblPath.Path " _
    & "FROM tblPath " _
    & "WHERE tblPath.MRNumber = " & sMRN_Number



    rs.Open , cn, adOpenStatic, adLockBatchOptimistic



    Debug.Print rs.RecordCount

    If rs.RecordCount > 0 Then

    With lvwRM
    Do While Not rs.EOF

    Set Itm = .ListItems.Add(, , rs(1) & vbNullString)
    Itm.SubItems(1) = rs(2) & vbNullString

    rs.MoveNext
    Loop
    End With

    lblStatus.Caption = "Record Retrieved"

    LoadFormData = True

    Else

    MsgBox "Record not on file", vbCritical, "Search Results"
    tdbMRN.SetFocus

    LoadFormData = False

    End If




    Set Itm = Nothing
    Set rs = Nothing
    Set cn = Nothing

    Exit Function

    ErrorHandler:

    MsgBox "Error #" & Err.Number & " " & Err.Description
    LoadFormData = False

    End Function

    Private Sub cmdAttach_Click()

    Call AttachFile

    End Sub

    Private Sub cmdSearch_Click()

    If Not (IsNull(tdbMRN) Or tdbMRN = "") Then
    LoadFormData (tdbMRN)
    Else
    MsgBox "MRN Number Required for Search", vbCritical, "Required Field"
    End If


    End Sub


    Private Function IsSel() As Boolean

    Dim intX As Integer
    On Error Resume Next ' If an integer isn't entered.

    intX = InputBox("Check Item", , lvwRM.SelectedItem.Index)

    If IsNumeric(intX) Then ' Ensure an integer was entered.

    If lvwRM.ListItems(intX).Selected = True Then
    Debug.Print lvwRM.ListItems(intX).Text & " is selected."
    Else
    Debug.Print "Not selected"
    End If

    End If




    End Function

    Private Sub cmdUp_Click()
    IsSel
    End Sub

    Private Sub tdbMRN_KeyDown(KeyCode As Integer, Shift As Integer)

    If KeyCode = 13 Then
    LoadFormData (tdbMRN)
    tdbMRN.SelStart = 0
    tdbMRN.SelLength = Len(tdbMRN.Text)

    End If


    End Sub

    Private Sub AttachFile()

    Dim rsPath As New ADODB.Recordset

    Dim FSO As New FileSystemObject

    Dim OpenFile As New cFileDialog

    Dim rs As New ADODB.Recordset

    Dim cn As New ADODB.Connection

    Dim FileName As String

    Dim iSeq As Integer


    If Me.tdbMRN = "" Or IsNull(Me.tdbMRN) Then
    MsgBox "Medical Record Number Required to Attach Record", vbCritical, "Required Field"
    Exit Sub
    End If


    OpenFile.Filter = "*.PDF"

    OpenFile.Show (True)

    FileName = OpenFile.FileName

    cn.Open "Provider=Microsoft.jet.oledb.3.51;" _
    & "Data Source=CatabaseOncology.mdb"

    rsPath.Source = "tblPath"

    rsPath.Open , cn, adOpenDynamic, adLockBatchOptimistic


    'Set rsPath = CurrentDb.OpenRecordset("tblPath")

    If FileName = "" Then
    Exit Sub
    End If


    With rsPath
    .AddNew
    !MRNumber = CLng(tdbMRN.Text)
    !seq = 9999
    !Path = FileName
    .Update
    End With


    'If MsgBox("Open file " & FileName & " ? ", vbYesNo, "SheaWare 2001") = vbYes Then


    'End If




    If LoadFormData(tdbMRN.Text) Then

    Me.lblStatus.Caption = "File Attached to MR #" & tdbMRN.Text & " Successfully"

    Else

    Me.lblStatus.Caption = "Error Attaching File"

    End If

    Set rsPath = Nothing
    Set cn = Nothing
    Set FSO = Nothing
    Set OpenFile = Nothing


    End Sub

  5. #5
    Star Lounger
    Join Date
    Nov 2001
    Location
    Illinois, USA
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO AddNew not Adding New (VB6 / Access 97)

    Hi Mike,
    For just "diving into" ADO you certainly have accomplished a lot, and I think you really have already identified the cause of one of your problems being the connection object itself getting used in multiple places with the updates, opens, and closes, not quite in sync. I have just gone through your code and have some ideas that I hope will help you get past some of the remaining sticking points.
    I'm pretty sure that the lock method is what is preventing the update from occurring at all. adLockBatchOptimistic is used in conjunction with the UpdateBatch method; this is used for handling disconnected recordsets, so in your future studies you will definitely want to read about why and how disconnected recordsets are used. You want adLockOptimistic for your current situation; that will work with the Update method. BTW: You are right about ADO automatically running the Update method, but this happens when you move off the record. Closing the recordset should have the same result as navigating to another record, but I just thought it was worth explicitly using Update to see if it shed any light on your troubles. As for using the same connection object in multiple places: leaving it as in your posted code, will cause the statement " If LoadFormData(tdbMRN.Text) Then" to always give your "Error Updating File" message even when the update succeeds after your locking is fixed. It seems the two "cn" objects are out of sync. A couple of approaches to fix this are: 1) close connection, recordset, set both to nothing BEFORE invoking your LoadFormData method. OR 2) Use one connection object declared in the form's general declaration area. Close it and set it to nothing in the form's terminate event; you will have to decide the best spot for opening it. When using one connection object in several places, you'll also have to check the state as in If cn.State = adStateOpen then ..... before attempting to close it as well as to avoid getting an error for trying to open one that is already open. There are lots of considerations for determining the best way of handling connections. I am glad that you will followup on Charlotte's suggestion about the Errors collection too; looking at those errors will help you learn more about what is happening from the database's point of view. Good luck!

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO AddNew not Adding New (VB6 / Access 97)

    Dottie,

    Thank you for reviewing my code, and in such detail.

    I did some experimenting over the weekend with the connection objects and figured out that they were interfering with my other functions that also set connections and recordsets. Just like you said, I set them equal to Nothing before making calls to any other functions and it worked fine ever since.

    Also, (and again, like you said) I did some reading on Batch updates and trying to see how to integrate it into my existing architecture. I created a function that returns a disconnected recordset. I figured a calling procedure can use the data from this and the somehow do a batch update after the calling sub is done manipulating the data (I still have some reading to do, but I am assuming I would call another function to re-open the connection and then do the UpdateBatch).

    My preliminary code is as follows, and it does work for at least reading data from the datasource. Do I appear to be on the right track?:

    Public Function GetRS(sSource As String) As adodb.Recordset

    Dim rs As New adodb.Recordset

    Dim cn As New adodb.Connection

    cn.Open "Provider=Microsoft.jet.oledb.4.0;" _
    & "Data Source=CatabaseOncology2000.mdb"

    rs.Source = sSource
    rs.CursorLocation = adUseClient
    rs.CursorType = adOpenForwardOnly
    rs.LockType = adLockBatchOptimistic

    rs.Open , cn

    Set GetRS = rs

    Set rs.ActiveConnection = Nothing
    cn.Close

    Exit Function

  7. #7
    Star Lounger
    Join Date
    Nov 2001
    Location
    Illinois, USA
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO AddNew not Adding New (VB6 / Access 97)

    Mike,
    Looks good! You might want to change the cursor type to adOpenStatic in case any of your updates will be done to records out of sequence. You'll also need to trap for errors when you try to run the UpdateBatch method after reconnecting the recordset; if there are any conflicts during the update an error will be thrown. You'll want to determine a strategy for handling conflicts. In case you haven't already been to the section of MSDN's ADO Programmer's Guide dealing with batch updates, here is a link:
    <A target="_blank" HREF=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdconbatchmode.asp>http://msdn.microsoft.com/library/default....onbatchmode.asp</A>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •