Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

Get list of Access database macros, via a query call from VC++ program

We have a VC++ program, need to make a call to an Access database, Access 97 (maybe later date to Access 2000), to get a list of the macros there.  We will use that by launching Access from the VC++ program with a user's choice of macros in the command line, to allow them to print a report from Access.  What is a query or SQL command that could be used from VC++ to do this?
0
davspa
Asked:
davspa
  • 11
  • 6
  • 2
  • +2
1 Solution
 
lmerrellCommented:
Here.  This will do it:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=-32766));


lmerrell
0
 
davspaAuthor Commented:
Thanks, I will try it...
0
 
davspaAuthor Commented:
To Imerrell,
Do you know the reason for this - I tried it and it got the message "Record(s) can't be read; no read permission on 'MSysObjects'.  I am using a MS Access 97 database.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
lmerrellCommented:
If this is a secured database you would have to give the users read permission.
0
 
davspaAuthor Commented:
To Imerrell,
Do you know the reason for this - I tried it and it got the message "Record(s) can't be read; no read permission on 'MSysObjects'.  I am using a MS Access 97 database.
0
 
davspaAuthor Commented:
error in my sending a repeat of last comment  -  also, it could also be a problem with database structure or use of the MFC DaoRecordset object; I tried using a different setup on that object and got the message that "Item not found in this collection"
0
 
lmerrellCommented:
Does the SQL work for you as a query in the database itself?
0
 
NosterdamusCommented:
Hi davspa,

1. Open the database in Access.
2. Select "Tools->Options" from the main menu.
3. Select the "View" tag.
4. Check "Hidden Objects" and "System Objects"

Now you can see MSysObjects table.

WARNING: Don't mess-up with this table, just make read calls to it as it is a build-in table of Access.

5. Select "Tools->Security->Users & group permission..." from the main menu.
6. Select MSysObjects from the list
7. Check the Read Data option.

Hope this helps,

Nosterdamus
0
 
dovholukCommented:
don't know if you want/need it or not, but here's a VBA method if you'd like it. copy / paste into a new module...

'********************* begin copy *************************
Function EnumerateScripts()
   
   Dim i As Integer

For i = 0 To CurrentDb.Containers(5).Documents.count - 1
   MsgBox CurrentDb.Containers(5)(i).name
   'or some other vba code here, like adding to a names collection or what not...
Next i

End Function
'********************* end copy *************************


dovholuk

0
 
dovholukCommented:
didn't read the question fully.

please feel free to tell me how stupid i am! lol

dovholuk
0
 
davspaAuthor Commented:
To Nosterdamus - thanks for help, I need a solution for the end-user to use, though.

to dovholuk - maybe could use that later if cannot get access directly, but currently am looking to get list using SQL.
0
 
davspaAuthor Commented:
Imerrell, I think query is ok, but am still checking, may have to modify for the object I am using; am still checking this object setup, and database access.
0
 
lmerrellCommented:
davspa - The directions Nosterdamus gave you were to open the permissions on the system objects table to Read access.  This is what I mentioned earlier to get the SQL I gave you working.
0
 
davspaAuthor Commented:
Nosterdamus - I apologize for that, didn't read your response fully.  I tried that, and it did give read permissions, as Imerrell said in last comment.
0
 
davspaAuthor Commented:
Imerrell, I understand what was meant now in your first response on the secured database - you said the same thing; I probably read your answer too quickly too, sorry.  I think it is close to working now - have tried above solutions, & will award points, but may be in a couple of hours or so.
0
 
NosterdamusCommented:
Thanx imerrel, I forgot to mention that it's the instructions to open the security... :-(

davspa, hope that you'll get up and running before the end of the day... ;-)

Nosterdamus
0
 
lmerrellCommented:
No problem, davspa.  It's sometimes hard to communicate clearly in this medium.  It sometimes takes awhile but we usually get the message across.  ;-)

lmerrell
0
 
pbryanCommented:
Sorry for jumping in late ;-)  

I was able to query the MSysObjects table without changing the security so that I could query from another program by setting the DBEngine.SystemDB property to full path of the default system.mdw (the security) file....ok so its only vbcode but you should be able to do the same kind of thing in vc with the MFC object for the daodatabase

Private Sub Form_Load()
Dim strDBName As String
Dim strSQL  As String
Dim dbConn As Database
Dim rsData As Recordset
strDBName = "F:\Documents and Settings\Administrator\My Documents\barcode.mdb"
strSQL = "SELECT MSysObjects.Name  From MSysObjects WHERE (((MSysObjects.Type)=-32766));"
DBEngine.SystemDB = "C:\WINNT\System32\SYSTEM.MDW"
Set dbConn = DBEngine.OpenDatabase(strDBName)
Set rsData = dbConn.OpenRecordset(strSQL)
While Not rsData.EOF
    Debug.Print rsData!Name
    rsData.MoveNext
Wend
End Sub
0
 
davspaAuthor Commented:
pbryan, Thank you for the added info.  I must award points to previous contributor, as I told him I would in previous comment.  If this is not fair, I am willing to discuss with a moderator.
0
 
davspaAuthor Commented:
code given does work, and when I made comment that I couldn't get access to dbase, lmerrell did advise also that I needed to set security settings.
0
 
lmerrellCommented:
Glad I could help, davspa.  Have a great day!

lmerrell
0
 
davspaAuthor Commented:
The prev comment from me is for the accepted answer at top.

Also comment to pbryan was not intended to be harsh.  I apologize to him if it is.
0
 
pbryanCommented:
thats quite alright davspa...I saw your comment about giving the points to Imerrell, I was just saying that you do not need to change the security on the MSysobjects table to query it
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 11
  • 6
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now