list of ms access stories queries in asp

red_fiesta · December 18th, 2006, 10:12 AM

I have been searching and found out that stored procedures are called stored queries in access

This is an interesting link

http://www.stardeveloper.com/article...1050101&page=1

but i still havent found how to retrieve a list of ms access stored queries in asp... anyone know?

seananderson · December 18th, 2006, 12:28 PM

Open a query.

Right-click on the header and switch to SQL View

Paste this in and take a look at the datasheet

SELECT MSysObjects.Name, MSysObjects.Type, MSysObjects.Connect
FROM MSysObjects
WHERE (((MSysObjects.Type)=5))
ORDER BY MSysObjects.Name;

Interesting reading for you....
http://www.perfectparadigm.com/tip001.html

Regards,

Sean Anderson

red_fiesta · December 18th, 2006, 08:37 PM

Thanks...

I have tried to run this code

Code:

<%
Dim rsUsers, adminID

  set rsUsers=Server.CreateObject("ADODB.Recordset")
  strSQL = "SELECT MSysObjects.Name, MSysObjects.Type, MSysObjects.Connect FROM MSysObjects WHERE (((MSysObjects.Type)=5))ORDER BY MSysObjects.Name;" 
  rsUsers.open strSQL, strConnectAdmin

%>

but get the following access error...

Microsoft JET Database Engine error '80040e09'

Record(s) cannot be read; no read permission on 'MSysObjects'.

How do i setup permission on this?

Thanks

mat41 · December 18th, 2006, 08:50 PM

As a result od primarily using SQL Server, I have limited access experience however have never seen your above error stating a tble name.

Have you set permissions on the folder where your .mdb file is? Give the IUSR read and write permissions
to this location. however I seen to remember the error is more like:
'can not use an updatable query' (or something like that)

Wind is your friend
Matt

red_fiesta · December 18th, 2006, 09:06 PM

Yeah I have set permissions for IUser i am sure its not that,

I have been looking on google and others have had the problem, just not sure how they solved it!

mat41 · December 18th, 2006, 09:21 PM

Are you successfully getting records on any page?
I suspect it what you have in your connection string, are using a UN and PW? I dont, and use:
"DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("/data/dbName.mdb")
What does your look like?

Wind is your friend
Matt

dparsons · December 18th, 2006, 11:29 PM

To elaborate on Matts point a bit more, if you are using a DSN and providing a username and password to connect to the database it may have to do with the user account that you are using to connect to the database. In SQL Server, for example, I could get this error if the SQL user i am using to connect to the database does not have permissions to execute any commands on that table.

Try this tho, run that code inside Access itself, not from code, and see if it executes. It it does you can narrow it down, fairly well, to it being a permissions issue from calling this query in code.

-------------------------
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

^^Thats my signature