ADP - record source doesn't exist

  • Thread starter Thread starter usenet_e
  • Start date Start date
U

usenet_e

Hello,
few users have problems with our application (ADP+SqlServer2000). When
they want to open a report which source is a stored procedure, they get
message that "record source specified..... do not exist" - of course,
it's not true.
More details:
- other users don't have this problem
- they have WinXP
- all users have the same permissions to SQL objects
- I've checked Microsoft KB
(http://support.microsoft.com/kb/243532/en-us) and they write about
"dbo." before source name - I use that, so it's not a solution. What
more, in application there are lists which have SP as row source and
without "dbo." and it works. (In lists a row source is defined as:
"exec <sp_name>", in reports as: "dbo.<sp_name>").
- computers have all service packs, new version of MDAC, etc.
- I asked another user, who doesn't have this problem on his machine,
to login on "bad" machine and he couldn't open reports too.

Maybe someone could help? because I don't have more ideas.
 
ue> few users have problems with our application (ADP+SqlServer2000). When
ue> they want to open a report which source is a stored procedure, they get
ue> message that "record source specified..... do not exist" - of course,
ue> it's not true.
ue> More details:
ue> - other users don't have this problem

Run Profiler on sql server for those who don't have the problem, and ???
those who have, and compare.

Vadim Rapp
 
Try removing dbo from the Record Source and put it as the Record Source
Qualifier instead.

Are you using a Windows account (integrated security) or a SQL-Server
account to access the SQL-Server from ADP?
 
Vadim Rapp napisal(a):
ue> few users have problems with our application (ADP+SqlServer2000). When
ue> they want to open a report which source is a stored procedure, they get
ue> message that "record source specified..... do not exist" - of course,
ue> it's not true.
ue> More details:
ue> - other users don't have this problem

Run Profiler on sql server for those who don't have the problem, and ???
those who have, and compare.

Vadim Rapp
Click to expand...

I've just done it, I hope that I done it ok, because usually I don't
have access to this tool.
I compared 2 users (for example: user A -works ok, user B - can't open
reports), and it seems that in user's B log there is no trace about
trying execute a procedure which is a report source.

What now? ;)
 
Sylvain Lafontaine (fill the blanks, no spam please) napisal(a):
Try removing dbo from the Record Source and put it as the Record Source
Qualifier instead.

Are you using a Windows account (integrated security) or a SQL-Server
account to access the SQL-Server from ADP?
Click to expand...


I use Windows authentication.

Hmmm.. I can't find such property as record source qualifier - I don't
have English version of Access.
I found description of this property in Google, but I can't find in my
Access. Is it only VBA property?
 
usenet_e napisal(a):
Sylvain Lafontaine (fill the blanks, no spam please) napisal(a):


I use Windows authentication.

Hmmm.. I can't find such property as record source qualifier - I don't
have English version of Access.
I found description of this property in Google, but I can't find in my
Access. Is it only VBA property?
Click to expand...

Isn't it only Access 2003 property? (I have MsAccess2000)
(http://search.msdn.microsoft.com/search/default.aspx?siteId=0&tab=0&query=RecordSourceQualifier)
 
Actually, it started in Access 2002 (XP). If you're on 2000, no, you won't
have that property, so as far as I know, you'll have to leave the "dbo"
where it is.



Rob
 
If you are using A2000, then the first thing to do would be to upgrade to
A2003. Even without this error, this would be a good idea.

Second, another possible solution would be to check the references and make
a full decompilation followed by a compact/repair of the project directly on
one of the machine with this error. You can even try creating an empty
project and importing everything. You could also try to directly open the
stored procedure from the queries window; both from the same project or from
an empty project.
 
I can't now upgrade Access - our company have some standards of
software, and I can't change it just like that.

I will try to do these all things with the project on this machine, as
you said.
We will see if it helps.

Thanks.


Sylvain Lafontaine (fill the blanks, no spam please) napisal(a):
 
Finally, everything works.

When I created new database on user's computer and connected to
SqlServer, I saw that end of stored procedures names had ";1". So, it
means that something is wrong with service packs.
MsAccess informed that it had SP-3 but it turned out that MsAccess was
installed additionally - after Office Stnd. and SP-3.
So, it didn't have SP-3 really ;)

Anyway, thanks a lot for the help.

usenet_e napisal(a):
 
yeah those ;1 are a seldom discussed feature of all sql server

create procedure mySproc;1
as
insert into mytableAudit()
select * from myTable1


create procedure mySproc;2
as
insert into mytableAudit()
select * from myTable2


when you run 'exec mysproc' it is smart enough to know that you want to
run mysproc;1 and mysproc;2

I read about this only in one place; in a book called 'SQL Server 7.0
Secrets'

hope that helps and congrats..
i strongly agree with these folk; Access 2002/2003 is _SIGNIFICANTLY_
more fun that 2000.. I really really really reccomend moving everybody
as soon as possible
 
Hello usenet_e,
You wrote in conference microsoft.public.access.adp.sqlserver on 8 Nov 2006
00:39:25 -0800:


ue> Vadim Rapp napisal(a):
ue>>> few users have problems with our application (ADP+SqlServer2000).
ue>>> When they want to open a report which source is a stored procedure,
ue>>> they get message that "record source specified..... do not exist" -
ue>>> of course, it's not true. More details: - other users don't have this
ue>>> problem
ue>>
ue>> Run Profiler on sql server for those who don't have the problem, and
ue>> ??? those who have, and compare.
ue>>
ue>> Vadim Rapp

ue> I've just done it, I hope that I done it ok, because usually I don't
ue> have access to this tool.
ue> I compared 2 users (for example: user A -works ok, user B - can't open
ue> reports), and it seems that in user's B log there is no trace about
ue> trying execute a procedure which is a report source.

ue> What now? ;)

so it looks like Access is thinking that the stored procedure does not
exist, so it does not even try to run it.

If it's ADP, you should be able to open the database window, as well as
Connection. Hopefully this will explain why it does not see the s.p.

Most likely, it's some permission problem. Triple-check your statement "all
users have the same permissions to SQL objects". You can also check if they
are able to run this stored procedure using simple vbscript like

set c=createobject("adodb.connection")
c.open <connection string> ' ( from http://www.connectionstrings.com/
c.execute "storedprocedure"

Vadim Rapp
 
Back
Top