Add New using ADO

[Steven Deetz]

I am finishing up developing an Access 2003 FE and SQL Server 2000 BE project and came across something I have never before seen. I routinely use ADO to add a new record to tables residing on the SQL Server BE. I have created an unbound form and added the ADO code to post the contents of the form fields into a single table. I have added basic error handling and a Msgbox at the end of the code to tell the user the information has been successfully posted.

Herein lies the problem, no errors occur and the message box states that the information has been posted to the SQL Server Table but in checking the table the record has not been added. I have stepped through the code to trace it's execution and it appears to work just fine like the other pieces of code in the application.

I can post other pieces of information to other tables just fine with the ADO connection that I am using, I can create an Access Append Query and post just fine to the table in question. I am really stumped as to what I am missing. Below is the code for the attempt to Add New to the SQL Server table. DAO executes to append an Access table that serves as a temp table first and then ADO executes to append to SQL Server.

Thanks in advance for any suggestions!

Private Sub CreatePayment_Click()
On Error GoTo CreatePaymentErr

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rstADO As ADODB.Recordset
Dim strSubName As String
Dim strSubID As String
Dim strMainID As String
Dim strDepID As String
Dim strProcCat As String
Dim strTranCat As String
Dim ckIns As Boolean
Dim intDue As Integer
Dim curAmt As Currency
Dim curFee As Currency
Dim strProvider As String
Dim strProvID As String
Dim strNotes As String
Dim strEmp As String
Dim strSQL As String
Dim strSQLADO As String
Dim fOK As Boolean

If MsgBox("Do you wish to add this Subscriber?", vbYesNo, "Add To Reoccurring Payments List") = vbNo Then
GoTo CreatePaymentExit
End If


'Set the variables
strSubID = Me.cboMedichargeSubscriberID
strSubName = Me.SubscriberName
strMainID = Me.MainMedichargeID
strProcCat = Me.cboMedicalBillCode
strTranCat = Me.TransactionType
If Not IsNull(Me.ckInsurance) Then
ckIns = Me.ckInsurance
End If
intDue = Me.DayDue
curAmt = Me.Amount
curFee = Me.FeeAmount
strProvider = Me.ProviderName
strProvID = Me.cboProviderID
strNotes = Me.SpecialNotes
strEmp = Me.CPNY
strDepID = Me.DependantNumber

'Put into temp table

strSQL = "SELECT * FROM temp_ReoccurringPayment"
Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)

rst.AddNew
rst!MediChargeSubscriberID = strSubID
rst!LastName = strSubName
rst!MainMedichargeID = strMainID
rst!MedicalCategory = strProcCat
rst!TransactionType = strTranCat
rst!InsurancePayment = ckIns
rst!DayDue = intDue
rst!Amount = curAmt
rst!FeeAmount = curFee
rst!ProviderName = strProvider
rst!MediChargeProviderID = strProvID
rst!SpecialNotes = strNotes
rst!CPNY = strEmp
rst!DependantNumber = strDepID

rst.Update
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing


'Put into Main Table

fOK = OpenConnection()

If Not fOK Then
Err.Raise 99999
End If

Set rstADO = New ADODB.Recordset
strSQLADO = "SELECT * FROM tbl_ReoccurringPayments"
' WHERE MedichargeSubscriberID = " & "'" & strSubID & "'"

rstADO.Open strSQLADO, cnnlocal, adOpenDynamic, adLockBatchOptimistic

rstADO.AddNew
rstADO!MediChargeSubscriberID = strSubID
rstADO!MedicalCategory = strProcCat
rstADO!InsurancePayment = ckIns
rstADO!DayDue = intDue
rstADO!Amount = curAmt
rstADO!FeeAmount = curFee
rstADO!MediChargeProviderID = strProvID
rstADO!SpecialNotes = strNotes
rstADO!CPNY = strEmp

rstADO.Update
rstADO.Requery

rstADO.Close
Set rstADO = Nothing

DoCmd.GoToControl "CloseThisForm"
Me.CreatePayment.Enabled = False
DoCmd.Requery


MsgBox "The Subscriber " & strSubName & " has been added!", , "Add To Reoccurring Payments List"

CreatePaymentExit:
Exit Sub

CreatePaymentErr:
Select Case Err.Number
Case 99999
MsgBox "The connection has failed to the main database. Please try again!", , "ADO Connection Failure"
GoTo CreatePaymentExit
Case Else
MsgBox Err.Description, , Err.Number
GoTo CreatePaymentExit
End Select


End Sub

 

[boblarson]

I am not in the same location as my book (The Access Developer's Guide to SQL Server), so I can't give you specifics. But, basically the thing to note is that if you are connecting to SQL Server via Access.mdb and you are performing operations in SQL Server via this, you need to be able to specifically code to return SQL Errors, which don't get raised in Access so you will never know about them unless you specifically code for them.

I wish I had the book available to help. Maybe someone here has that info memorized, but I don't as I haven't needed to remember that yet. But, I have read through that section of the book and realize that for certain operations you can't rely on Access to notify you.

Good luck with it.