Get Records Affected on inserting a row

[dileeprajam]

Hi,
I am new to access vba. i am trying to create a code to insert rows into Oracle Db via linked tables. I wanted to check if the row is inseted into my Oracle DB or not and so i am using Access RecordsAffected property of Database.

below is the code snippet i am using:

Dim myDb As dao.Database
Dim strSQL As String
Dim i As Integer
strSQL = "INSERT INTO <Linked Table Name> values(---)"
Set myDb = CurrentDb
myDb.Execute strSQL, dbFailOnError
i = myDb.RecordsAffected
MsgBox (i)

But i am getting the error as

Run-time error '3146' ODBC--Call failed.

can you please help me where i am doing wrong?

Appreciate your help
Regards,
-Dileep

[dileeprajam]

When i remove the dbFailOnError in the execute statement, i am not getting the ODBC failed error and the record is being inserted into my DB but the RecordsAffected property returns 0 instead of 1.

Can anyone help why is it returning 0?

Thank you in advance.

Regards,
-Dileep

[zmbd]

We need the version of access you are using and you're going to have to show us line 4.
you can place "***" for passwords and user names if needed.

[dileeprajam]

Hi i am using Access 2007 and below is the code i am using

strSQL = "INSERT INTO <Linked Table Name> Select AccessTable.Col1,AccessTable.Col2,AccessTable.Col3 from <AccessTable Name>"

Thanks for the help in advance.

-Dileep

[zmbd]

Ok,
as you are either unwilling or unable to provide the actual resolved string being used in the execute method, the best that can be offered to help:
INSERT INTO Statement (Microsoft Access SQL) Office 2010 I don't think that there are any changes between ACC2007 and ACC2010 for this method.

[dileeprajam]

This is the insert statement i am using. and my linked tables are linked using DSN.

i did not mention actual table Names instead given generic name.

[zmbd]

We need the actual string... absolutely no generics;
3146 error means that there is something in the string that the SQL engine does not like.

Unless you are willing-to or able-to provide the actual string that you are using we will not be able to help you.

[dileeprajam]

This is the exact string

strSQL = "INSERT INTO BHP_SINGLE_POINT SELECT UpdateData.SPOINT_ID, UpdateData.SP_REFNO, UpdateData.SP_SIDETRACK, " _
& "UpdateData.SP_COMPLETION, UpdateData.SP_DATE, UpdateData.SP_SI_TIME, UpdateData.SP_SURVEYTYP, " _
& "UpdateData.SP_DATUM, UpdateData.SP_PRES, UpdateData.SP_TEMP, UpdateData.SP_GRADIENT," _
& "UpdateData.SP_COMMENTS, UpdateData.SP_INCLUDE_SURVEY, " _
& "UpdateData.SP_QUAL_COMMENTS, UpdateData.SP_SURVEY_SOURCE " _
& "FROM UpdateData WHERE (UpdateData.SPOINT_ID= 78069);" '" & intMinSpointId & "

[dileeprajam]

This is the exact code i am using

Dim strSQL As String
strSQL = "INSERT INTO BHP_SINGLE_POINT SELECT UpdateData.SPOINT_ID, UpdateData.SP_REFNO, UpdateData.SP_SIDETRACK, " _
& "UpdateData.SP_COMPLETION, UpdateData.SP_DATE, UpdateData.SP_SI_TIME, UpdateData.SP_SURVEYTYP, " _
& "UpdateData.SP_DATUM, UpdateData.SP_PRES, UpdateData.SP_TEMP, UpdateData.SP_GRADIENT," _
& "UpdateData.SP_COMMENTS, UpdateData.SP_INCLUDE_SURVEY, " _
& "UpdateData.SP_QUAL_COMMENTS, UpdateData.SP_SURVEY_SOURCE " _
& "FROM UpdateData WHERE (UpdateData.SPOINT_ID= 78069);" '" & intMinSpointId & "

Dim myDb As dao.Database
Dim i As Integer
Set myDb = CurrentDb

myDb.Execute strSQL , dbFailOnError
i = myDb.RecordsAffected
MsgBox (i)

[zmbd]

First Error:
Line 3:
& "UpdateData.SP_ DATUM, UpdateData.SP_P RES, UpdateData.SP_T EMP, UpdateData.SP_G RADIENT," _

Insert a space before the last quote:
& "UpdateData.SP_ DATUM, UpdateData.SP_P RES, UpdateData.SP_T EMP, UpdateData.SP_G RADIENT, " _
This is more than likely your 3146

Also double check that UpdateData.SPOI NT_ID= 78069 refers to a numeric type cast in the receiving table or you may get this 3146 or another error. Depends on Access-SQL's mood.

Speaking of its moods; I've also found this to be helpful in trying to get it to talk to me. I found the code some years ago when researching my own 3146 error:

'(... code left out ...)
   Dim MyError As Error
'(... code left out ...)
   On error goto z_errortrap
'(... code left out ...)
   exit sub
z_errortrap:
   if err.number = 3146
      Debug.print Errors.Count
      For Each MyError In DBEngine.Errors
         With MyError
            debug.pring .Number & " " & .Description
         End With
      Next MyError
   end if
end sub

I've occationally had to place this in the form's on_error event to trap pass-thru queries. This will print the errors to the immediate winow ( <ctrl><g> ) which I find much easier to deal with than message boxes.

Trying to find where I ran across that code in lead me to this:
Database.Execut e Method ACC2007 However, I've used the code since ACC2003.

As for why your record count is wrong... more than likely it has to do with the error.

Let us know what pops-up

OK - I'm out the door so it'll be awhile before I get back to the answers.

[dileeprajam]

Hi,

after doing the changes, it worked fine if the data to be inserted is not violating any Oracle key violations set at the table level. If a duplicate record is being inserted which leads to a primary key violation, i am getting the ODBC--Call failed error.

I had then commented the dbFailOnError option in the execute statement, it displayed 0 rows affected which is good. But, now i am getting a different error "3155 ODBC--insert on a linked table BHP_SINGLE_POIN T failed" for the new record.

I had modified the code to loop through the access data table "UpdateData " which has one record which leads to Key Violation and one valid record.
below is the modified code:

Dim strSQL As String
Dim intCounter As Integer
Dim intMinSpointId As Long
Dim intMaxSpointId  As Long
intMinSpointId = 62503
intMaxSpointId = 62504

While intMinSpointId <= intMaxSpointId
strSQL = "INSERT INTO BHP_SINGLE_POINT SELECT UpdateData.SPOINT_ID, UpdateData.SP_REFNO, UpdateData.SP_SIDETRACK, " _
& "UpdateData.SP_COMPLETION, UpdateData.SP_DATE, UpdateData.SP_SI_TIME, UpdateData.SP_SURVEYTYP, " _
& "UpdateData.SP_DATUM, UpdateData.SP_PRES, UpdateData.SP_TEMP, UpdateData.SP_GRADIENT, " _
& "UpdateData.SP_COMMENTS, UpdateData.SP_INCLUDE_SURVEY, " _
& "UpdateData.SP_QUAL_COMMENTS, UpdateData.SP_SURVEY_SOURCE " _
& "FROM UpdateData WHERE (UpdateData.SPOINT_ID= " & intMinSpointId & ");" '

Dim myDb As dao.Database
Dim i As Integer
Set myDb = CurrentDb

'MsgBox (myDb.Name)
myDb.Execute strSQL ', dbFailOnError
i = myDb.RecordsAffected
MsgBox (i)
intMinSpointId = intMinSpointId + 1
Wend

now for the 1st record it diplayed 0 rows affected and for the 2nd record(valid one) i am getting the 3155 error

[zmbd]

Trap all of the ODBC level errors in the form's on_error event. Insert the code I posted at the end of my last post for part of the event hanlder, change it slightly so that the if_then construct looks like: If DBEngine.Errors .Count > 0 Then

dileeprajam:
You are going to have to write code that starts comparing the exsiting records in your back-end against the data you are trying to push to it from the front end. This is what your new error is trying to tell you:
---

ODBC – insert on a linked table <table> failed. (Error 3155)

Using an ODBC connection, you tried to insert data into an ODBC database; that insertion could not be completed.

Possible causes:

• The insertion would have caused a rule violation.
• The ODBC database is read-only, or you do not have permission to insert data in that database. Resolve the read-only condition, or see your system administrator or the person who created the database to obtain the necessary permissions.
• The ODBC database is on a network drive, and you are not connected to the network. Make sure the network is available, and then try the operation again.

---

We can certainly help you create such code; however, that should be started in a new thread - and keep in mind that you will have to create the code structure as you are the only one with the data infront of you :)

[dileeprajam]

Thank you for your prompt responses, i really had learnt a lot, and understood what these errors are about.

[zmbd]

I use an Access Front end I developed against the company MYSQL server (and upon occasions an Oracle Server)... thankfully, I have REALY good support and an excellent DBA that oversees the enterprise system - everything I know about this was either from them or the school of hard knocks - so I'm glad to get a chance to pass some of that help along to others!