Data access errors after upsizing from Jet

[robert demo via AccessMonster.com]

I upsized my backend to SQL Server. Retrieval of data for display in my forms
seems to work fine. However on some of the forms where editing and adding of
new records is allowed some errors occurs. The errors are associated with
trying to add a new record or edit an existing one.

Specifically, the lines at which the error occurs are at (DAO objects_

rs.AddNew
rs.Update
Set rs = ..

The Error is that the ODBC call failed.

I think this has to do with the tables, but all of the tables have keys. Note
that there are a number of tables that can be updated just fine from the
forms.

Help is greatly appreciated even if you're not sure what the problem is as I
am totally stumped.

Thanks.

 

[Robert Morley]

There are probably a few things that it could be, but one thing I've had
inordinate amounts of difficulty with are text/ntext fields (Memo fields in
Access). If you can, try changing any text fields to varchar (holds up to
8000 characters) and ntext fields nvarchar (up to 4000 unicode characters).
See if that resolves the problem.

By the time we switched to SQL Server, I was using ADO to do all my
programming, so I'm not sure about any DAO-specific issues.



Rob

 

[robert demo via AccessMonster.com]

Thanks for the response. But I just found the MS Article on how to get more
info out of that error message and it looks like it is not an SQL Server
problem. I forgot that I had modified the key for the table but the code is
not giving the added key field a value so it's a 'Null' in the key problem.

Don't know about the other problems yet.

Thanks.

There are probably a few things that it could be, but one thing I've had
inordinate amounts of difficulty with are text/ntext fields (Memo fields in
Access). If you can, try changing any text fields to varchar (holds up to
8000 characters) and ntext fields nvarchar (up to 4000 unicode characters).
See if that resolves the problem.

By the time we switched to SQL Server, I was using ADO to do all my
programming, so I'm not sure about any DAO-specific issues.

Rob

I upsized my backend to SQL Server. Retrieval of data for display in my
forms

[quoted text clipped - 21 lines]

Thanks.

 

[robert demo via AccessMonster.com]

Your right about the memo fields they are causing me a lot of problems. But,
luckily, in all cases where I include memo fields in an SQL statement to
retrieve rows, I have back-added an Autonumber field to the table. Now I've
rewritten the SQL statements to include only the AutoNumbers when I need to
select a specific row to allow the user to edit the record. Initial
selection isn't a problem because I return a number of rows into a listbox
based on a limited set of criteria. The listbox contains the AutoNumber
(hidden). When the user selects an item in the listbox, then a new form
opens for editing. That form is populated based solely on the autonumber.
Actually very easy. SQL Server is now making me work with Autonumbers (when
appropriate) instead of fighting them like I had in the past.

Thanks for the response. But I just found the MS Article on how to get more
info out of that error message and it looks like it is not an SQL Server
problem. I forgot that I had modified the key for the table but the code is
not giving the added key field a value so it's a 'Null' in the key problem.

Don't know about the other problems yet.

Thanks.

There are probably a few things that it could be, but one thing I've had
inordinate amounts of difficulty with are text/ntext fields (Memo fields in

[quoted text clipped - 12 lines]