Solved

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

Posted on 2001-06-04
23
276 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
 
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now