28-Jul-2006, 08:42 AM
|  | Guest | | | | | | | | | | Interesting SQL commands
Today I see a PostgreSQL SQL book and find some interesting commands below.
Is there any similar SQL commands under ADO??
1. Meta-Command
\d_\dt List All TAbles
\dv List All View
...... List All Query (equal to View??)
...... List All Users
2. Frequency Statistic
Select n_distinct, most_common_vals, most_common_freqs FROM ..... Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/14007-interesting-sql-commands.html
==> these 3 terms are system terms. Result is showing N-th data/value
occuring with top frequency in a table's field.
Besides, please help:
3. There is a System table in MS Access "MsysObjects".
I can open it in MS Access easily, without any password needed, but not
in my application.
How to solve it?
4. I have searched through Google with keywords SQL ADO or SQL "OLE DB", I
feel very strange that there is only a few data for the former, and None for
the latter.
Even though their ultimate data engine may be ODBC or Jet, their SQL
language in User-Level should be defined by themselves.
(Otherwise, there will not be so many SQL language around MS
applications :> )
Could you introduce me any tutorial and handbook web site for it?? Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14007 
 Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh! |
28-Jul-2006, 08:42 AM
| | Guest | | | | | | | | | | Re: Interesting SQL commands
For all Tables
Create a new Query ( MSysObjects is hidden - leave it so - can be changed
through tools/options/show hidden objects)
Hit cancel for the table selection
View SQL
Write SELECT Name From MSYSOBJECTS A WHERE A.TYPE IN (1,6,4)
This will give you all the tables (native, Linked, ODBC)
For Queries (Views)
SELECT A.NameFROM MSYSOBJECTS A WHERE EXISTS ( SELECT 'X' FROM MSysQueries Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14007
B WHERE B.ObjectId =A.Id)
To get all the users you'll need to use VB Code:
Sub CreateUserTable()
Dim Ws As DAO.WorkSpace
Dim Db As DAO.Database
Dim Usr As DAO.User
Set Ws = DAO.DbEngine.Workspaces(0)
Set Db = CurrentDb
Db.Execute "DROP Table USysUsers"
Db.Execute "CREATE TABLE USysUsers (UserName char(32))"
Db.Execute "CREATE UNIQUE INDEX PK_USysTable ON USysUsers (UserName) With
Primary"
For Each Usr In Ws.Users
Db.Execute "INSERT INTO USysusers (UserName) VALUES ('" & Usr.Name & "')"
Next ' Usr
Set Db = Nothing
Set Ws = Nothing
End Sub
now you can use (to show users - including "internal users" - Engine,
Creator)
SELECT UserName FROM USysUsers
For MSSQL, Oracle(8i +) you can query:
INFORMATION_SCHEMA.TABLES etc Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14007
Pieter
"Cicada" wrote in message
news:uEl%23O5PsGHA.4444@TK2MSFTNGP06.phx.gbl...
> Today I see a PostgreSQL SQL book and find some interesting commands
> below.
> Is there any similar SQL commands under ADO??
>
> 1. Meta-Command
> \d_\dt List All TAbles
> \dv List All View
> ...... List All Query (equal to View??)
> ...... List All Users
>
> 2. Frequency Statistic
> Select n_distinct, most_common_vals, most_common_freqs FROM .....
> ==> these 3 terms are system terms. Result is showing N-th data/value
> occuring with top frequency in a table's field.
>
> Besides, please help:
> 3. There is a System table in MS Access "MsysObjects".
> I can open it in MS Access easily, without any password needed, but not
> in my application.
> How to solve it?
>
> 4. I have searched through Google with keywords SQL ADO or SQL "OLE DB", I
> feel very strange that there is only a few data for the former, and None
> for the latter.
> Even though their ultimate data engine may be ODBC or Jet, their SQL
> language in User-Level should be defined by themselves.
> (Otherwise, there will not be so many SQL language around MS
> applications :> )
> Could you introduce me any tutorial and handbook web site for it??
>
 |
28-Jul-2006, 08:42 AM
| | Guest | | | | | | | | | | Re: Interesting SQL commands
For all Tables
Create a new Query ( MSysObjects is hidden - leave it so - can be changed
through tools/options/show hidden objects)
Hit cancel for the table selection
View SQL
Write SELECT Name From MSYSOBJECTS A WHERE A.TYPE IN (1,6,4)
This will give you all the tables (native, Linked, ODBC)
For Queries (Views)
SELECT A.NameFROM MSYSOBJECTS A WHERE EXISTS ( SELECT 'X' FROM MSysQueries
B WHERE B.ObjectId =A.Id)
To get all the users you'll need to use VB Code:
Sub CreateUserTable()
Dim Ws As DAO.WorkSpace
Dim Db As DAO.Database
Dim Usr As DAO.User
Set Ws = DAO.DbEngine.Workspaces(0)
Set Db = CurrentDb
Db.Execute "DROP Table USysUsers" Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14007
Db.Execute "CREATE TABLE USysUsers (UserName char(32))"
Db.Execute "CREATE UNIQUE INDEX PK_USysTable ON USysUsers (UserName) With
Primary"
For Each Usr In Ws.Users
Db.Execute "INSERT INTO USysusers (UserName) VALUES ('" & Usr.Name & "')"
Next ' Usr
Set Db = Nothing
Set Ws = Nothing
End Sub
now you can use (to show users - including "internal users" - Engine,
Creator)
SELECT UserName FROM USysUsers
For MSSQL, Oracle(8i +) you can query:
INFORMATION_SCHEMA.TABLES etc
Pieter
"Cicada" wrote in message
news:uEl%23O5PsGHA.4444@TK2MSFTNGP06.phx.gbl...
> Today I see a PostgreSQL SQL book and find some interesting commands
> below.
> Is there any similar SQL commands under ADO??
>
> 1. Meta-Command
> \d_\dt List All TAbles
> \dv List All View
> ...... List All Query (equal to View??)
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14007
> ...... List All Users
>
> 2. Frequency Statistic
> Select n_distinct, most_common_vals, most_common_freqs FROM .....
> ==> these 3 terms are system terms. Result is showing N-th data/value
> occuring with top frequency in a table's field.
>
> Besides, please help:
> 3. There is a System table in MS Access "MsysObjects".
> I can open it in MS Access easily, without any password needed, but not
> in my application.
> How to solve it?
>
> 4. I have searched through Google with keywords SQL ADO or SQL "OLE DB", I
> feel very strange that there is only a few data for the former, and None
> for the latter.
> Even though their ultimate data engine may be ODBC or Jet, their SQL
> language in User-Level should be defined by themselves.
> (Otherwise, there will not be so many SQL language around MS
> applications :> )
> Could you introduce me any tutorial and handbook web site for it??
>
--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4367 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len
|
28-Jul-2006, 08:42 AM
| | Guest | | | | | | | | | | Re: Interesting SQL commands
Use the syscmd to find the workgroup database, then
open the user list table in the workgroup database
(david)
"Pieter Wijnen"
wrote in message news:u6itDMQsGHA.904@TK2MSFTNGP03.phx.gbl...
> For all Tables
> Create a new Query ( MSysObjects is hidden - leave it so - can be changed
> through tools/options/show hidden objects)
> Hit cancel for the table selection
> View SQL
> Write SELECT Name From MSYSOBJECTS A WHERE A.TYPE IN (1,6,4)
> This will give you all the tables (native, Linked, ODBC)
>
> For Queries (Views)
> SELECT A.NameFROM MSYSOBJECTS A WHERE EXISTS ( SELECT 'X' FROM
> MSysQueries B WHERE B.ObjectId =A.Id)
>
> To get all the users you'll need to use VB Code:
>
> Sub CreateUserTable()
> Dim Ws As DAO.WorkSpace
> Dim Db As DAO.Database
> Dim Usr As DAO.User
>
> Set Ws = DAO.DbEngine.Workspaces(0)
> Set Db = CurrentDb
> Db.Execute "DROP Table USysUsers"
> Db.Execute "CREATE TABLE USysUsers (UserName char(32))"
> Db.Execute "CREATE UNIQUE INDEX PK_USysTable ON USysUsers (UserName) With
> Primary"
>
> For Each Usr In Ws.Users
> Db.Execute "INSERT INTO USysusers (UserName) VALUES ('" & Usr.Name & "')"
> Next ' Usr
>
> Set Db = Nothing
> Set Ws = Nothing
> End Sub
>
> now you can use (to show users - including "internal users" - Engine,
> Creator)
> SELECT UserName FROM USysUsers
>
> For MSSQL, Oracle(8i +) you can query:
> INFORMATION_SCHEMA.TABLES etc
>
> Pieter
>
>
> "Cicada" wrote in message
> news:uEl%23O5PsGHA.4444@TK2MSFTNGP06.phx.gbl...
>> Today I see a PostgreSQL SQL book and find some interesting commands
>> below.
>> Is there any similar SQL commands under ADO??
>>
>> 1. Meta-Command
>> \d_\dt List All TAbles
>> \dv List All View
>> ...... List All Query (equal to View??)
>> ...... List All Users
>>
>> 2. Frequency Statistic
>> Select n_distinct, most_common_vals, most_common_freqs FROM .....
>> ==> these 3 terms are system terms. Result is showing N-th data/value
>> occuring with top frequency in a table's field.
>>
>> Besides, please help:
>> 3. There is a System table in MS Access "MsysObjects".
>> I can open it in MS Access easily, without any password needed, but
>> not in my application.
>> How to solve it?
>>
>> 4. I have searched through Google with keywords SQL ADO or SQL "OLE DB",
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14007
>> I feel very strange that there is only a few data for the former, and
>> None for the latter.
>> Even though their ultimate data engine may be ODBC or Jet, their SQL
>> language in User-Level should be defined by themselves.
>> (Otherwise, there will not be so many SQL language around MS
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14007
>> applications :> )
>> Could you introduce me any tutorial and handbook web site for it??
>>
>
>
|
28-Jul-2006, 08:42 AM
| | Guest | | | | | | | | | | Re: Interesting SQL commands
On Thu, 27 Jul 2006 05  07 +0800, "Cicada"
wrote:
>Today I see a PostgreSQL SQL book and find some interesting commands below.
>Is there any similar SQL commands under ADO??
Not really... but Tools... Analyze... Documentor will show you all
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14007
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14007
this information and more (too much information actually, you should
check the options before printing!)
John W. Vinson[MVP]
|
28-Jul-2006, 08:42 AM
| | Guest | | | | | | | | | | Re: Interesting SQL commands
As I state in Point 3, I can view (not hidden) MsysObjects table in both
Access & my application (ADO-based),
but I can just OPEN it in Access, but not in my application, and error
message is "No authorization ro read". (but I have not set any
authorization level myself)
(This application is not not programmed by me, but a standard database
application very similar to Access).
How to solve it 1st??
"Pieter Wijnen"
¼¶¼g©ó¶l¥ó·s»D:u6itDMQsGHA.904@TK2MSFTNGP03.phx.gb l...
> For all Tables
> Create a new Query ( MSysObjects is hidden - leave it so - can be changed
> through tools/options/show hidden objects)
> Hit cancel for the table selection
> View SQL
> Write SELECT Name From MSYSOBJECTS A WHERE A.TYPE IN (1,6,4)
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14007
> This will give you all the tables (native, Linked, ODBC)
>
> For Queries (Views)
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14007
> SELECT A.NameFROM MSYSOBJECTS A WHERE EXISTS ( SELECT 'X' FROM
> MSysQueries B WHERE B.ObjectId =A.Id)
>
> To get all the users you'll need to use VB Code:
>
> Sub CreateUserTable()
> Dim Ws As DAO.WorkSpace
> Dim Db As DAO.Database
> Dim Usr As DAO.User
>
> Set Ws = DAO.DbEngine.Workspaces(0)
> Set Db = CurrentDb
> Db.Execute "DROP Table USysUsers"
> Db.Execute "CREATE TABLE USysUsers (UserName char(32))"
> Db.Execute "CREATE UNIQUE INDEX PK_USysTable ON USysUsers (UserName) With
> Primary"
>
> For Each Usr In Ws.Users
> Db.Execute "INSERT INTO USysusers (UserName) VALUES ('" & Usr.Name & "')"
> Next ' Usr
>
> Set Db = Nothing
> Set Ws = Nothing
> End Sub
>
> now you can use (to show users - including "internal users" - Engine,
> Creator)
> SELECT UserName FROM USysUsers
>
> For MSSQL, Oracle(8i +) you can query:
> INFORMATION_SCHEMA.TABLES etc
>
> Pieter
>
>
> "Cicada" wrote in message
> news:uEl%23O5PsGHA.4444@TK2MSFTNGP06.phx.gbl...
>> Today I see a PostgreSQL SQL book and find some interesting commands
>> below.
>> Is there any similar SQL commands under ADO??
>>
>> 1. Meta-Command
>> \d_\dt List All TAbles
>> \dv List All View
>> ...... List All Query (equal to View??)
>> ...... List All Users
>>
>> 2. Frequency Statistic
>> Select n_distinct, most_common_vals, most_common_freqs FROM .....
>> ==> these 3 terms are system terms. Result is showing N-th data/value
>> occuring with top frequency in a table's field.
>>
>> Besides, please help:
>> 3. There is a System table in MS Access "MsysObjects".
>> I can open it in MS Access easily, without any password needed, but
>> not in my application.
>> How to solve it?
>>
>> 4. I have searched through Google with keywords SQL ADO or SQL "OLE DB",
>> I feel very strange that there is only a few data for the former, and
>> None for the latter.
>> Even though their ultimate data engine may be ODBC or Jet, their SQL
>> language in User-Level should be defined by themselves.
>> (Otherwise, there will not be so many SQL language around MS
>> applications :> )
>> Could you introduce me any tutorial and handbook web site for it??
>>
>
>
|
28-Jul-2006, 08:42 AM
| | Guest | | | | | | | | | | Re: Interesting SQL commands
Oh yeah I forgot about that, silly me
you can query MSysAccounts in the system.mdw (workGroup file), to find the
users (do it myself - I must admit)
For Users:
SELECT A.NAME FROM MSYSACCOUNTS A WHERE FGROUP = 0
For Groups (Roles)
SELECT A.NAME FROM MSYSACCOUNTS A WHERE FGROUP <>0
For User/Roles (Membership):
SELECT G.Name AS GroupName, U.Name AS UserName
FROM (MSysAccounts U INNER JOIN MSysGroups UG ON U.SID = UG.UserSID) INNER Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14007
JOIN MSysAccounts G ON UG.GroupSID = G.SID
WHERE U.FGroup=0 AND G.FGroup<>0
Pieter
"Cicada" wrote in message
news:%23eo0JjUsGHA.4784@TK2MSFTNGP04.phx.gbl...
> As I state in Point 3, I can view (not hidden) MsysObjects table in both
> Access & my application (ADO-based),
> but I can just OPEN it in Access, but not in my application, and error
> message is "No authorization ro read". (but I have not set any
> authorization level myself)
> (This application is not not programmed by me, but a standard database
> application very similar to Access).
>
> How to solve it 1st??
>
>
> "Pieter Wijnen"
>
> ¼¶¼g©ó¶l¥ó·s»D:u6itDMQsGHA.904@TK2MSFTNGP03.phx.gb l...
>> For all Tables
>> Create a new Query ( MSysObjects is hidden - leave it so - can be
>> changed through tools/options/show hidden objects)
>> Hit cancel for the table selection
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14007
>> View SQL
>> Write SELECT Name From MSYSOBJECTS A WHERE A.TYPE IN (1,6,4)
>> This will give you all the tables (native, Linked, ODBC)
>>
>> For Queries (Views)
>> SELECT A.NameFROM MSYSOBJECTS A WHERE EXISTS ( SELECT 'X' FROM
>> MSysQueries B WHERE B.ObjectId =A.Id)
>>
>> To get all the users you'll need to use VB Code:
>>
>> Sub CreateUserTable()
>> Dim Ws As DAO.WorkSpace
>> Dim Db As DAO.Database
>> Dim Usr As DAO.User
>>
>> Set Ws = DAO.DbEngine.Workspaces(0)
>> Set Db = CurrentDb
>> Db.Execute "DROP Table USysUsers"
>> Db.Execute "CREATE TABLE USysUsers (UserName char(32))"
>> Db.Execute "CREATE UNIQUE INDEX PK_USysTable ON USysUsers (UserName) With
>> Primary"
>>
>> For Each Usr In Ws.Users
>> Db.Execute "INSERT INTO USysusers (UserName) VALUES ('" & Usr.Name &
>> "')"
>> Next ' Usr
>>
>> Set Db = Nothing
>> Set Ws = Nothing
>> End Sub
>>
>> now you can use (to show users - including "internal users" - Engine,
>> Creator)
>> SELECT UserName FROM USysUsers
>>
>> For MSSQL, Oracle(8i +) you can query:
>> INFORMATION_SCHEMA.TABLES etc
>>
>> Pieter
>>
>>
>> "Cicada" wrote in message
>> news:uEl%23O5PsGHA.4444@TK2MSFTNGP06.phx.gbl...
>>> Today I see a PostgreSQL SQL book and find some interesting commands
>>> below.
>>> Is there any similar SQL commands under ADO??
>>>
>>> 1. Meta-Command
>>> \d_\dt List All TAbles
>>> \dv List All View
>>> ...... List All Query (equal to View??)
>>> ...... List All Users
>>>
>>> 2. Frequency Statistic
>>> Select n_distinct, most_common_vals, most_common_freqs FROM .....
>>> ==> these 3 terms are system terms. Result is showing N-th data/value
>>> occuring with top frequency in a table's field.
>>>
>>> Besides, please help:
>>> 3. There is a System table in MS Access "MsysObjects".
>>> I can open it in MS Access easily, without any password needed, but
>>> not in my application.
>>> How to solve it?
>>>
>>> 4. I have searched through Google with keywords SQL ADO or SQL "OLE DB",
>>> I feel very strange that there is only a few data for the former, and
>>> None for the latter.
>>> Even though their ultimate data engine may be ODBC or Jet, their SQL
>>> language in User-Level should be defined by themselves.
>>> (Otherwise, there will not be so many SQL language around MS
>>> applications :> )
>>> Could you introduce me any tutorial and handbook web site for it??
>>>
>>
>>
>
>
|
28-Jul-2006, 08:42 AM
| | Guest | | | | | | | | | | Re: Interesting SQL commands
Oh yeah I forgot about that, silly me
you can query MSysAccounts in the system.mdw (workGroup file), to find the
users (do it myself - I must admit)
For Users:
SELECT A.NAME FROM MSYSACCOUNTS A WHERE FGROUP = 0
For Groups (Roles)
SELECT A.NAME FROM MSYSACCOUNTS A WHERE FGROUP <>0
For User/Roles (Membership):
SELECT G.Name AS GroupName, U.Name AS UserName
FROM (MSysAccounts U INNER JOIN MSysGroups UG ON U.SID = UG.UserSID) INNER
JOIN MSysAccounts G ON UG.GroupSID = G.SID
WHERE U.FGroup=0 AND G.FGroup<>0
Pieter
"Cicada" wrote in message
news:%23eo0JjUsGHA.4784@TK2MSFTNGP04.phx.gbl...
> As I state in Point 3, I can view (not hidden) MsysObjects table in both
> Access & my application (ADO-based),
> but I can just OPEN it in Access, but not in my application, and error
> message is "No authorization ro read". (but I have not set any
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14007
> authorization level myself)
> (This application is not not programmed by me, but a standard database
> application very similar to Access).
>
> How to solve it 1st??
>
>
> "Pieter Wijnen"
>
> ¼¶¼g©ó¶l¥ó·s»D:u6itDMQsGHA.904@TK2MSFTNGP03.phx.gb l...
>> For all Tables
>> Create a new Query ( MSysObjects is hidden - leave it so - can be
>> changed through tools/options/show hidden objects)
>> Hit cancel for the table selection
>> View SQL
>> Write SELECT Name From MSYSOBJECTS A WHERE A.TYPE IN (1,6,4)
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14007
>> This will give you all the tables (native, Linked, ODBC)
>>
>> For Queries (Views)
>> SELECT A.NameFROM MSYSOBJECTS A WHERE EXISTS ( SELECT 'X' FROM
>> MSysQueries B WHERE B.ObjectId =A.Id)
>>
>> To get all the users you'll need to use VB Code:
>>
>> Sub CreateUserTable()
>> Dim Ws As DAO.WorkSpace
>> Dim Db As DAO.Database
>> Dim Usr As DAO.User
>>
>> Set Ws = DAO.DbEngine.Workspaces(0)
>> Set Db = CurrentDb
>> Db.Execute "DROP Table USysUsers"
>> Db.Execute "CREATE TABLE USysUsers (UserName char(32))"
>> Db.Execute "CREATE UNIQUE INDEX PK_USysTable ON USysUsers (UserName) With
>> Primary"
>>
>> For Each Usr In Ws.Users
>> Db.Execute "INSERT INTO USysusers (UserName) VALUES ('" & Usr.Name &
>> "')"
>> Next ' Usr
>>
>> Set Db = Nothing
>> Set Ws = Nothing
>> End Sub
>>
>> now you can use (to show users - including "internal users" - Engine,
>> Creator)
>> SELECT UserName FROM USysUsers
>>
>> For MSSQL, Oracle(8i +) you can query:
>> INFORMATION_SCHEMA.TABLES etc
>>
>> Pieter
>>
>>
>> "Cicada" wrote in message
>> news:uEl%23O5PsGHA.4444@TK2MSFTNGP06.phx.gbl...
>>> Today I see a PostgreSQL SQL book and find some interesting commands
>>> below.
>>> Is there any similar SQL commands under ADO??
>>>
>>> 1. Meta-Command
>>> \d_\dt List All TAbles
>>> \dv List All View
>>> ...... List All Query (equal to View??)
>>> ...... List All Users
>>>
>>> 2. Frequency Statistic
>>> Select n_distinct, most_common_vals, most_common_freqs FROM .....
>>> ==> these 3 terms are system terms. Result is showing N-th data/value
>>> occuring with top frequency in a table's field.
>>>
>>> Besides, please help:
>>> 3. There is a System table in MS Access "MsysObjects".
>>> I can open it in MS Access easily, without any password needed, but
>>> not in my application.
>>> How to solve it?
>>>
>>> 4. I have searched through Google with keywords SQL ADO or SQL "OLE DB",
>>> I feel very strange that there is only a few data for the former, and
>>> None for the latter.
>>> Even though their ultimate data engine may be ODBC or Jet, their SQL
>>> language in User-Level should be defined by themselves.
>>> (Otherwise, there will not be so many SQL language around MS
>>> applications :> )
>>> Could you introduce me any tutorial and handbook web site for it??
>>>
>>
>>
>
>
--
----------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4367 spam emails to date.
Paying users do not have this message in their emails.
Get the free SPAMfighter here: http://www.spamfighter.com/len
|
28-Jul-2006, 08:43 AM
| | Guest | | | | | | | | | | Re: Interesting SQL commands
Thanks very much for all of your great help!
I have learned a great lesson. 
"Pieter Wijnen"
¼¶¼g©ó¶l¥ó·s»D:eGKKiyVsGHA.356@TK2MSFTNGP05.phx.gb l...
> Oh yeah I forgot about that, silly me
> you can query MSysAccounts in the system.mdw (workGroup file), to find the
> users (do it myself - I must admit)
>
> For Users:
> SELECT A.NAME FROM MSYSACCOUNTS A WHERE FGROUP = 0
>
> For Groups (Roles)
> SELECT A.NAME FROM MSYSACCOUNTS A WHERE FGROUP <>0
>
> For User/Roles (Membership):
> SELECT G.Name AS GroupName, U.Name AS UserName
> FROM (MSysAccounts U INNER JOIN MSysGroups UG ON U.SID = UG.UserSID) INNER
> JOIN MSysAccounts G ON UG.GroupSID = G.SID
> WHERE U.FGroup=0 AND G.FGroup<>0
>
> Pieter
>
> "Cicada" wrote in message
> news:%23eo0JjUsGHA.4784@TK2MSFTNGP04.phx.gbl...
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14007
>> As I state in Point 3, I can view (not hidden) MsysObjects table in both
>> Access & my application (ADO-based),
>> but I can just OPEN it in Access, but not in my application, and error
>> message is "No authorization ro read". (but I have not set any
>> authorization level myself)
>> (This application is not not programmed by me, but a standard database
>> application very similar to Access).
>>
>> How to solve it 1st??
>>
>>
>> "Pieter Wijnen"
>>
>> ¼¶¼g©ó¶l¥ó·s»D:u6itDMQsGHA.904@TK2MSFTNGP03.phx.gb l...
>>> For all Tables
>>> Create a new Query ( MSysObjects is hidden - leave it so - can be
>>> changed through tools/options/show hidden objects)
>>> Hit cancel for the table selection
>>> View SQL
>>> Write SELECT Name From MSYSOBJECTS A WHERE A.TYPE IN (1,6,4)
>>> This will give you all the tables (native, Linked, ODBC)
>>>
>>> For Queries (Views)
>>> SELECT A.NameFROM MSYSOBJECTS A WHERE EXISTS ( SELECT 'X' FROM
>>> MSysQueries B WHERE B.ObjectId =A.Id)
>>>
>>> To get all the users you'll need to use VB Code:
>>>
>>> Sub CreateUserTable()
>>> Dim Ws As DAO.WorkSpace
>>> Dim Db As DAO.Database
>>> Dim Usr As DAO.User
>>>
>>> Set Ws = DAO.DbEngine.Workspaces(0)
>>> Set Db = CurrentDb
>>> Db.Execute "DROP Table USysUsers"
>>> Db.Execute "CREATE TABLE USysUsers (UserName char(32))"
>>> Db.Execute "CREATE UNIQUE INDEX PK_USysTable ON USysUsers (UserName)
>>> With Primary"
>>>
>>> For Each Usr In Ws.Users
>>> Db.Execute "INSERT INTO USysusers (UserName) VALUES ('" & Usr.Name &
>>> "')"
>>> Next ' Usr
>>>
>>> Set Db = Nothing
>>> Set Ws = Nothing
>>> End Sub
>>>
>>> now you can use (to show users - including "internal users" - Engine,
>>> Creator)
>>> SELECT UserName FROM USysUsers
>>>
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=14007
>>> For MSSQL, Oracle(8i +) you can query:
>>> INFORMATION_SCHEMA.TABLES etc
>>>
>>> Pieter
>>>
>>>
>>> "Cicada" wrote in message
>>> news:uEl%23O5PsGHA.4444@TK2MSFTNGP06.phx.gbl...
>>>> Today I see a PostgreSQL SQL book and find some interesting commands
>>>> below.
>>>> Is there any similar SQL commands under ADO??
>>>>
>>>> 1. Meta-Command
>>>> \d_\dt List All TAbles
>>>> \dv List All View
>>>> ...... List All Query (equal to View??)
>>>> ...... List All Users
>>>>
>>>> 2. Frequency Statistic
>>>> Select n_distinct, most_common_vals, most_common_freqs FROM .....
>>>> ==> these 3 terms are system terms. Result is showing N-th
>>>> data/value occuring with top frequency in a table's field.
>>>>
>>>> Besides, please help:
>>>> 3. There is a System table in MS Access "MsysObjects".
>>>> I can open it in MS Access easily, without any password needed, but
>>>> not in my application.
>>>> How to solve it?
>>>>
>>>> 4. I have searched through Google with keywords SQL ADO or SQL "OLE
>>>> DB", I feel very strange that there is only a few data for the former,
>>>> and None for the latter.
>>>> Even though their ultimate data engine may be ODBC or Jet, their SQL
>>>> language in User-Level should be defined by themselves.
>>>> (Otherwise, there will not be so many SQL language around MS
>>>> applications :> )
>>>> Could you introduce me any tutorial and handbook web site for it??
>>>>
>>>
>>>
>>
>>
>
>
| 
Support Us! Become a Promoter! | | Gurfateh ji, you can become a SPN Promoter by Donating as little as $10 each month. With limited resources & high operational costs, your donations make it possible for us to deliver a quality website and spread the teachings of the Sri Guru Granth Sahib Ji, to serve & uplift humanity. Every contribution counts. Donate Generously. Gurfateh! |
 (View-All)
Members who have read this thread : 0
| | There are no names to display. |
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | | | | Tools | Search | | | | | Display Modes |  Linear Mode |
Posting Rules
| You may not post new threads You may not post replies You may not post attachments You may not edit your posts
HTML code is On
| | | |  | » Gurbani Jukebox |  Listen to Gurbani while surfing SPN! | | » Active Discussions | | | | | | |  ਨਾਮਾ Today 06:37 AM 2 Replies, 53 Views | | | | | | | | | | | | | | | | | | | | | | | | » Books You Should Read... | | | |
