Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2001-06-04
23
Medium Priority
?
321 Views
Last Modified: 2008-02-01
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
Comment
Question by:davspa
  • 11
  • 6
  • 2
  • +2
23 Comments
 
LVL 7

Accepted Solution

by:
lmerrell earned 400 total points
ID: 6152591
Here.  This will do it:

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


lmerrell
0
 

Author Comment

by:davspa
ID: 6152647
Thanks, I will try it...
0
 

Author Comment

by:davspa
ID: 6152764
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 7

Expert Comment

by:lmerrell
ID: 6152798
If this is a secured database you would have to give the users read permission.
0
 

Author Comment

by:davspa
ID: 6152824
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
 

Author Comment

by:davspa
ID: 6152849
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
 
LVL 7

Expert Comment

by:lmerrell
ID: 6152871
Does the SQL work for you as a query in the database itself?
0
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6152897
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
 
LVL 8

Expert Comment

by:dovholuk
ID: 6152902
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
 
LVL 8

Expert Comment

by:dovholuk
ID: 6152908
didn't read the question fully.

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

dovholuk
0
 

Author Comment

by:davspa
ID: 6152963
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
 

Author Comment

by:davspa
ID: 6152987
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
 
LVL 7

Expert Comment

by:lmerrell
ID: 6153001
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
 

Author Comment

by:davspa
ID: 6153066
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
 

Author Comment

by:davspa
ID: 6153118
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
 
LVL 7

Expert Comment

by:Nosterdamus
ID: 6153143
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
 
LVL 7

Expert Comment

by:lmerrell
ID: 6153189
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
 
LVL 3

Expert Comment

by:pbryan
ID: 6153656
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
 

Author Comment

by:davspa
ID: 6153802
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
 

Author Comment

by:davspa
ID: 6153811
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
 
LVL 7

Expert Comment

by:lmerrell
ID: 6153837
Glad I could help, davspa.  Have a great day!

lmerrell
0
 

Author Comment

by:davspa
ID: 6153845
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
 
LVL 3

Expert Comment

by:pbryan
ID: 6154813
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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question