Users can't use createquerydef

  • Thread starter Thread starter Mark Schirle
  • Start date Start date
M

Mark Schirle

Hi,
I am converting from A97 to A2k3. I have a form that allows users to
make choices and then hit a button to see results. Behind the scenes I'm
making a sql statement. So when they hit the button i first delete the
query so that I can get rid of the old one and then
set qrydef = currentdb.createquerydef("qryName", strSQL)
the reason I create a query with a name is so I can then open a report
and the report uses the "qryName" as the recordsource.
As admin I have no problem. But users aren't so lucky. I've granted full
permission on New Queries in security. I get the error "No recordsource
found". So the query is not getting made.
So what am I doing wrong. (This has worked fine in A97)
TIA for your help!
 
HI Mark

Sounds like this is a permissions issue, and you have denied permissions for
users to create queries.

Instead of deleting the query and recreating, you could merely reassign its
SQL property, e.g.:
CurrentDb().QueryDefs("qryName").SQL = strSQL

Alternatively, you could save the report unbound, and assign the string
directly to the RecordSource of the report in its Open event (unless it is
actually a subreport):
Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = strSQL
End Sub
 
Are you using Front-End / Bach-End and each user has his own copy of the
Front-End?

If all users share the single MDB file, there is a slight difference in that
A97 allows the user to save the design of the database while it is being
shared. With A2K or later, you need exclusivity to be able to save the
changes in the design of the database.

HTH
Van T. Dinh
MVP (Access)
 
Yes I think this is the problem. I have had a hard time grasping querydef for
some reason but I think I will use the solution provided on the previous
response. I will just replace the sql string.
Thanks for your help!
 
Back
Top