Access / SQL 2000 -- ODBC Linked Table Problem

  • Thread starter Thread starter Mike Morgan
  • Start date Start date
M

Mike Morgan

I'm using Access XP as a frontend for SQL 2000. I have linked my main form
to the SQL database via ODBC links. I'm having a strange problem. I have
some subforms on my main form. When I attempt to update a text box or combo
box on the main form I get this error:

ODBC - Update on a linked table failed. Maximum stored procedure,
function, or view nesting level exceeded. (limit 32) (#217)

What I don't understand is that I can update the combo and text boxes on the
subforms fine and they are linked to their data in the same way as the main
form is linked to its data. It's all ODBC linked It's only when I try to
update the data on the main form that I run into trouble. What have I done
to this form that would prevent it from being able to do ODBC updates to the
linked table? BTW: The main form is linked to a single table. I have
experimented with making it a query with no joy. It still throws the same
error when I attempt to update anything on the main form. What have I
missed?
 
The database was already in Access mdb. I am slowly converting it to use
SQL. It seemed to me at the time that the easiest thing to do would be to
add the odbc table links as needed. But, virtually all of its over in SQL
now. I really should go ahead and do that. But, that's why. Just trying to
maximize time.
 
Since I was stuck, I went ahead and moved it into an ADP. But, its still
throwing the same error on the main form. I can edit everything else, but
not data in controls on the main form. The application is much faster now.
But, not much consolation if I can't get this solved.
 
Maybe something like a trigger on your SQL-Server table or a recursive
foreigh key relationship?

S. L.
 
Also, some column names, like NO or DATE, may confuse the ODBC driver.

BTW, while you're at it and now that you have moved to ADP, you can take the
precaution of replacing ODBC with OLEDB-SQL-Server as your driver.

S. L.
 
Thank you for the help. As it turned out, I had same update trigger on the
table by two different names. Apparently, SQL doesn't like that. I removed
one of them and it works fine now. Thanks again.
 
Can you open up this table in the table view, and edit it?

No use trying to get something to work on a form if you can't even open and
edit the table directly.

I would test the above first..as then you then will know if it is your
actual link, or something in the form.

And, as you correctly state, there is no reason at all to convert to a adp
for a existing applications.

In fact, I am written a new complex application right now for sql
server..and I am avoiding using
a adp project, as I find a odbc project tends to have more flexibility then
using adp..
 
In fact, I am written a new complex application right now for sql
server..and I am avoiding using
a adp project, as I find a odbc project tends to have more flexibility then
using adp..
Click to expand...

This is the Microsoft-recommended approach for the reasons you discuss
in your post. ADPs do not give you the flexibility of an mdb and will
not be forwards-compatible with SQL Server 2005 for any design
features. You will only be able to connect to a "finished" database
running in 2000 compatibility mode.

--Mary
 
The database was already in Access mdb. I am slowly converting it to use
SQL. It seemed to me at the time that the easiest thing to do would be to
add the odbc table links as needed. But, virtually all of its over in SQL
now. I really should go ahead and do that. But, that's why. Just trying
to
maximize time.
Click to expand...
 
Back
Top