Trying to Add a New record to SQL by code - ODBC Call Failed

L

LisaB

When I run the following code I get an -- Error # 3146 ODBC Call Failed--
can anyone please tell me what is wrong and how to fix it

1. This is an Access 2000 application connected to a SQL2000 database
2. tblGrants is in the SQL2000 database
3. tblUploadGrants is an Access table (in the Front-end)
4. When debugging the error stops on -- .Update --
5. A new record never get added to the SQL table because of this error
6. In tblGrants there is an AutoNum field and field GRANTID is the Primary
Key
--------------------------
Public Sub AddUploadGrantRecords()

Dim UniqueNumber As Integer
Dim DestinationRS As DAO.Recordset

SQLStatement = "SELECT Max(tblGrants.AutoNum) AS MaxOfAutoNum FROM
tblGrants;"

Set TheDB = CurrentDb
Set SourceRS = TheDB.OpenRecordset(SQLStatement, dbOpenDynaset,
dbSeeChanges)
Set SourceRS2 = TheDB.OpenRecordset("tblUploadGrants", dbOpenDynaset,
dbSeeChanges)
Set DestinationRS = TheDB.OpenRecordset("tblGrants", dbOpenDynaset,
dbSeeChanges)

With SourceRS
UniqueNumber = !MaxOfAutonum + 1
End With

SourceRS.Close

If SourceRS2.RecordCount Then
SourceRS2.MoveFirst

Do Until SourceRS2.EOF
With DestinationRS
'.MoveLast
.AddNew 'adding to tblGrants
!GrantID = "CSAT" & UniqueNumber
!OrgID = SourceRS2![OrgID]
!GrantNum = SourceRS2![Grant No ]
!GranteeName = SourceRS2![Grantee ]
!Project = SourceRS2![Project ]
!StartDate = SourceRS2![Project Start ]
!EndDate = SourceRS2![Project End ]
!Deactivating = SourceRS2![Deactivating Date ]
!ProgramArea = SourceRS2![Program ]
!GFAProgram = SourceRS2![GFA Program ]
!Setting = SourceRS2![Setting ]
!Modality = SourceRS2![Modality ]
!SubPop = SourceRS2![Sub-Population ]
!Division = SourceRS2![Division ]
!Editor = "Upload - " & CurrentUser
!EditDate = Now()
.Update
End With

SourceRS2.MoveNext
UniqueNumber = UniqueNumber + 1
Loop
End If


SourceRS2.Close
DestinationRS.Close

End Sub
 
J

Joe Fallon

When SQL Server uses an Identity column (Autonumber in Access) then you do
NOT pass in the ID field as part of the Insert statement. The DB will
generate the next value automatically upon insert.
 
L

LisaB

I am not passing a value to the Autonum field (GrantID is not the Autonum
field)

I found the problem -- It was the Program Area Field

Program Area is in a LookUp table and the record that was being added had a
Program Area that was not in the Program Area LookUp table

So, before I add new records I must first check to see if there are any new
Program Area's and add them to the Program Area LookUp table.

Thanks for your response

Joe Fallon said:
When SQL Server uses an Identity column (Autonumber in Access) then you do
NOT pass in the ID field as part of the Insert statement. The DB will
generate the next value automatically upon insert.
--
Joe Fallon
Access MVP



LisaB said:
When I run the following code I get an -- Error # 3146 ODBC Call Failed--
can anyone please tell me what is wrong and how to fix it

1. This is an Access 2000 application connected to a SQL2000 database
2. tblGrants is in the SQL2000 database
3. tblUploadGrants is an Access table (in the Front-end)
4. When debugging the error stops on -- .Update --
5. A new record never get added to the SQL table because of this error
6. In tblGrants there is an AutoNum field and field GRANTID is the
Primary
Key
--------------------------
Public Sub AddUploadGrantRecords()

Dim UniqueNumber As Integer
Dim DestinationRS As DAO.Recordset

SQLStatement = "SELECT Max(tblGrants.AutoNum) AS MaxOfAutoNum FROM
tblGrants;"

Set TheDB = CurrentDb
Set SourceRS = TheDB.OpenRecordset(SQLStatement, dbOpenDynaset,
dbSeeChanges)
Set SourceRS2 = TheDB.OpenRecordset("tblUploadGrants", dbOpenDynaset,
dbSeeChanges)
Set DestinationRS = TheDB.OpenRecordset("tblGrants", dbOpenDynaset,
dbSeeChanges)

With SourceRS
UniqueNumber = !MaxOfAutonum + 1
End With

SourceRS.Close

If SourceRS2.RecordCount Then
SourceRS2.MoveFirst

Do Until SourceRS2.EOF
With DestinationRS
'.MoveLast
.AddNew 'adding to tblGrants
!GrantID = "CSAT" & UniqueNumber
!OrgID = SourceRS2![OrgID]
!GrantNum = SourceRS2![Grant No ]
!GranteeName = SourceRS2![Grantee ]
!Project = SourceRS2![Project ]
!StartDate = SourceRS2![Project Start ]
!EndDate = SourceRS2![Project End ]
!Deactivating = SourceRS2![Deactivating Date ]
!ProgramArea = SourceRS2![Program ]
!GFAProgram = SourceRS2![GFA Program ]
!Setting = SourceRS2![Setting ]
!Modality = SourceRS2![Modality ]
!SubPop = SourceRS2![Sub-Population ]
!Division = SourceRS2![Division ]
!Editor = "Upload - " & CurrentUser
!EditDate = Now()
.Update
End With

SourceRS2.MoveNext
UniqueNumber = UniqueNumber + 1
Loop
End If


SourceRS2.Close
DestinationRS.Close

End Sub
Click to expand...
Click to expand...
 
Top