-
Aug 17th, 2010, 02:13 PM
#1
Thread Starter
Addicted Member
[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!!!
-
Aug 17th, 2010, 07:48 PM
#2
Re: query than adding to DB?
You could use the sql instruction 'Insert' to add new record to db and the 'Execute' method
JG
-
Aug 18th, 2010, 02:49 AM
#3
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.
-
Aug 18th, 2010, 09:46 AM
#4
Thread Starter
Addicted Member
Re: query than adding to DB?
Originally Posted by si_the_geek
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!!!
-
Aug 18th, 2010, 10:17 AM
#5
Re: query than adding to DB?
Originally Posted by aa9gg
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.
-
Aug 18th, 2010, 12:57 PM
#6
Thread Starter
Addicted Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|