Results 1 to 6 of 6

Thread: [RESOLVED] query than adding to DB?

  1. #1

    Thread Starter
    Addicted Member aa9gg's Avatar
    Join Date
    Apr 2009
    Location
    Chicagoland
    Posts
    184

    Resolved [RESOLVED] query than adding to DB?

    Maybe a dumb question...but I'm confused. I have a parent/child DB with a 1 to many relationship. I open them as such:
    Code:
    Public Sub Open_DB()
        Set cn = New ADODB.Connection   'instantiate the connection object
        'specify the connectionstring
         cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                              "Data Source=" & App.Path & "\Database\csv1000_8.mdb"
       
        cn.Open  'open the connection
        
        'instantiate the recordset object
        Set P_rs = New ADODB.Recordset
            P_rs.PageSize = 10
            P_rs.CacheSize = 10
    
        Set C_rs = New ADODB.Recordset
            C_rs.PageSize = 10
            C_rs.CacheSize = 10
    
        'open the recordset
        With P_rs
            .Open "Patient_Info", cn, adOpenKeyset, adLockPessimistic, adCmdTable
            If Not (.EOF And .BOF) Then
                P_rs.MoveLast     'go to the last record if there are existing records
            End If
        End With
        
        With C_rs
            .Open "Patient_Tests", cn, adOpenKeyset, adLockPessimistic, adCmdTable
            If Not (.EOF And .BOF) Then
                C_rs.MoveLast     'go to the last record if there are existing records
            End If
        End With
        
    End Sub
    My question is that when I do a query like this:
    Code:
    Public Sub Search_Dup_ID()
     ' Dupe_Flag : 0 = None
     '             1 = Dup
     '             2 = Valid Match
     
      Dupe_Flag = 0  'set if duplicate/match id is found
      
      'build SQL string to look for ID
      strSQL = "SELECT * FROM Patient_Info"
      strSQL = strSQL & " WHERE PATIENT_ID LIKE '" & Patient_Info5.patient_id.Text & "'"
      
      If P_rs.State = adStateOpen Then
       P_rs.Close  'close the recordset (required before reloading it)
      End If
      
      P_rs.Open strSQL, cn, adOpenKeyset, adLockPessimistic, adCmdText   'begin DB search
    
      If P_rs.EOF Then
       'Reached EOF without finding ID
           Dupe_Flag = 0
           MsgBox ("ID does NOT Exist")
       Else  ' Not at EOF...ID found in database
         If ((Patient_Info5.first_name.Text <> P_rs.Fields("PATIENT_FIRST_NAME")) And _
             (Patient_Info5.last_name.Text <> P_rs.Fields("PATIENT_LAST_NAME"))) Then
              
               MsgBox ("ID Exists, Different Patient...Select New ID")
               Dupe_Flag = 1
          Else
            MsgBox ("Existing Record Update") ' matching record found in database
            Dupe_Flag = 2
         End If
      End If
    
    End Sub
    If I want to add a new record to the end of the DB, do I have to call Open_DB() again because of the SQL test I just did?
    FCC Section 97.313(a) “At all times, an amateur station must use the minimum transmitter power necessary to carry out the desired communications.”

    I'd rather run a "Killer-Watt" than a KiloWatt - QRP Rules!!!

  2. #2
    Frenzied Member
    Join Date
    May 2006
    Location
    some place in the cloud
    Posts
    1,886

    Re: query than adding to DB?

    You could use the sql instruction 'Insert' to add new record to db and the 'Execute' method

    JG

  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: query than adding to DB?

    If you want to use P_rs again as you did before calling the Search_Dup_ID sub, you should either re-open it at the end of Search_Dup_ID (as you did in Open_DB), or better still don't use it in Search_Dup_ID at all - instead use a different recordset declared inside that sub.

  4. #4

    Thread Starter
    Addicted Member aa9gg's Avatar
    Join Date
    Apr 2009
    Location
    Chicagoland
    Posts
    184

    Red face Re: query than adding to DB?

    Quote Originally Posted by si_the_geek View Post
    If you want to use P_rs again as you did before calling the Search_Dup_ID sub, you should either re-open it at the end of Search_Dup_ID (as you did in Open_DB), or better still don't use it in Search_Dup_ID at all - instead use a different recordset declared inside that sub.
    So yes I would currently have to call Open_DB after the Search_Dup_ID?

    By declaring another recordset (Test_rs) in Search_Dup_ID, and use that for the test...if I find a valid match (Dupe_Flag = 2) I need to save data. Do I do it with this new recordset? How do I point P_rs to the same location?
    FCC Section 97.313(a) “At all times, an amateur station must use the minimum transmitter power necessary to carry out the desired communications.”

    I'd rather run a "Killer-Watt" than a KiloWatt - QRP Rules!!!

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: query than adding to DB?

    Quote Originally Posted by aa9gg View Post
    So yes I would currently have to call Open_DB after the Search_Dup_ID?
    No, definitely not - that will just cause problems.

    If you re-use the recordset (and I recommend you don't), you should only be closing and re-opening the recordset. There is no reason to also repeat the Set lines, or re-open the connection, etc - all of which take time, and are very likely to cause problems (unless you also close everything properly first, which takes more time).
    By declaring another recordset (Test_rs) in Search_Dup_ID, and use that for the test...if I find a valid match (Dupe_Flag = 2) I need to save data. Do I do it with this new recordset? How do I point P_rs to the same location?
    It is hard to be sure without knowing what the details of "save data" are, but should probably just use the new recordset to do the check - as is being done in the Search_Dup_ID routine already.

    To save data you could use a recordset, or one of the other methods such as an Insert/Update statement via the connection object.

  6. #6

    Thread Starter
    Addicted Member aa9gg's Avatar
    Join Date
    Apr 2009
    Location
    Chicagoland
    Posts
    184

    Re: query than adding to DB?

    ok...I think I understand and have the new recordset working. thanks guys
    FCC Section 97.313(a) “At all times, an amateur station must use the minimum transmitter power necessary to carry out the desired communications.”

    I'd rather run a "Killer-Watt" than a KiloWatt - QRP Rules!!!

Posting Permissions

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



Click Here to Expand Forum to Full Width