I have a Access 2000 database that is linked to some tables on a SQL 2005 Express database (over a VPN connection). I am pulling down a subset of records to a Access table, then updating a field in the SQL database (I am going to set it up to only pull 10 to 25 records at a time, every 30/60 minutes). My question is what is the best cursor and lock type to use for this?
I do not care if the recordset data changes after I get my recordset. I only need to pull that records data at that particular time. My main concern is to ensure the connection does not lock records for too long. I was using adOpenKeyset previously and we experienced some application issues. Before I try this again I need to make sure I am using the best lock for what I am trying to do. Thanks in advance!
'Getting records from SQL
strSQL1 = "SELECT temp_dbo_Dictations.JobNumber, temp_dbo_Dictations.DAccount," & _
"temp_dbo_Dictations.LineCountBilling," & _
"temp_dbo_Dictations.LineCountPayroll,Transcribers.TranscriberID,Physicians.PhysicianID," & _
"temp_dbo_Dictations.dtScribed,temp_dbo_Dictations.dtCurrent" & _
" FROM (temp_dbo_Dictations LEFT JOIN Physicians" & _
" ON temp_dbo_Dictations.DictatorID = Physicians.SQLPhyID)" & _
" LEFT JOIN Transcribers ON temp_dbo_Dictations.TranID = Transcribers.SQLTranID" & _
" WHERE ((UserField1 = 0) AND (State >= 15)" & _
" AND (LineCountBilling > 0) AND (dtScribed Is Not Null))"
rs.Open strSQL1, c, adOpenStatic, adLockOptimistic 'my attempt, this is where I need help??
Do Until rs.EOF
JobNumber = rs("JobNumber")
Account = rs("dAccount")
LineCountBilling = rs("LineCountBilling")
LineCountPayroll = rs("LineCountPayroll")
dtScribed = Format(rs("dtScribed"), "mm/dd/yyyy")
'Inserting record into Access Table
strSQL2 = "Insert INTO SQLDictations(JobNumber,Account," & _
"PhysicianID,TranscriberID,LineCountBilling,dtScribed,LineCountPayroll,ImportDate)" & _
"VALUES (" & JobNumber & "," & Account & ", " & PhysicianID & "," & _
"" & TranscriberID & "," & LineCountBilling & "," & _
"#" & dtScribed & "#," & LineCountPayroll & "," & _
"#" & ImportDate & "#)"
DoCmd.RunSQL strSQL2
'Getting Record Key Created for New Record
Dim rst As New ADODB.Recordset
rst.Open "Select MAX(ImportID) from SQLDictations", c, adOpenKeyset, adLockOptimistic
AccessImportID = rst(0)
rst.Close
Set rst.ActiveConnection = Nothing
'Updating SQL table with the Access Record Key
strSQL3 = "UPDATE temp_dbo_Dictations SET UserField1 = " & AccessImportID & "" & _
" Where JobNumber = " & JobNumber & ""
DoCmd.RunSQL strSQL3
rs.MoveNext
Loop
....
I do not care if the recordset data changes after I get my recordset. I only need to pull that records data at that particular time. My main concern is to ensure the connection does not lock records for too long. I was using adOpenKeyset previously and we experienced some application issues. Before I try this again I need to make sure I am using the best lock for what I am trying to do. Thanks in advance!
'Getting records from SQL
strSQL1 = "SELECT temp_dbo_Dictations.JobNumber, temp_dbo_Dictations.DAccount," & _
"temp_dbo_Dictations.LineCountBilling," & _
"temp_dbo_Dictations.LineCountPayroll,Transcribers.TranscriberID,Physicians.PhysicianID," & _
"temp_dbo_Dictations.dtScribed,temp_dbo_Dictations.dtCurrent" & _
" FROM (temp_dbo_Dictations LEFT JOIN Physicians" & _
" ON temp_dbo_Dictations.DictatorID = Physicians.SQLPhyID)" & _
" LEFT JOIN Transcribers ON temp_dbo_Dictations.TranID = Transcribers.SQLTranID" & _
" WHERE ((UserField1 = 0) AND (State >= 15)" & _
" AND (LineCountBilling > 0) AND (dtScribed Is Not Null))"
rs.Open strSQL1, c, adOpenStatic, adLockOptimistic 'my attempt, this is where I need help??
Do Until rs.EOF
JobNumber = rs("JobNumber")
Account = rs("dAccount")
LineCountBilling = rs("LineCountBilling")
LineCountPayroll = rs("LineCountPayroll")
dtScribed = Format(rs("dtScribed"), "mm/dd/yyyy")
'Inserting record into Access Table
strSQL2 = "Insert INTO SQLDictations(JobNumber,Account," & _
"PhysicianID,TranscriberID,LineCountBilling,dtScribed,LineCountPayroll,ImportDate)" & _
"VALUES (" & JobNumber & "," & Account & ", " & PhysicianID & "," & _
"" & TranscriberID & "," & LineCountBilling & "," & _
"#" & dtScribed & "#," & LineCountPayroll & "," & _
"#" & ImportDate & "#)"
DoCmd.RunSQL strSQL2
'Getting Record Key Created for New Record
Dim rst As New ADODB.Recordset
rst.Open "Select MAX(ImportID) from SQLDictations", c, adOpenKeyset, adLockOptimistic
AccessImportID = rst(0)
rst.Close
Set rst.ActiveConnection = Nothing
'Updating SQL table with the Access Record Key
strSQL3 = "UPDATE temp_dbo_Dictations SET UserField1 = " & AccessImportID & "" & _
" Where JobNumber = " & JobNumber & ""
DoCmd.RunSQL strSQL3
rs.MoveNext
Loop
....
