Problem using Access .ADP to connect to SQL Server

antic

antic

Perch
Hi all,

I'm using an Access 2003 .ADP file to connect to an SQL Server database. Usually it all works fine, but sometimes I get an error when simply trying to open a table:

"Stored procedure executed successfully but did not return any records."

This error does not even go away when I delete the ADP file and set it up again from scratch. Some tables will open up fine, others will result in this error. The tables that give the error seem to always be the same ones, so maybe there's something in the table design that Access ADP doesn't like?

Does anyone else have experience with this problem?
 
G'day Antic. I use Access too and never had a problem. Suspect it could be a faulty installation, so maybe worth trying a re-install?
 
antic said:
Hi all,

I'm using an Access 2003 .ADP file to connect to an SQL Server database. Usually it all works fine, but sometimes I get an error when simply trying to open a table:

"Stored procedure executed successfully but did not return any records."

This error does not even go away when I delete the ADP file and set it up again from scratch. Some tables will open up fine, others will result in this error. The tables that give the error seem to always be the same ones, so maybe there's something in the table design that Access ADP doesn't like?

Does anyone else have experience with this problem?
Click to expand...

I have recently run into the exact same problem using an Access 2003 .adp with sql server 2000. Have you been able to resolve the issue?
 
Nope. :( I've gone back to using MDB's connected to SQL thru ODBC using linked tables.

Haven't had the time to try reinstalling... that will have to wait for another time... pity there seems to be nothing on the net about this problem, which I'd assume many people are having.
 
I appreciate your reply. I honestly do not think it is a faulty install. In my case, the tables used to work just fine. I am not the only developer in the database, and I've been unable to determine when the symptoms started, but the point is that it used to work fine. There has to be something that Access does not like. It works fine through Enterprise Manager and the Query Analyzer. I can't spend more time trying to track it down right now, but if/when I get the answer, I'll post it here. Thanks again.
 
This is just a guess: Check if your tables have a primary key. Missing primary keys have caused me all kinds of trouble.

antic said:
Hi all,

I'm using an Access 2003 .ADP file to connect to an SQL Server database. Usually it all works fine, but sometimes I get an error when simply trying to open a table:

"Stored procedure executed successfully but did not return any records."

This error does not even go away when I delete the ADP file and set it up again from scratch. Some tables will open up fine, others will result in this error. The tables that give the error seem to always be the same ones, so maybe there's something in the table design that Access ADP doesn't like?

Does anyone else have experience with this problem?
Click to expand...
 
I found the solution! The problem is with SQL's extended properties of the tables in question. There are some properties that Access does not recognize and therefore cannot open the table or produces an error. I started removing the extended properties until I found the offending culprits. I ultimately went through and removed ALL of the extended properties from the tables / columns with the problems and the problem was immediately resolved. I did notice that some of the PKs needed to be reset, but this may have been me with a large degree of wrenching on it trying to fix the problem, but recheck the keys in any case.

To add, edit, or remove the extended properties, open the db in QA. Right click on the table and select 'Extended Properties' from the submenu. Do this at both the table level and for each column as needed.

Hope this helps!
 
Cool, thanks for that! Did you track down which property(s) in particular were causing it? Hopefully comments are ok, I like comments.. :)
 
antic said:
Cool, thanks for that! Did you track down which property(s) in particular were causing it? Hopefully comments are ok, I like comments.. :)
Click to expand...

I did not note the "offenders", I was going to just add the comments back in. I'm sure the comments are fine because virtually all of my columns have them and the majority of the tables came into the .ADP without incident.
 
Hi!

I had the same problem and found that the extended property 'MS_NameMap' was causing the problem (in my case).

Here is a script to remove any specified extended property from all tables in a database, just replace the databasename and property with your own values:

Code: 
USE databasename --replace with the name of your database
GO
DECLARE @propertyname VARCHAR(100)
SET @propertyname = 'MS_NameMap' --replace with the name of the property you want to remove

DECLARE @objname VARCHAR(100)

DECLARE extendedproperty_cursor CURSOR
   FOR SELECT objname FROM ::fn_listextendedproperty 
	(NULL, 'user', 'dbo', 'table', null, null, null)
	WHERE name=@propertyname

OPEN extendedproperty_cursor

FETCH NEXT FROM extendedproperty_cursor
INTO @objname

WHILE @@FETCH_STATUS = 0
BEGIN

	EXEC sp_dropextendedproperty @propertyname, 'user', 'dbo', 'table', @objname, null, null
	
	FETCH NEXT FROM extendedproperty_cursor
	INTO @objname

END

CLOSE extendedproperty_cursor

DEALLOCATE extendedproperty_cursor
 
Thanks heaps for that, brilliant! I'll give it a go as soon as I can and let u know how it went.

Cheers!! :thumb:
 
I m new to adp and sql server and i need help from you guys.
what i hv are the adp front end and sql server back end. How would i start to figure out that what needs to be done..
 
musman, I've stopped using ADP's now, I've found it to be a bit buggy. I've gone back to the old-fashioned way:

Create an ODBC link to your SQL database, then create a new Access mdb file and add "linked tables" to it, using the ODBC connection.
 
it was buggy like that in o2k also.. i never even bothered to try to get adp to work (not very hard at least)
 
You must log in or register to reply here.
Back
Top