Problem adding records to SQL tables from Access

[Guest]

I have an Access database with a SQL Server back-end. I am new to SQL, so
the problem I have been having is a real puzzle to me.

I have created an Access query called ‘qryIRCasesUnrouted’, which finds
records in a table called ‘IR’ that do not have a matching key in another
table called ‘tblRoutingLog’. The query is used to find new Incident Reports
(Cases) that haven’t been routed so that I can add them to the Routing List
for processing and disposition.

I then run the following subroutine in Access to add those records to the
‘tblRoutingLog’ table:

Public Sub ImportIR()
On Error GoTo ErrHandler

Dim DB As DATABASE, rstSrc As Recordset, rstTgt As Recordset
Dim i As Integer, strSrcSQL As String

Set DB = CurrentDb
strSrcSQL = "SELECT * FROM qryIRCasesUnrouted WHERE ORI = '" &
Forms!Login!ORI & "'"
Set rstSrc = DB.OpenRecordset(strSrcSQL, dbOpenDynaset, dbSeeChanges)
Set rstTgt = DB.OpenRecordset("tblRoutingLog", dbOpenDynaset, dbSeeChanges)

With rstSrc
Do Until .EOF
rstTgt.AddNew
rstTgt!RouteUID = NewPK
rstTgt!KeyID = !Case_ID
rstTgt!RouteRecipient = !LoginName
rstTgt!RecvdDate = Now
rstTgt!Pending = True
rstTgt!ReportType = "Incident Report"
rstTgt!Descrip = !Case_Number
rstTgt!Status = "A"
rstTgt.Update
i = i + 1
.MoveNext
Loop
.Close
rstTgt.Close
End With

ImportExit:
Set rstSrc = Nothing
Set rstTgt = Nothing
Exit Sub

ErrHandler:

MsgBox "Import failed due to Error # " & err.Number & " - " &
err.Description, vbCritical + vbOKOnly, "Import failed"
Resume ImportExit
End Sub

The subroutine simply moves through the ‘qryIRCasesUnrouted’ result set and
adds the appropriate data to the ‘tblRoutingLog’ SQL table.

In most cases, everything works fine. However, I recently had a customer
who had over 5000 records to add and I received the following error:

Error # 3155 – ODBC –insert on a linked table ‘tblRoutingLog’ failed

This doesn’t occur until it has already added 3601 records to the
‘tblRoutingLog’ table. Trying to re-run it the second time to add the rest
of the records doesn’t work either.

I noticed that if I checked the “Create as Clustered†option for the Primary
Key of the tblRoutingLog table in Enterprise Manager, I do not get this error
and all the records are added.

Can anyone explain to me what the problem is? I’m not confident that I
won’t have problems with other clients in the future? What does changing the
‘Primary Key to clustered do?

PS
This error doesn’t occur if I import to an Access table either

 

[Joe Fallon]

Clustered should not make a difference.
The trick is to have a true PK defined. (Which it sounds like you did.)
It also helps to have a Timestamp column defined in the table so Access can
interact better with SQL Server.

 

[Guest]

Maybe it is a time-out issue then, because it does work if I choose the
Clustered Index and it does not if it is not.

How do you create a timestamp column? And what does it do?

 

[Rick Brandt]

Maybe it is a time-out issue then, because it does work if I choose
the Clustered Index and it does not if it is not.

How do you create a timestamp column? And what does it do?

If you use Enterprise Manager to change the design of the table on the SQL
Server Timestamp is one of the choices when adding a new field.

It is a binary (non human readable) value that is guranteed to be unique
within a particular database and is updated every time a row is inserted or
updated.

Whenever Access (via ODBC) is used to do an update to a row it checks to see
if another process has modified the same row since the current user began
editing it. When a Timestamp column exists all that has to be done to
determine this is to compare the live Timestamp value to the cached
Timestamp value. If a Timestamp is not present then Access will compare the
value of every field in the live record to the cache and certain data types
that don't map exactly to an Access equivelant might "appear" to Access to
have been changed when they really haven't. This will cause erroneous
errors messages indicating write conflict with other users.

 

[Guest]

Thanks for the information. I will take a look at the table design to see if
I can implement the timestamp option in Enterprise Manager.