Error ODBC Call Failed

[jinalpatel]

I have 4 tables in SQL 2000. I linked them in MS ACCESS 2003. One of the table is parent table havinf personal information. The other table stores Licenses. One person can have multiple license.

I created a query which joins both Parent and Child tables. In a datasheet view of this query if I add any record it will populate both the child and parent. This works fine.
I created a form which is bound to this query. I have two commands on this form. Add new record and Save record. I am posting code at the end.
When I click on add record it will clear all the fields and allow me to enter data. When I click on "Save Record", it takes some time and says "ODBC call failed"
Please help me with this.

Option Compare Database
Dim str As String
Dim namecode As String
        


Private Sub cmdsave_Click()
On Error GoTo Err_cmdsave_Click
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset

Set conn = New ADODB.Connection
Set rst = New ADODB.Recordset


rst.CursorType = adOpenKeyset
rst.LockType = adLockOptimistic

    rst.Open "Query3", CurrentProject.Connection

   If rst.Supports(adAddNew) Then
        With rst
            .AddNew
            .Fields("NAME_CODE") = namecode
             namecode = txtltype & txtlname & Left(Me.txtfirstname, 1) & txtmi
             Debug.Print namecode
            .Fields("LAST_NAME") = txtlname
            .Fields("FIRST_NAME") = txtfirstname
            .Fields("MIDDLE_INITIAL") = txtmi
            .Fields("Suffix") = txtsuffix
            .Fields("SSN") = txtssn
            .Fields("ADDRESS") = txtaddress1
            .Fields("ADDRESS2") = txtaddress2
            .Fields("CITY") = txtcity
            .Fields("STATE") = txtstate
            .Fields("ZIP_CODE") = txtzipcode
            .Fields("COUNTY") = txtcounty
            .Fields("TELEPHONE") = txtphone
            .Fields("BIRTH_DATE") = txtdob
            .Fields("SEX") = txtgender
            .Fields("DECEASED") = txtdeceased
            .Fields("SEX") = txtgender
            .Fields("HEIGHT") = txtheight
            .Fields("WEIGHT") = txtweight
            .Fields("EYE_COLOR") = txteyecolor
            .Fields("HAIR_COLOR") = txthaircolor
            .Fields("LICENSE_TYPE") = txtltype
            .Fields("SERIAL_NUMBER") = txtlnumber
            .Fields("PURCHASE_DATE") = txtpurchasedate
            .Fields("PURCHASE_AMOUNT") = txtpurchaseamount
            '.Fields("[dbo_PURCHASE_DATA].[NAME_CODE]") = namecode
            .Fields("STATUS") = txtstatus
            .Fields("STATUS_DATE") = txtstatusdate
            .Update
       End With
    End If
        
            
    MsgBox "Successfully added", vbOKOnly, "New record Added"
    rst.Close
    
    Set rst = Nothing
    'DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
 

Exit_cmdsave_Click:
    Exit Sub

Err_cmdsave_Click:
    MsgBox Err.Description
    Resume Exit_cmdsave_Click
    
End Sub
Private Sub cmdadd_Click()
On Error GoTo Err_cmdadd_Click

    DoCmd.GoToRecord , , acNewRec

Exit_cmdadd_Click:
    Exit Sub

Err_cmdadd_Click:
    MsgBox Err.Description
    Resume Exit_cmdadd_Click
    
End Sub

[NeoPa]

When adding records in a bound form you don't need to code anything in using the recordset. As soon as the operator navigates off the new (current) record the form will add it automatically.

[jinalpatel]

Yes you are right,but after adding one record it gives me error : #515 and #3621 ODBC connection failed. cannot add....like that.

I want make one thing clear here that my tables are in SQL. I linked them in Access 2003. Primary key of Customers table is Namecode, which is made up like "Licensetype"+" Lastname"+"Init ials" and for License table License number and licensetype uniquely identifies a license. They both are PK

So I created a query that joins Customer table to License table. and bound my form to that query. In a datasheet view of this query I can add as many records as I want..and it will automatically populate both license and customer table. BUT WHEN it comes to form all the errors attack me . It will add one record but then I am stuck with unsolved ODBC errors so that I have to close whole application.

One more : If a customer has more than one license, in customers table the entry will be done by his fisrt license type.
Like if he has 55 and 50 numbered license. and his name is John A Abbott

In customers table you will find : 55AbbottJA
and in license table : 55 12345(lno) 55AbbottJA (name_code)
50 14256(lno) 55AbbottJA

Please help:

I also tried the same with two insert queries, but it doesn't work ..

I appreciate your help!!

When adding records in a bound form you don't need to code anything in using the recordset. As soon as the operator navigates off the new (current) record the form will add it automatically.

[NeoPa]

Well, it's a bit hard to guess what's going on from what you've said so far, but I guess your linking of tables in the query (and possibly your db design itself) is not a correct match for what you're trying to do.

Please supply the following :

1. Meta-data for the Customer table
2. Meta-data for the License table
3. SQL for the query.

Here is an example of how to post table meta-data :

Table Name=[[U]tblLicense[/U]]
[I]Field; Type; IndexInfo[/I]
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

[jinalpatel]

Holder Data (like a Customer table)

Table Name=[[U]dbo_HOLDER_DATA[/U]]
[I]Field;         Type;   IndexInfo[/I]
Namecode       Text      PK
SSN            text      req
lastname       text      req
firstname      text      req
middleinitial  text      req
address        text      req
City           text      req
State          text      req
Zipcode        text      req
County         number    req
dob text       date/time req
deceased       text      req

Table Name=[[U]dbo_PURCHASE_DATA[/U]] 
[I]Field;         Type;   IndexInfo[/I]
Licensetype    number     PK
Serialno       number     PK (it's license no)
Purchasedate   date/time  req
Amount         currency   req
namecode       text       req
status         number     req
statusdate     date/time  req

SQL bound to form :
SELECT dbo_HOLDER_DATA.*,
       dbo_PURCHASE_DATA.LICENSE_TYPE,
       dbo_PURCHASE_DATA.SERIAL_NUMBER,
       dbo_PURCHASE_DATA.PURCHASE_DATE,
       dbo_PURCHASE_DATA.PURCHASE_AMOUNT,
       dbo_PURCHASE_DATA.STATUS,
       dbo_PURCHASE_DATA.STATUS_DATE
FROM dbo_PURCHASE_DATA LEFT JOIN
     dbo_HOLDER_DATA
  ON dbo_PURCHASE_DATA.NAME_CODE = dbo_HOLDER_DATA.NAME_CODE;

[NeoPa]

It may be ok while talking and explaining to someone to get the details wrong. In a technical post it is not. This wastes time and effort.

I assume (from other references and from reading the code) that the field is actually called NAME_CODE rather than the Namecode as you state in your table meta-data. Please be more careful in future.

Important Question:
When you (as the operator of the form) want to save a record on the form what do you do?

If you navigate to another record (the correct way) then we have a problem and you need to identify which line the code stops on (if it asks you to debug) for us to make any progress.

If you click on the cmdsave button and run through the code then you would certainly expect RecordSet problems as forms are not designed to work this way.

If you can answer the question then we can decide where to go from there (if the problem's not already resolved by then of course).

[jinalpatel]

Option Compare Database
Dim str As String
Dim namecode As String
Dim mfRequery As Boolean
   
Private Sub cmdsave_Click()
On Error GoTo Err_cmdsave_Click

namecode = txtltype & txtlname & Left(Me.txtfirstname, 1) & txtmi

Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim response As Integer
Dim rst1 As ADODB.Recordset

Set conn = New ADODB.Connection

Set rst1 = New ADODB.Recordset
rst1.CursorType = adOpenKeyset
rst1.LockType = adLockOptimistic


Set rst = New ADODB.Recordset
rst.CursorType = adOpenKeyset
rst.LockType = adLockOptimistic

    rst.Open "dbo_HOLDER_DATA", CurrentProject.Connection
   If rst.Supports(adAddNew) Then
        With rst
            .AddNew
            .Fields("NAME_CODE") = cboltype & txtlname & Left(Me.txtfirstname, 1) & txtmi
            .Fields("LAST_NAME") = txtlname
            .Fields("FIRST_NAME") = txtfirstname
            .Fields("MIDDLE_INITIAL") = txtmi
            .Fields("Suffix") = cbosuffix
            .Fields("SSN") = txtssn
            .Fields("ADDRESS") = txtaddress1
            .Fields("ADDRESS2") = txtaddress2
            .Fields("CITY") = txtcity
            .Fields("STATE") = txtstate
            .Fields("ZIP_CODE") = txtzipcode
            .Fields("COUNTY") = cbocounty
            .Fields("TELEPHONE") = txtphone
            .Fields("BIRTH_DATE") = txtdob
            .Fields("SEX") = txtgender
            .Fields("DECEASED") = txtdeceased
            .Fields("SEX") = txtgender
                If txtheight Is Not Null Then
                    .Fields("HEIGHT") = txtheight
                Else
                    .Fields("HEIGHT") = ""
                End If
                If txtweight Is Not Null Then
                    .Fields("WEIGHT") = txtweight
                Else
                    .Fields("WEIGHT") = ""
                End If
                If cboeyecolor Is Not Null Then
                    .Fields("EYE_COLOR") = cboeyecolor
                Else
                    .Fields("EYE_COLOR") = ""
                End If
                If cbohaircolor Is Not Null Then
                    .Fields("HAIR_COLOR") = cbohaircolor
                Else
                    .Fields("HAIR_COLOR") = ""
                End If
                         
            .Update
       End With
    End If
    
rst1.Open "dbo_PURCHASE_DATA", CurrentProject.Connection
   If rst1.Supports(adAddNew) Then
        With rst1
            .AddNew
            .Fields("LICENSE_TYPE") = cboltype
            .Fields("SERIAL_NUMBER") = txtlnumber
            .Fields("PURCHASE_DATE") = txtpurchasedate
            .Fields("PURCHASE_AMOUNT") = txtpurchaseamount
            .Fields("NAME_CODE") = namecode
            .Fields("STATUS") = cbostatus
            .Fields("STATUS_DATE") = txtstatusdate
            .Update
        End With
    End If
    
    
response = MsgBox("Records added successfully..Do you want to add another record??", vbOKCancel)

    If response = vbOK Then
    
        cboltype = ""
        txtlnumber = ""
        cbostatus = ""
        txtpurchasedate = ""
        txtstatusdate = ""
        txtpurchaseamount = ""
        txtlname = ""
        txtfirstname = ""
        txtmi = ""
        cbosuffix = ""
        txtssn = ""
        txtaddress1 = ""
        txtaddress2 = ""
        txtcity = ""
        txtstate = ""
        txtzipcode = ""
        cbocounty = ""
        txtphone = ""
        txtdob = ""
        txtdeceased = ""
        txtgender = ""
        cbohaircolor = ""
        cboeyecolor = ""
        txtheight = ""
        txtweight = ""
    
    Else
    If response = vbCancel Then
   
      ' Undo the change.
      DoCmd.RunCommand acCmdUndo
    DoCmd.CancelEvent
      ' Cancel the update.
      Cancel = True
    Else
       
    DoCmd.OpenForm frmsearchbylname, acNormal
    End If
    
    End If
    

Exit_cmdsave_Click:
    Exit Sub

Err_cmdsave_Click:
    MsgBox Err.Description
    Resume Exit_cmdsave_Click
    
End Sub
Private Sub cmdadd_Click()

On Error GoTo Err_cmdadd_Click
    
cboltype = ""
txtlnumber = ""
cbostatus = ""
txtpurchasedate = ""
txtstatusdate = ""
txtpurchaseamount = ""
txtlname = ""
txtfirstname = ""
txtmi = ""
cbosuffix = ""
txtssn = ""
txtaddress1 = ""
txtaddress2 = ""
txtcity = ""
txtstate = ""
txtzipcode = ""
cbocounty = ""
txtphone = ""
txtdob = ""
txtdeceased = ""
txtgender = ""
cbohaircolor = ""
cboeyecolor = ""
txtheight = ""
txtweight = ""
 
'    DoCmd.GoToRecord , , acNewRec

Exit_cmdadd_Click:
    Exit Sub

Err_cmdadd_Click:
    MsgBox "Error Occured"
    Resume Exit_cmdadd_Click
    
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
 Dim strMsg As String
   Dim iResponse As Integer
        Dim strdelete As String
        
   ' Specify the message to display.
   strMsg = "Do you wish to save the changes?" & Chr(10)
   strMsg = strMsg & "Click Yes to Save or No to Discard changes."

   ' Display the message box.
   iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?")
   
   ' Check the user's response.
   If iResponse = vbNo Then
   
   strdelete = "DELETE dbo_HOLDER_DATA.NAME_CODE, dbo_HOLDER_DATA.SSN, dbo_HOLDER_DATA.LAST_NAME," & _
   "dbo_HOLDER_DATA.FIRST_NAME, dbo_HOLDER_DATA.MIDDLE_INITIAL, dbo_HOLDER_DATA.Suffix, dbo_HOLDER_DATA.ADDRESS," & _
   "dbo_HOLDER_DATA.ADDRESS2, dbo_HOLDER_DATA.CITY, dbo_HOLDER_DATA.STATE, dbo_HOLDER_DATA.ZIP_CODE," & _
   "dbo_HOLDER_DATA.COUNTY, dbo_HOLDER_DATA.TELEPHONE, dbo_HOLDER_DATA.BIRTH_DATE, dbo_HOLDER_DATA.DECEASED," & _
   "dbo_HOLDER_DATA.HUNTER_EDUCATION, dbo_HOLDER_DATA.SEX, dbo_HOLDER_DATA.HEIGHT, dbo_HOLDER_DATA.WEIGHT," & _
   "dbo_HOLDER_DATA.EYE_COLOR, dbo_HOLDER_DATA.HEIGHT, dbo_HOLDER_DATA.WEIGHT, dbo_HOLDER_DATA.EYE_COLOR, " & _
   "dbo_HOLDER_DATA.HAIR_COLOR, dbo_PURCHASE_DATA.LICENSE_TYPE, dbo_PURCHASE_DATA.SERIAL_NUMBER," & _
   "dbo_PURCHASE_DATA.PURCHASE_DATE, dbo_PURCHASE_DATA.PURCHASE_AMOUNT, dbo_PURCHASE_DATA.NAME_CODE, " & _
   "dbo_PURCHASE_DATA.STATUS, dbo_PURCHASE_DATA.STATUS_DATE" & _
   "FROM dbo_PURCHASE_DATA INNER JOIN dbo_HOLDER_DATA ON dbo_PURCHASE_DATA.NAME_CODE = dbo_HOLDER_DATA.NAME_CODE " & _
   "Where dbo_HOLDER_DATA.NAME_CODE = namecode;"
      ' Undo the change.
      DoCmd.RunSQL strdelete

      ' Cancel the update.
      Cancel = True
   End If

End Sub


Private Sub Form_Load()
cboltype = ""
txtlnumber = ""
cbostatus = ""
txtpurchasedate = ""
txtstatusdate = ""
txtpurchaseamount = ""
txtlname = ""
txtfirstname = ""
txtmi = ""
cbosuffix = ""
txtssn = ""
txtaddress1 = ""
txtaddress2 = ""
txtcity = ""
txtstate = ""
txtzipcode = ""
cbocounty = ""
txtphone = ""
txtdob = ""
txtdeceased = ""
txtgender = ""
cbohaircolor = ""
cboeyecolor = ""
txtheight = ""
txtweight = ""

End Sub

Private Sub Go_Back_Click()
'DoCmd.OpenForm "frmsearchbylname"
End Sub

I figured that problem by unbounding the form and declare two diff recorsets for two different tables. It is working now. But still one more problem:
In dbo_HOLDER_DATA fields called HAIR_COLOR,EYE_ COLOR,HEIGHT and WEIGHT are not required. and still if I try to save the records without entering the values in those fields, it says "Object Required"
Can you help me for this?
Thanks for your time and help. I will keep in mind to write exact field names from nowonwards.
Also what shd be done to undo the recent changes? I mean undo the lastly added record. I wrote some code for that as you can see but it is not working.

[NeoPa]

Jinal,

I wish I could be more help, but other than not answering the question I flagged as Important Question, you also seem to have taken the route I warned you against in trying to get past your problem.

This is a blind alley and to be avoided. You are trying to code past Access instead of correctly getting Access to do the job for you. This approach may get you some way down the road, but ultimately will come back to bite you.

Another unwanted side-effect of this approach is that, having missed the opportunity to learn how to get Access to do this for you, you are setting yourself up to have to do a similar, lengthy solution (involving more code) next time you come across a similar situation.

Please don't think I'm trying to be obstructive. I have no such intention. I can't imagine I'd be much help if you're not happy with my suggestions though (which would be as outlined above obviously).