Table behaves like stored proc

[Moebius01]

I've got an odd problem. I have an Access 2003 project linked to a SQL Server 2000 database. For some reason, there are 2 tables that I cannot open. Double clicking the first time returns "Stored Procedure executed but did not return any records." After that it will return the field ID is read only (first field, autonumber).

There are no special properties on the table, no triggers, links, or anything else that I can think of. I've tried creating a new project to no avail. The tables were both accessible from Access 2000.

Any ideas?

 

[ESquared]

Can you query the tables from Query Analyzer?

Have you tried copying the tables in Access to new names (make a database backup first)?

Have you tried exporting the tables in Enterprise Manager and importing them?

Have you tried renaming the tables, and doing a SELECT INTO?

Have you tried renaming the tables, scripting new copies of them using QA or EM, and doing an INSERT?

 

[Moebius01]

I've tried most of these. Here's what I found after more testing.

This is limited to Access 2003. Connecting a 2000 ADP to the same database works fine for the two tables in question.

The tables can be accessed, read, and written to from QA and EM.

I cannot rename these 2 tables as this is a production server and the realtime data system running on this DB needs them. My dev envirnoment is in transit to a new location, but I'll be doing some tests once it's back up.

I copied the table into an Access DB and there were no problems encountered. I also checked again to make sure this table had no unique properties compared to others in the DB.

So far, all I can come up with is that for some strange reason, Access 2003 does not recognize these as true tables, and gets a little confused when trying to access them.

 

[ESquared]

Do the table names coincide with some reserved word in Access or some reserved word of a Reference you've added (just a shot in the dark)?

Have you tried scripting the tables with all scripting options turned on and comparing to working similar tables (probably)?

Have you done dbcc checkdb stuff?

Have you tried editing something in the tables with Enterprise Manager, which handily scripts out for you the whole process of dropping constraints/integrity/etc., renaming, copying, filling the new table, and recreating the dropped items? This would temporarily lock those tables but if you did it an off-peak time it might not take so long (try it with some copies, first). Recreating the tables could eliminate any strange setting that's interfering with Access 2003 reading them.