Solved

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

Posted on 2001-06-04
23
304 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 100 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

820 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