By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 2,482 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

Error ODBC Call Failed

jinalpatel
P: 68
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.


Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Dim str As String
  3. Dim namecode As String
  4.  
  5.  
  6.  
  7. Private Sub cmdsave_Click()
  8. On Error GoTo Err_cmdsave_Click
  9. Dim conn As ADODB.Connection
  10. Dim rst As ADODB.Recordset
  11.  
  12. Set conn = New ADODB.Connection
  13. Set rst = New ADODB.Recordset
  14.  
  15.  
  16. rst.CursorType = adOpenKeyset
  17. rst.LockType = adLockOptimistic
  18.  
  19.     rst.Open "Query3", CurrentProject.Connection
  20.  
  21.    If rst.Supports(adAddNew) Then
  22.         With rst
  23.             .AddNew
  24.             .Fields("NAME_CODE") = namecode
  25.              namecode = txtltype & txtlname & Left(Me.txtfirstname, 1) & txtmi
  26.              Debug.Print namecode
  27.             .Fields("LAST_NAME") = txtlname
  28.             .Fields("FIRST_NAME") = txtfirstname
  29.             .Fields("MIDDLE_INITIAL") = txtmi
  30.             .Fields("Suffix") = txtsuffix
  31.             .Fields("SSN") = txtssn
  32.             .Fields("ADDRESS") = txtaddress1
  33.             .Fields("ADDRESS2") = txtaddress2
  34.             .Fields("CITY") = txtcity
  35.             .Fields("STATE") = txtstate
  36.             .Fields("ZIP_CODE") = txtzipcode
  37.             .Fields("COUNTY") = txtcounty
  38.             .Fields("TELEPHONE") = txtphone
  39.             .Fields("BIRTH_DATE") = txtdob
  40.             .Fields("SEX") = txtgender
  41.             .Fields("DECEASED") = txtdeceased
  42.             .Fields("SEX") = txtgender
  43.             .Fields("HEIGHT") = txtheight
  44.             .Fields("WEIGHT") = txtweight
  45.             .Fields("EYE_COLOR") = txteyecolor
  46.             .Fields("HAIR_COLOR") = txthaircolor
  47.             .Fields("LICENSE_TYPE") = txtltype
  48.             .Fields("SERIAL_NUMBER") = txtlnumber
  49.             .Fields("PURCHASE_DATE") = txtpurchasedate
  50.             .Fields("PURCHASE_AMOUNT") = txtpurchaseamount
  51.             '.Fields("[dbo_PURCHASE_DATA].[NAME_CODE]") = namecode
  52.             .Fields("STATUS") = txtstatus
  53.             .Fields("STATUS_DATE") = txtstatusdate
  54.             .Update
  55.        End With
  56.     End If
  57.  
  58.  
  59.     MsgBox "Successfully added", vbOKOnly, "New record Added"
  60.     rst.Close
  61.  
  62.     Set rst = Nothing
  63.     'DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  64.  
  65.  
  66. Exit_cmdsave_Click:
  67.     Exit Sub
  68.  
  69. Err_cmdsave_Click:
  70.     MsgBox Err.Description
  71.     Resume Exit_cmdsave_Click
  72.  
  73. End Sub
  74. Private Sub cmdadd_Click()
  75. On Error GoTo Err_cmdadd_Click
  76.  
  77.     DoCmd.GoToRecord , , acNewRec
  78.  
  79. Exit_cmdadd_Click:
  80.     Exit Sub
  81.  
  82. Err_cmdadd_Click:
  83.     MsgBox Err.Description
  84.     Resume Exit_cmdadd_Click
  85.  
  86. End Sub
Apr 11 '08 #1
Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,186
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.
Apr 11 '08 #2

jinalpatel
P: 68
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"+"Initials" 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.
Apr 14 '08 #3

NeoPa
Expert Mod 15k+
P: 31,186
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 :
Expand|Select|Wrap|Line Numbers
  1. Table Name=[tblLicense]
  2. Field; Type; IndexInfo
  3. StudentID; AutoNumber; PK
  4. Family; String; FK
  5. Name; String
  6. University; String; FK
  7. Mark; Numeric
  8. LastAttendance; Date/Time
Apr 14 '08 #4

jinalpatel
P: 68
Holder Data (like a Customer table)
Expand|Select|Wrap|Line Numbers
  1. Table Name=[dbo_HOLDER_DATA]
  2. Field;         Type;   IndexInfo
  3. Namecode       Text      PK
  4. SSN            text      req
  5. lastname       text      req
  6. firstname      text      req
  7. middleinitial  text      req
  8. address        text      req
  9. City           text      req
  10. State          text      req
  11. Zipcode        text      req
  12. County         number    req
  13. dob text       date/time req
  14. deceased       text      req
Expand|Select|Wrap|Line Numbers
  1. Table Name=[dbo_PURCHASE_DATA
  2. Field;         Type;   IndexInfo
  3. Licensetype    number     PK
  4. Serialno       number     PK (it's license no)
  5. Purchasedate   date/time  req
  6. Amount         currency   req
  7. namecode       text       req
  8. status         number     req
  9. statusdate     date/time  req
Expand|Select|Wrap|Line Numbers
  1. SQL bound to form :
  2. SELECT dbo_HOLDER_DATA.*,
  3.        dbo_PURCHASE_DATA.LICENSE_TYPE,
  4.        dbo_PURCHASE_DATA.SERIAL_NUMBER,
  5.        dbo_PURCHASE_DATA.PURCHASE_DATE,
  6.        dbo_PURCHASE_DATA.PURCHASE_AMOUNT,
  7.        dbo_PURCHASE_DATA.STATUS,
  8.        dbo_PURCHASE_DATA.STATUS_DATE
  9. FROM dbo_PURCHASE_DATA LEFT JOIN
  10.      dbo_HOLDER_DATA
  11.   ON dbo_PURCHASE_DATA.NAME_CODE = dbo_HOLDER_DATA.NAME_CODE;
Apr 15 '08 #5

NeoPa
Expert Mod 15k+
P: 31,186
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).
Apr 16 '08 #6

jinalpatel
P: 68
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Dim str As String
  3. Dim namecode As String
  4. Dim mfRequery As Boolean
  5.  
  6. Private Sub cmdsave_Click()
  7. On Error GoTo Err_cmdsave_Click
  8.  
  9. namecode = txtltype & txtlname & Left(Me.txtfirstname, 1) & txtmi
  10.  
  11. Dim conn As ADODB.Connection
  12. Dim rst As ADODB.Recordset
  13. Dim response As Integer
  14. Dim rst1 As ADODB.Recordset
  15.  
  16. Set conn = New ADODB.Connection
  17.  
  18. Set rst1 = New ADODB.Recordset
  19. rst1.CursorType = adOpenKeyset
  20. rst1.LockType = adLockOptimistic
  21.  
  22.  
  23. Set rst = New ADODB.Recordset
  24. rst.CursorType = adOpenKeyset
  25. rst.LockType = adLockOptimistic
  26.  
  27.     rst.Open "dbo_HOLDER_DATA", CurrentProject.Connection
  28.    If rst.Supports(adAddNew) Then
  29.         With rst
  30.             .AddNew
  31.             .Fields("NAME_CODE") = cboltype & txtlname & Left(Me.txtfirstname, 1) & txtmi
  32.             .Fields("LAST_NAME") = txtlname
  33.             .Fields("FIRST_NAME") = txtfirstname
  34.             .Fields("MIDDLE_INITIAL") = txtmi
  35.             .Fields("Suffix") = cbosuffix
  36.             .Fields("SSN") = txtssn
  37.             .Fields("ADDRESS") = txtaddress1
  38.             .Fields("ADDRESS2") = txtaddress2
  39.             .Fields("CITY") = txtcity
  40.             .Fields("STATE") = txtstate
  41.             .Fields("ZIP_CODE") = txtzipcode
  42.             .Fields("COUNTY") = cbocounty
  43.             .Fields("TELEPHONE") = txtphone
  44.             .Fields("BIRTH_DATE") = txtdob
  45.             .Fields("SEX") = txtgender
  46.             .Fields("DECEASED") = txtdeceased
  47.             .Fields("SEX") = txtgender
  48.                 If txtheight Is Not Null Then
  49.                     .Fields("HEIGHT") = txtheight
  50.                 Else
  51.                     .Fields("HEIGHT") = ""
  52.                 End If
  53.                 If txtweight Is Not Null Then
  54.                     .Fields("WEIGHT") = txtweight
  55.                 Else
  56.                     .Fields("WEIGHT") = ""
  57.                 End If
  58.                 If cboeyecolor Is Not Null Then
  59.                     .Fields("EYE_COLOR") = cboeyecolor
  60.                 Else
  61.                     .Fields("EYE_COLOR") = ""
  62.                 End If
  63.                 If cbohaircolor Is Not Null Then
  64.                     .Fields("HAIR_COLOR") = cbohaircolor
  65.                 Else
  66.                     .Fields("HAIR_COLOR") = ""
  67.                 End If
  68.  
  69.             .Update
  70.        End With
  71.     End If
  72.  
  73. rst1.Open "dbo_PURCHASE_DATA", CurrentProject.Connection
  74.    If rst1.Supports(adAddNew) Then
  75.         With rst1
  76.             .AddNew
  77.             .Fields("LICENSE_TYPE") = cboltype
  78.             .Fields("SERIAL_NUMBER") = txtlnumber
  79.             .Fields("PURCHASE_DATE") = txtpurchasedate
  80.             .Fields("PURCHASE_AMOUNT") = txtpurchaseamount
  81.             .Fields("NAME_CODE") = namecode
  82.             .Fields("STATUS") = cbostatus
  83.             .Fields("STATUS_DATE") = txtstatusdate
  84.             .Update
  85.         End With
  86.     End If
  87.  
  88.  
  89. response = MsgBox("Records added successfully..Do you want to add another record??", vbOKCancel)
  90.  
  91.     If response = vbOK Then
  92.  
  93.         cboltype = ""
  94.         txtlnumber = ""
  95.         cbostatus = ""
  96.         txtpurchasedate = ""
  97.         txtstatusdate = ""
  98.         txtpurchaseamount = ""
  99.         txtlname = ""
  100.         txtfirstname = ""
  101.         txtmi = ""
  102.         cbosuffix = ""
  103.         txtssn = ""
  104.         txtaddress1 = ""
  105.         txtaddress2 = ""
  106.         txtcity = ""
  107.         txtstate = ""
  108.         txtzipcode = ""
  109.         cbocounty = ""
  110.         txtphone = ""
  111.         txtdob = ""
  112.         txtdeceased = ""
  113.         txtgender = ""
  114.         cbohaircolor = ""
  115.         cboeyecolor = ""
  116.         txtheight = ""
  117.         txtweight = ""
  118.  
  119.     Else
  120.     If response = vbCancel Then
  121.  
  122.       ' Undo the change.
  123.       DoCmd.RunCommand acCmdUndo
  124.     DoCmd.CancelEvent
  125.       ' Cancel the update.
  126.       Cancel = True
  127.     Else
  128.  
  129.     DoCmd.OpenForm frmsearchbylname, acNormal
  130.     End If
  131.  
  132.     End If
  133.  
  134.  
  135. Exit_cmdsave_Click:
  136.     Exit Sub
  137.  
  138. Err_cmdsave_Click:
  139.     MsgBox Err.Description
  140.     Resume Exit_cmdsave_Click
  141.  
  142. End Sub
  143. Private Sub cmdadd_Click()
  144.  
  145. On Error GoTo Err_cmdadd_Click
  146.  
  147. cboltype = ""
  148. txtlnumber = ""
  149. cbostatus = ""
  150. txtpurchasedate = ""
  151. txtstatusdate = ""
  152. txtpurchaseamount = ""
  153. txtlname = ""
  154. txtfirstname = ""
  155. txtmi = ""
  156. cbosuffix = ""
  157. txtssn = ""
  158. txtaddress1 = ""
  159. txtaddress2 = ""
  160. txtcity = ""
  161. txtstate = ""
  162. txtzipcode = ""
  163. cbocounty = ""
  164. txtphone = ""
  165. txtdob = ""
  166. txtdeceased = ""
  167. txtgender = ""
  168. cbohaircolor = ""
  169. cboeyecolor = ""
  170. txtheight = ""
  171. txtweight = ""
  172.  
  173. '    DoCmd.GoToRecord , , acNewRec
  174.  
  175. Exit_cmdadd_Click:
  176.     Exit Sub
  177.  
  178. Err_cmdadd_Click:
  179.     MsgBox "Error Occured"
  180.     Resume Exit_cmdadd_Click
  181.  
  182. End Sub
  183.  
  184. Private Sub Form_BeforeUpdate(Cancel As Integer)
  185.  Dim strMsg As String
  186.    Dim iResponse As Integer
  187.         Dim strdelete As String
  188.  
  189.    ' Specify the message to display.
  190.    strMsg = "Do you wish to save the changes?" & Chr(10)
  191.    strMsg = strMsg & "Click Yes to Save or No to Discard changes."
  192.  
  193.    ' Display the message box.
  194.    iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?")
  195.  
  196.    ' Check the user's response.
  197.    If iResponse = vbNo Then
  198.  
  199.    strdelete = "DELETE dbo_HOLDER_DATA.NAME_CODE, dbo_HOLDER_DATA.SSN, dbo_HOLDER_DATA.LAST_NAME," & _
  200.    "dbo_HOLDER_DATA.FIRST_NAME, dbo_HOLDER_DATA.MIDDLE_INITIAL, dbo_HOLDER_DATA.Suffix, dbo_HOLDER_DATA.ADDRESS," & _
  201.    "dbo_HOLDER_DATA.ADDRESS2, dbo_HOLDER_DATA.CITY, dbo_HOLDER_DATA.STATE, dbo_HOLDER_DATA.ZIP_CODE," & _
  202.    "dbo_HOLDER_DATA.COUNTY, dbo_HOLDER_DATA.TELEPHONE, dbo_HOLDER_DATA.BIRTH_DATE, dbo_HOLDER_DATA.DECEASED," & _
  203.    "dbo_HOLDER_DATA.HUNTER_EDUCATION, dbo_HOLDER_DATA.SEX, dbo_HOLDER_DATA.HEIGHT, dbo_HOLDER_DATA.WEIGHT," & _
  204.    "dbo_HOLDER_DATA.EYE_COLOR, dbo_HOLDER_DATA.HEIGHT, dbo_HOLDER_DATA.WEIGHT, dbo_HOLDER_DATA.EYE_COLOR, " & _
  205.    "dbo_HOLDER_DATA.HAIR_COLOR, dbo_PURCHASE_DATA.LICENSE_TYPE, dbo_PURCHASE_DATA.SERIAL_NUMBER," & _
  206.    "dbo_PURCHASE_DATA.PURCHASE_DATE, dbo_PURCHASE_DATA.PURCHASE_AMOUNT, dbo_PURCHASE_DATA.NAME_CODE, " & _
  207.    "dbo_PURCHASE_DATA.STATUS, dbo_PURCHASE_DATA.STATUS_DATE" & _
  208.    "FROM dbo_PURCHASE_DATA INNER JOIN dbo_HOLDER_DATA ON dbo_PURCHASE_DATA.NAME_CODE = dbo_HOLDER_DATA.NAME_CODE " & _
  209.    "Where dbo_HOLDER_DATA.NAME_CODE = namecode;"
  210.       ' Undo the change.
  211.       DoCmd.RunSQL strdelete
  212.  
  213.       ' Cancel the update.
  214.       Cancel = True
  215.    End If
  216.  
  217. End Sub
  218.  
  219.  
  220. Private Sub Form_Load()
  221. cboltype = ""
  222. txtlnumber = ""
  223. cbostatus = ""
  224. txtpurchasedate = ""
  225. txtstatusdate = ""
  226. txtpurchaseamount = ""
  227. txtlname = ""
  228. txtfirstname = ""
  229. txtmi = ""
  230. cbosuffix = ""
  231. txtssn = ""
  232. txtaddress1 = ""
  233. txtaddress2 = ""
  234. txtcity = ""
  235. txtstate = ""
  236. txtzipcode = ""
  237. cbocounty = ""
  238. txtphone = ""
  239. txtdob = ""
  240. txtdeceased = ""
  241. txtgender = ""
  242. cbohaircolor = ""
  243. cboeyecolor = ""
  244. txtheight = ""
  245. txtweight = ""
  246.  
  247. End Sub
  248.  
  249. Private Sub Go_Back_Click()
  250. 'DoCmd.OpenForm "frmsearchbylname"
  251. End Sub
  252.  
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.
Apr 16 '08 #7

NeoPa
Expert Mod 15k+
P: 31,186
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).
Apr 17 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.