Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2001-06-04
23
Medium Priority
?
318 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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 …

715 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