Some ADP tables won''t open
-
I have an odd problem where some tables won't open in an Access 2003 project. This is effecting about 5 tables (out of 46). It has been workng fine for well over 2 years. No services packs or patches have been applied recently.
All the problem tables open fine in Enterprise Manager and Query Analyzer.
If I try to open or design a problem tables in Access 2003 I get one of the following -
- Nothing happens
- I get a message "the Stored Procedure ran sucessfully but did not produce any results" (this is a table!)
- I get a message that "field name is read only", where the field name does not even exist in the problem table
So far I have tried the following, all without any success -
- Check the database with DBCC CHECKDB, no errors
- Connected to the server from other pc's with different versions of Access (XP & 2003)
- The tables all look fine in Access Database Diagrams
- All existing queries/views seem to work OK. If I create a new view as SELECT * FROM etc' this works OK, but SELECT field1, field2 etc gives the same problems as above
- Deleted the database and restored from a backup
- Deleted the database and restored from the production server
- Started a new Access Project
- Rebooted the server
- Check for free disk space
- The problem tables are all the "larger" tables, but even so, none are very big, between 2000 and 30000 records.
I've run out of ideas and would be greatful for any suggestions on where I go from here.
Many thanks
Tim -
Can you open the profiler, try to access the problem tables and paste the queries sent by access 2k3 on the server?
-
Sounds like your ADP client is corrupted. There may be properties (such as the ones that control the column widths and orders in the datasheet) that are stored in the ADP itself that may be corrupt. It does not sound like there is any problem with your database or server. Create a new ADP and import all your objects (except tables and "queries") into it. Then try it again.
If you have already done this, and it still does not work, you may have corruption in the Access extended properties that Access writes automatically into SQL Server tables. To view or edit some extended properties, open your table in design view, and then open the properties window. Click the "Data" tab to view some of the properties.
Some possible ways to clean up extended properties:
1) Rename the problem Tables. Then, 2) Copy the problem tables to new tables (using the original names) with exactly the same structure, relationships, etc
2) Drop all the extended properties on the problem tables using sp_dropextendedproperty. (Use fn_listextendedproperty to list the properties on your tables).
3) Use a 3rd party tool (e.g. DbaMgr2K - free) that lets you edit them graphically.
Using these techniques can have adverse consequences for your database, so make sure you have a current backup!!!!
HTH
--Rich
-
To be a bit simpler about my first suggestion:
Just create a new ADP and connect to your databse. Then try to open up the problem tables. If it works in the new ADP, then your old ADP is probably corrupt.
-
Hi Remi, Richard,
Many thanks for your suggestions.
I'm never sure what I'm seeing with the Profiler, but with a table that works, I'm getting a SELECT *, sql_variant_property(value, 'basetype') AS type FROM ::fn_listextendedproperty(NULL,N'user',N'dbo',N'table',N'TNTPackCode',N'column',NULL) ORDER BY PATINDEX(N'MS_DisplayControl', name) DESC followed shortly after by a SELECT * FROM "dbo"."TNTPackCode" On the table that fails, all I get is the first select.
I had already tried creating a new ADP, but I have now dropped all the Extended Properties for one of the tables and then created another new ADP. Still no luck. (Although I have noticed that if you open a table that works OK, then try to open one that does not work, the error I see about a field name being read only, always seems to be the field name of the primary key in the table that I opened previously) (Very wierd).
I then tried, as per Richard suggestion, renaming a problem table, creating a new table with the same structure and name as the original table, and using DTS copied all the data from the old to the new table, with Identity_Insert on. Created a new ADP and it worked! That table then opened OK in my original ADP.
So with your kind help, I have a workaround, but I'm still puzzled what was causing this problem. Droping all extended properties and creating a new ADP, I would have though would have eliminated most sources of potential problems, but mine still continued.
Once again, very many thanks for your guidance and suggestions.
Tim -
Glad you got it worked out... there's nothing out of the ordinary with the trace you sent me. Next time you see something wrong with the queries you can repeat those steps. Paste the results in qa and rerun, that way you'll get the actual error message instead of the general oledb error.
-
I suspect you had some corruption in a system table, perhaps related to extended properties, or perhaps related to something else that Access writes into the system tables directly.
It's a strange case, because usually these kinds of problems are usually due to corruption in the Access client. Oh well, live and learn...
In any case, I'm glad you finally got it to work.
Rich
-
I've had (and have...) a similar problem with my ADP (2002 version), but in my case it says "can't find column XXX". A couple of table just won't open in adp but are fully accessible by any other mean (including in forms, access queries, vba, etc.).
I've tried many times to correct the problem, but I just learned to live with it. I think I found a pattern to explain the problem (still can't fix it thought): it seems that when you delete a column in EM while your ADP is open, you won't be able to open it anymore in access. I doesn't always do that... but often.
Maybe this info can help you find the reason of your problem...
Erik
-
Hi Erik,
It took me something like 3 hours to rename all the problem table out of the way, create new tables, transfer the data and re-build and sort out the reationships/constraints etc. But everything is back working normally again, and an exercise well worth doing.
Tim -
Hi Tim,
I knew that the problem would be corrected if I rebuild the tables... but I not that easy on a production server
So I just learn to not open the table from access!
Erik
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply