Linked SQL Server tables - error occurs when trying to update

[Kevin Coleman]

Hi,

I am having a most perplexing problem.

I have moved all of my access tables to SQL Server 2000
and linked them to my access 2000 database.

Reading data is no problem... however when I try to add a
row to a table using code, I get an ODBC-Failed Call
error. After doing some testing, it appears the primary
key field on the form isn't being automatically updated
(it's NULL)... when I manually assign the next number to
the key field, it saves OK.

Here's my code:

Set DB = CurrentDb()
Set rs = DB.OpenRecordset("StudentForms", dbOpenDynaset,
dbSeeChanges)

rs.AddNew
rs![lnkStudent] = pkStudent
rs![lnkProgram] = CStr([Forms]![frmStudents]!
[lstRegisteredPrograms])
rs![Name] = cmbForm
rs.Update

On the rs.Update, the error occurs.

If I go out to the debugger and type:

rs![pkStudentForms]=19010

(for example), and then hit F8 to perform the update
command, it works fine.

Can anyone help me out?

Thanks,

Kevin Coleman

 

[Gary Miller]

Kevin,

Try debugging 'pkStudent'. It doesn't look like you have assigned it any
value before you are trying to put it in [lnkStudent]. If you have, it
doesn't show here.

Gary Miller

 

[Ron Weiner]

I assume that this field was an Access Autonumber Field when the backend was
hosted in Access. I believe that the upsizing tool converts this to an Int
on Sql200 but does NOT set the field to an Identity fields with an Increment
of 1.

You will need to edit the table to make this change, Relink the back end and
it will likely start working.

Another "problem" you might want to look out for is Access Yes/No fields get
converted to Sql Bit fields, but Access throws a hissy fit if this field is
null. So you will want to set all of the Sql bit fields to Not Null and
Default value to 0 (Zero).

RonW

 

[Kevin Coleman]

Thanks Ron and Gary -- I went through and made sure all of
the autonumber fields were set to autonumber and it
appears to work now.

One other thing that I've been trying to fix with little
success... I have an recordset that's innerjoined... and
both tables have primary keys (which are autonumber
fields).

I used to be able to do the following in Access:

'phone number
Set rs = DB.OpenRecordset("Select * FROM PhoneNumbers
INNER JOIN lnkPhoneNumbers ON
PhoneNumbers.pkPhoneNumber=lnkPhoneNumbers.lnkPhoneNumber
WHERE lnkStudent=" + CStr(HoldpkStudent) + " AND
lnkPhoneNumber=pkPhoneNumber AND PrimaryPhoneNumber=TRUE")
If Not (rs.BOF And rs.EOF) Then
rs.MoveFirst
rs.Edit
Else
rs.AddNew
rs![TelephoneType] = "Home"
rs![lnkStudent] = HoldpkStudent
rs![lnkPhoneNumber] = rs![pkPhoneNumber]
End If

This worked fine.... however I had to change it to:

'phone number
Set rs = DB.OpenRecordset("Select * FROM PhoneNumbers
INNER JOIN lnkPhoneNumbers ON
PhoneNumbers.pkPhoneNumber=lnkPhoneNumbers.lnkPhoneNumber
WHERE lnkStudent=" + CStr(HoldpkStudent) + " AND
lnkPhoneNumber=pkPhoneNumber AND PrimaryPhoneNumber=TRUE",
dbOpenDynaset, dbSeeChanges)
If Not (rs.BOF And rs.EOF) Then
rs.MoveFirst
rs.Edit
Else
rs.AddNew
rs![TelephoneType] = "Home"
rs![lnkStudent] = HoldpkStudent
rs.Update
rs.MoveLast
rs.Edit
rs![lnkPhoneNumber] = rs![pkPhoneNumber]
End If

in order to work with SQL on the backend. This is because
it wasn't assigning pkPhoneNumber (an autonumber field)
it's number until AFTER I saved the recordset.

This, of course, is causing a problem in other areas (not
to mention me having to update the code in numerous
places).

Is there something that I can do.... so it will assign
this field it's number immediately open doing the AddNew?

Thanks,

Kevin

-----Original Message-----
I assume that this field was an Access Autonumber Field when the backend was
hosted in Access. I believe that the upsizing tool converts this to an Int
on Sql200 but does NOT set the field to an Identity fields with an Increment
of 1.

You will need to edit the table to make this change, Relink the back end and
it will likely start working.

Another "problem" you might want to look out for is Access Yes/No fields get
converted to Sql Bit fields, but Access throws a hissy fit if this field is
null. So you will want to set all of the Sql bit fields to Not Null and
Default value to 0 (Zero).

RonW

Hi,

I am having a most perplexing problem.

I have moved all of my access tables to SQL Server 2000
and linked them to my access 2000 database.

Reading data is no problem... however when I try to add a
row to a table using code, I get an ODBC-Failed Call
error. After doing some testing, it appears the primary
key field on the form isn't being automatically updated
(it's NULL)... when I manually assign the next number to
the key field, it saves OK.

Here's my code:

Set DB = CurrentDb()
Set rs = DB.OpenRecordset("StudentForms", dbOpenDynaset,
dbSeeChanges)

rs.AddNew
rs![lnkStudent] = pkStudent
rs![lnkProgram] = CStr([Forms]![frmStudents]!
[lstRegisteredPrograms])
rs![Name] = cmbForm
rs.Update

On the rs.Update, the error occurs.

If I go out to the debugger and type:

rs![pkStudentForms]=19010

(for example), and then hit F8 to perform the update
command, it works fine.

Can anyone help me out?

Thanks,

Kevin Coleman

.