Results 1 to 11 of 11
  1. #1
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065

    ODBC Error Writing to a new record

    I have VBA Code to merge data from 4 databases into a single database. One of them is giving me an issue



    Here's the error I'm getting

    Click image for larger version. 

Name:	ODBC Error.jpg 
Views:	18 
Size:	89.0 KB 
ID:	27035

    Here's the code that generates that Error

    Code:
            strsql = "Select * from dbo_tbl_Contacts1 where PatientID = " & rsmain!PatientID
            rscontacts.Open strsql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
            Do Until rscontacts.EOF
                strsql = "Insert Into dbo_tbl_contacts (contactdate,contacttext,personid,createdby,contacttype,programtype) values (#" & _
                    rscontacts!ContactDate & "#,""" & rscontacts!contactdesc & """," & rsperson!PersonID & ",""" & GetLoginName() & """,1,2)"
                rsout.CommandText = strsql
                rsout.Execute
                rscontacts.MoveNext
            Loop
    
    The Primary Key and only index is on ContactID which is an Identity field in dbo_tbl_contacts.  which is why it's not included in the Insert SQL above.  The Error I get from the code is just that the odbc call failed.  The error above is from taking the Insert Into strsql and putting it into the Query Analyzer and running it there.  The problem I have is that the only Key for that table is a field that is not included in the Insert Into since it's an identity field so what else could cause this?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    here's the SQL: Insert Into dbo_tbl_contacts (contactdate,contacttext,personid,createdby,contac ttype,programtype) values (#3/8/2016#,"phone",42975,"RMILHON",1,2)

    It works if I use query analyzer but I get an odbc error as stated above if I run it in VBA.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    How is rsout set?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Code:
    Private rsout As New ADODB.Command
    Is in the Declarations Section of the module of the module and the code is as above. There is also a line in the beginning of the sub routine
    Code:
    rsout.activeconnection = currentproject.connection
    Here's the entire subroutine

    Code:
    Public Sub Import_Health_Education()
        Dim add As String, diabtype As Long
        rsmain.Open "Select * from dbo_tbl_DM_Wellness", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
        Do Until rsmain.EOF
            If Not IsNull(rsmain!LastName) And Not IsNull(rsmain!FirstName) And Not IsNull(rsmain!DOB) Then
                strsql = "Select PersonID From dbo_tbl_person where lastname = """ & rsmain!LastName & """ and firstname = """ & _
                    rsmain!FirstName & """ and dob = #" & rsmain!DOB & "#"
                rsperson.Open strsql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
                If rsperson.EOF Then
                   strsql = "Insert Into dbo_tbl_Person (PersonKey,LastName,Firstname,DOB,Gender,HPCode,pcp) Values (""" & _
                        Left(rsmain!LastName, 4) & Left(rsmain!FirstName, 4) & Format(rsmain!DOB, "mmddyyyy") & """,""" & _
                        rsmain!LastName & """,""" & rsmain!FirstName & """," & _
                        IIf(IsNull(rsmain!DOB), "Null", "#" & rsmain!DOB & "#") & ",""" & _
                        rsmain!Gender & """,""" & rsmain!hpcode & """,""" & rsmain!pcp & """)"
                    rsout.ActiveConnection = CurrentProject.Connection
                    rsout.CommandText = strsql
                    rsout.Execute
                    rsperson.Close
                    strsql = "Select PersonID From dbo_tbl_person where lastname = """ & rsmain!LastName & """ and firstname = """ & _
                        rsmain!FirstName & """ and dob = #" & rsmain!DOB & "#"
                    rsperson.Open strsql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
                End If
            End If
            'Save Addresses
            add = Replace(rsmain!StreetAddress, Chr(34), "")
            strsql = "insert into dbo_tbl_Address (personid, address, unit,city,state,zip,zipplus,addtype,chdate," & _
                  "chby) values " & _
                  "(" & rsperson!PersonID & ",""" & add & """,""" & """,""" & rsmain!City & """,""" & rsmain!State & """,""" & _
                  rsmain!zip & """,""" & rsmain!zipplus & """," & _
                  2 & ", #" & Date & "#,""" & GetLoginName() & """)"
            rsout.ActiveConnection = CurrentProject.Connection
            
            rsout.CommandText = strsql
            rsout.Execute
            If Not IsNull(rsmain!email) Then
                strsql = "insert into dbo_tbl_emails (personid,emailaddress) values (" & rsperson!PersonID & ",""" & rsmain!email & """)"
                rsout.CommandText = strsql
                rsout.Execute
            End If
            'Home Phone
            If Not IsNull(rsmain!phone) Then
                If IsNumeric(Left(rsmain!phone, 1)) Then
                    strsql = "insert into dbo_tbl_phone (personid,phonenumber,phonetype) values(" & rsperson!PersonID & ",""" & rsmain!phone & """,1)"
                Else
                    strsql = "insert into dbo_tbl_phone (personid,phonenumber,phonetype) values(" & rsperson!PersonID & ",""" & Left(rsmain!phone, 12) & """,1)"
                End If
                rsout.CommandText = strsql
                rsout.Execute
            End If
            strsql = "Select DiabTypeID from dbo_tbl_diab_type where DiabDesc = """ & rsmain!diabtype & """"
            rsdiab.Open strsql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
            If rsdiab.EOF Then diabtype = 3 Else diabtype = rsdiab!diabtypeid
            strsql = "insert into dbo_tbl_Health_Education(personid,status,Date_Recd,Referred_Date,Referred_Source,ClassType,DiabType,last_Office_Visit,Closed_Reason) values " & _
                "(" & rsperson!PersonID & "," & IIf(rsmain!Status = 2, 9, 8) & ",#" & rsmain!HE_Received & "#,#" & rsmain!Referred_Date & "#,""" & rsmain!referral_source & """," & _
                rsmain!Type & "," & diabtype & "," & IIf(IsNull(rsmain!last_office_visit), "Null", "#" & rsmain!last_office_visit & "#") & _
                ",""" & rsmain!closed_reason & """)"
            rsout.CommandText = strsql
            rsout.Execute
            strsql = "Select * from dbo_tbl_Contacts1 where PatientID = " & rsmain!PatientID
            rscontacts.Open strsql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
            Do Until rscontacts.EOF
                strsql = "Insert Into dbo_tbl_contacts (contactdate,contacttext,personid,createdby,contacttype,programtype) values (#" & _
                    rscontacts!ContactDate & "#,""" & rscontacts!contactdesc & """," & rsperson!PersonID & ",""" & GetLoginName() & """,1,2)"
                rsout.ActiveConnection = CurrentProject.Connection
                rsout.CommandText = strsql
                rsout.Execute
                rscontacts.MoveNext
            Loop
    
             rsperson.Close
            rsmain.MoveNext
        Loop
    
    End Sub
    As you can see the same routine is used several times and works all except for the last one.

  6. #6
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    I have seen times where although that error says "key violations", it isn't a key violation at all, it is something else. Check your data for a validation rule problem, or for data missing in a field that is required.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You're saying if you take the debug.print SQL and put it in a new query, it works? You get the error when the same SQL is executed via the recordset? I don't use ADO much; have you tried

    CurrentDb.Execute strsql, dbFailOnError

    to execute the SQL?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    No I haven't will try that now and post back in a few

  9. #9
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Nope same error. It must be something in the SQL that will run in Query Analyzer but won't in VBA. No idea what that could be though.

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Is it possible to attach a sample db that exhibits the error?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,065
    Found the problem although I'm still kind of confused. There is a field that in the new DB that does not exist in the old one I'm importing Due to a different set of requirements in the different DB's I'm merging, it was a required field so the error would come up. I'm confused because if that's a required field how did it work in Query Analyzer? I must have done something when copying it over to Query Analyzer but I've no idea what it could've been

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Writing to a record twice
    By Thompyt in forum Programming
    Replies: 6
    Last Post: 10-19-2016, 12:00 PM
  2. Replies: 1
    Last Post: 03-02-2015, 09:44 AM
  3. Writing macro to go to specific record
    By johnjmcnaughton in forum Programming
    Replies: 18
    Last Post: 04-15-2013, 10:23 AM
  4. Replies: 0
    Last Post: 07-16-2012, 05:42 AM
  5. Odbc error please help
    By TCB in forum Access
    Replies: 3
    Last Post: 04-13-2011, 02:07 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums