Trouble Opening Tables through ADP

[ianlukekane]

I'm currently using an Access 2003 adp file to connect to SQL Server
2000. Everything works flawlessly except for one particular issue. When
I try to view a table's contents through the adp the table doesn't
open. It totally freezes. No error messages or anything. An interesting
sidenote is that I CAN view the table in design mode through the adp.

I thought that this issue could be related to the extended properties
stored in SQL Server, but I removed them all for several tables and am
still encountering the same problem.

My only other lead is that I originally created all of my tables in an
mdb and copied them to SQL via the upsizing wizard. It was only later
that I created an adp. So I'm not sure if somehow there are properties
saved from my original upsizing that I no longer want to be there. For
instance I changed the names of several tables once they were in SQL
and before I created the adp.

Of the many databases that I'm currently using this is the only one
that has this issue. All the others allow me to open tables through the
adp.

Any suggestions?

 

[Sylvain Lafontaine]

First thing to do: look for a missing primary key for this table.

Usually, extended properties are not a problem in ADP because there are not
really used; so you should be fine with those. However, as with anything
else, there is always the possibility of a problem/bug in ADP in this
regard.

I would suggest that you try to recreate a second table with the same schema
and see what happens. (This can easily be done by using the EM to generate
the required SQL script.)

 

[ianlukekane]

Sylvain, thanks for the response. I've tried to recreate a few tables
via scripting but still run into the same issue. And all my primary
keys are definitely accounted for.

After playing around a bit more I've noticed that I can do EVERYTHING
through the adp with the exception of opening tables. I can design
tables/views, modify tables/views, access triggers, access diagrams,
etc. But no matter whether the table has 3 columns or 200 I'm unable to
open it. I've mirrored the same issue on several different computers
with several different user accounts.

I also considered that it might be a timeout issue in some way but
after playing with the OLE/DDE timeout value I'm fairly certain that
this is not the case. So I'm not exactly sure where to turn.

Any other suggestions?

 

[Robert Morley]

I don't really have much to suggest either, but one thing occurs to me that
might help narrow it down a tiny bit: create a table through the ADP and
see if you can open it afterwards. If you can, you know it's some specific
issue with the existing tables; if you can't, you know it's more likely to
be a more general Access or server issue (or something behind the scenes
like client DLL issues, etc.).

Oh, and of course, you can also try opening the table with Access 2002 or
2000 if you have a computer with one of those available. (Note that 2000's
got a lot of its own bugs with ADPs, so maybe not the best test, but worth a
try if that's all you've got.)



Rob

 

[ianlukekane]

Robert, thank you for the insights.

We've tried to create a table through the ADP and are having the same
issue. So as you said it's most likely a general Access or server
issue. If anyone has had a similar issue I would love to hear from you!

Thanks again everyone.

 

[Sylvain Lafontaine]

Does the account that you are using to connect to SQL-Server has ownership
role for the database? If not, did you try with one?