Access application fronting a SQL Server database. We need to pull in information from an application written in Infopath that stores data in XML/Sharepoint, and then store that info in a SQL Server table. We developed a view to read the XML data and it seems to work.....kinda. We are consistantly getting E-FAIL errors at random times; they might occur on the 2nd record read, on the 50th record read, etc.
Here's the code. I've cut non-essential stuff out and added some comments specifically for this question IN ALL CAPS.
Dim MCISConn As New ADODB.Connection
Dim rsPatient As New ADODB.Recordset
Dim rsSource as New ADODB.Recordset
' open the connection
' THIS IS THE CONNECTION TO THE XML DATA. IT POINTS TO THE SHAREPOINT SERVER
.CursorLocation = adUseServer
.ConnectionString = "Provider=Microsoft.Access.OLEDB.10.0;Persist Security Info=False;Data Source=sqlsvr03;Integrated Security=SSPI;Initial Catalog=PatientDirectory;Data Provider=SQLOLEDB.1"
' THIS IS THE SPECIFIC VIEW THAT WAS CREATED TO PULL THE XML DATA
' THE WHERE CLAUSE DOES NOT APPEAR TO WORK - IT LOOKS LIKE WE ARE GETTING ALL
' OF THE DATA
SQL = "SELECT * FROM Patient_List_IncidentReporting P "
SQL = SQL & "WHERE P.admissiondate > '" & sDate & "' "
rsSource.Open SQL, MCISConn ,adOpenForwardOnly
Do While Not rsSource.EOF
' THIS CHECK IS HERE BECAUSE OF THE ISSUE WITH THE WHERE CLAUSE NOT WORKING
If rsSource!admissiondate <= CutoffDate Then
' check to see if we already have this guy in the patient table
Set rsPatient = Nothing
SQL = "select * from TBL_PATIENT_DATA where patient_id='" & rsSource!patientid & "' "
SQL = SQL & "and episode_id=" & rsSource!episode
' GCONN IS DEFINED GLOBALLY, AND IS CURRENTPROJECT.CONNECTION
rsPatient.Open SQL, gConn, adOpenKeyset, adLockOptimistic
If rsPatient.EOF Then
' no, so add him in
Debug.Print "adding " & rsSource!patientid & " episode " & rsSource!episode
!patient_id = rsSource!patientid
!episode_id = rsSource!episode
!lname = rsSource!lastname
!fname = rsSource!firstname
!mname = rsSource!middlename
!dob = rsSource!birthdate
!sex = rsSource!sex
!Status = rsSource!admissionstatus
!date_enrolled = rsSource!admissiondate
!date_discharged = rsSource!dischargedate
!addr_1 = rsSource!address1
!addr_2 = rsSource!address2
!city = rsSource!city
!state = rsSource!state
!zip = rsSource!postalcode
!home_phone = rsSource!homephone
sName = Trim$(rsSource!lastname & ", " & rsSource!firstname)
' remove any single quotes in the name
i = InStr(sName, "'")
If i > 0 Then
sName = Mid$(sName, 1, i - 1) & Mid$(sName, i + 1)
' and load the name into the lookup table
SQL = "INSERT INTO TBL_PATIENT_NAME_DROPDOWN ( patient_id, patient_name) "
SQL = SQL & "values ('" & rsSource!patientid & "','" & sName & "')"
' THE ERROR OCCURS HERE ON THE MOVENEXT. AS MENTIONED, IT MIGHT HAPPEN AFTER
' A FEW RECORDS,
' OR AFTER 100 OR SO RECORDS
' Data provider or other service returned an E_FAIL status.
Set rsPatient = Nothing
Thanks in advance!