bhatia_amrita
asked on
MSAccess Views defintion Text
hi
Is there a way we can get the text definition of a query in MsAccess , so as in nWind database in Ms Access , there is a invoices query , is there a way i can get the text of this query programatically , i understand its saved in mSysQueries table . but here also its not saved as a whole but as a rows with some obscure attributes number. basically we need something like sp_helptext in sql server.
is the above possible with SQLDMO or oledbGetSchema or making the query ourself or any other way. we would be using vb.net for it so if anybody has a idea about implementing it from .Net that will be great.
pls suggest
Amrita
Is there a way we can get the text definition of a query in MsAccess , so as in nWind database in Ms Access , there is a invoices query , is there a way i can get the text of this query programatically , i understand its saved in mSysQueries table . but here also its not saved as a whole but as a rows with some obscure attributes number. basically we need something like sp_helptext in sql server.
is the above possible with SQLDMO or oledbGetSchema or making the query ourself or any other way. we would be using vb.net for it so if anybody has a idea about implementing it from .Net that will be great.
pls suggest
Amrita
dim db as DAO.database
dim qdf as DAO.Querydef
Set db = currentdb
Set qdf = db.QueryDefs("NameOfQuery"
Debug.Print qdf.SQL
dim qdf as DAO.Querydef
Set db = currentdb
Set qdf = db.QueryDefs("NameOfQuery"
Debug.Print qdf.SQL
If I am understanding your question correctly: you want to see the SQL statement for a query. Open it in design view then click on SQL view.
ASKER
hi
thanks for all the help , lemme rephrase my problem again
i have a Vb.net application that i want to query access for the data of query .
we have tried following thing
SQLDMO but i guess its not compatible with MSAccess
oledb.GetOleDbSchemaTable -- does not work with ODBC connection
conn.OpenSchema in ADODb -- does not work with ODBC connection
so is there a way that work with both oledb and odbc connection
or if above is not possible any custom query that gets the definition of the query
thanks
Amrita
thanks for all the help , lemme rephrase my problem again
i have a Vb.net application that i want to query access for the data of query .
we have tried following thing
SQLDMO but i guess its not compatible with MSAccess
oledb.GetOleDbSchemaTable -- does not work with ODBC connection
conn.OpenSchema in ADODb -- does not work with ODBC connection
so is there a way that work with both oledb and odbc connection
or if above is not possible any custom query that gets the definition of the query
thanks
Amrita
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I think it would be possible to write a function that rebuilds the query SQL from the (very confusing) system tables, it would only require understanding the "Attributes". The following query will return all the needed information for the User Defined queries in the database:
SELECT MSysObjects.Name, MSysQueries.Attribute, MSysQueries.Name1, MSysQueries.Name2, MSysQueries.Expression, MSysQueries.Flag
FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id = MSysQueries.ObjectId
WHERE (((MSysObjects.Name) Not Like "~*") AND ((MSysObjects.Type)=5));
I don't have time to work on a compiling function at the moment, but I think I will take a stab at it tomorrow, just for fun. If anone knows of a list of the attributes stored in the "MSysQueries" table give a shout as it will save me having to figure it all out from scratch...
SELECT MSysObjects.Name, MSysQueries.Attribute, MSysQueries.Name1, MSysQueries.Name2, MSysQueries.Expression, MSysQueries.Flag
FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id = MSysQueries.ObjectId
WHERE (((MSysObjects.Name) Not Like "~*") AND ((MSysObjects.Type)=5));
I don't have time to work on a compiling function at the moment, but I think I will take a stab at it tomorrow, just for fun. If anone knows of a list of the attributes stored in the "MSysQueries" table give a shout as it will save me having to figure it all out from scratch...
Amrita,
Would Craig Yellick's suggestion of importing the DAO component into your project work for you? I have started looking into rebuilding the SQL string, and while it is doable, it will take some time. If you need it, I don''t mind the time (as long as you can wait a few days)...
Sam
Would Craig Yellick's suggestion of importing the DAO component into your project work for you? I have started looking into rebuilding the SQL string, and while it is doable, it will take some time. If you need it, I don''t mind the time (as long as you can wait a few days)...
Sam
ASKER
hi
thanks for the help
i worked with the Dao thing , but it asks for the actual file name and path
, in case we have only connectionstring, it wudnt work , also any idea if it works with Sql Server.
i am jus looking for something at the common level that works with Oledb,odbc for Access and Sql , is it possible with DAO ???
And regarding the value of attributes i tried and found the following
--------- start of table, first line has column names
Attribute Frequency Sample Value Flag Name1 Name2 Order
0 Once
1 Once 1
2 Each formal parameter Parameter name, e.g. "Database Type" Datatype, e.g.
"10" for dbText
3 Once 8 8 for DistinctRow, 0 for unspecified, 2 for Distinct, 1 for all
fields of all tables
4 Once or None Source if entire query is from an external database
5 Each table Connect Source or Null if Native Table name, e.g.
"ActivityType" Alias for the table or Null if no alias
6 Each single select field or expression, or None if all fields Each field
select expression, e.g. " ActivityType.[Activity Type]" or "Problem.*" (for
all fields of all tables, there is no attribute 6 occurrence) 0 Field Alias
such as "ActivityType", or Null if no alias
7 Each join Single Join On expression, e.g. "ActivityType.Activity =
Activity.Activity" 1 for Inner Join, 2 for Left Join First table in join,
e.g. "ActivityType" Second table in join, e.g. "Activity"
8 Once for entire Where clause Where clause, e.g. "(((Activity.[Is
Equipment])=True) AND ((Equipment.[Is Sensor])=True))"
9 Each Group By expression Single Group By expression, e.g.
"ActivityType.[Activity Type]" 0
11 Each Order By expression Single Order By expression, e.g.
"ActivityType.[Model Order]"
255?
--------- end of table
at the URL
http://groups.google.com/groups?hl=pa&lr=&threadm=b1ovbc%2414ehpf%241%40ID-65843.news.dfncis.de&rnum=1&prev=/groups%3Fq%3Dmsysqueries%2B%2Battributes%26hl%3Dpa%26lr%3D%26selm%3Db1ovbc%252414ehpf%25241%2540ID-65843.news.dfncis.de%26rnum%3D1
not sure if the above one is correct but are these values static or change with newer versions of Access??
Thanks
Amrita
thanks for the help
i worked with the Dao thing , but it asks for the actual file name and path
, in case we have only connectionstring, it wudnt work , also any idea if it works with Sql Server.
i am jus looking for something at the common level that works with Oledb,odbc for Access and Sql , is it possible with DAO ???
And regarding the value of attributes i tried and found the following
--------- start of table, first line has column names
Attribute Frequency Sample Value Flag Name1 Name2 Order
0 Once
1 Once 1
2 Each formal parameter Parameter name, e.g. "Database Type" Datatype, e.g.
"10" for dbText
3 Once 8 8 for DistinctRow, 0 for unspecified, 2 for Distinct, 1 for all
fields of all tables
4 Once or None Source if entire query is from an external database
5 Each table Connect Source or Null if Native Table name, e.g.
"ActivityType" Alias for the table or Null if no alias
6 Each single select field or expression, or None if all fields Each field
select expression, e.g. " ActivityType.[Activity Type]" or "Problem.*" (for
all fields of all tables, there is no attribute 6 occurrence) 0 Field Alias
such as "ActivityType", or Null if no alias
7 Each join Single Join On expression, e.g. "ActivityType.Activity =
Activity.Activity" 1 for Inner Join, 2 for Left Join First table in join,
e.g. "ActivityType" Second table in join, e.g. "Activity"
8 Once for entire Where clause Where clause, e.g. "(((Activity.[Is
Equipment])=True) AND ((Equipment.[Is Sensor])=True))"
9 Each Group By expression Single Group By expression, e.g.
"ActivityType.[Activity Type]" 0
11 Each Order By expression Single Order By expression, e.g.
"ActivityType.[Model Order]"
255?
--------- end of table
at the URL
http://groups.google.com/groups?hl=pa&lr=&threadm=b1ovbc%2414ehpf%241%40ID-65843.news.dfncis.de&rnum=1&prev=/groups%3Fq%3Dmsysqueries%2B%2Battributes%26hl%3Dpa%26lr%3D%26selm%3Db1ovbc%252414ehpf%25241%2540ID-65843.news.dfncis.de%26rnum%3D1
not sure if the above one is correct but are these values static or change with newer versions of Access??
Thanks
Amrita
ASKER
hi
thanks for the help
i worked with the Dao thing , but it asks for the actual file name and path
, in case we have only connectionstring, it wudnt work , also any idea if it works with Sql Server.
i am jus looking for something at the common level that works with Oledb,odbc for Access and Sql , is it possible with DAO ???
And regarding the value of attributes i tried and found the following
--------- start of table, first line has column names
Attribute Frequency Sample Value Flag Name1 Name2 Order
0 Once
1 Once 1
2 Each formal parameter Parameter name, e.g. "Database Type" Datatype, e.g.
"10" for dbText
3 Once 8 8 for DistinctRow, 0 for unspecified, 2 for Distinct, 1 for all
fields of all tables
4 Once or None Source if entire query is from an external database
5 Each table Connect Source or Null if Native Table name, e.g.
"ActivityType" Alias for the table or Null if no alias
6 Each single select field or expression, or None if all fields Each field
select expression, e.g. " ActivityType.[Activity Type]" or "Problem.*" (for
all fields of all tables, there is no attribute 6 occurrence) 0 Field Alias
such as "ActivityType", or Null if no alias
7 Each join Single Join On expression, e.g. "ActivityType.Activity =
Activity.Activity" 1 for Inner Join, 2 for Left Join First table in join,
e.g. "ActivityType" Second table in join, e.g. "Activity"
8 Once for entire Where clause Where clause, e.g. "(((Activity.[Is
Equipment])=True) AND ((Equipment.[Is Sensor])=True))"
9 Each Group By expression Single Group By expression, e.g.
"ActivityType.[Activity Type]" 0
11 Each Order By expression Single Order By expression, e.g.
"ActivityType.[Model Order]"
255?
--------- end of table
at the URL
http://groups.google.com/groups?hl=pa&lr=&threadm=b1ovbc%2414ehpf%241%40ID-65843.news.dfncis.de&rnum=1&prev=/groups%3Fq%3Dmsysqueries%2B%2Battributes%26hl%3Dpa%26lr%3D%26selm%3Db1ovbc%252414ehpf%25241%2540ID-65843.news.dfncis.de%26rnum%3D1
not sure if the above one is correct but are these values static or change with newer versions of Access??
Thanks
Amrita
thanks for the help
i worked with the Dao thing , but it asks for the actual file name and path
, in case we have only connectionstring, it wudnt work , also any idea if it works with Sql Server.
i am jus looking for something at the common level that works with Oledb,odbc for Access and Sql , is it possible with DAO ???
And regarding the value of attributes i tried and found the following
--------- start of table, first line has column names
Attribute Frequency Sample Value Flag Name1 Name2 Order
0 Once
1 Once 1
2 Each formal parameter Parameter name, e.g. "Database Type" Datatype, e.g.
"10" for dbText
3 Once 8 8 for DistinctRow, 0 for unspecified, 2 for Distinct, 1 for all
fields of all tables
4 Once or None Source if entire query is from an external database
5 Each table Connect Source or Null if Native Table name, e.g.
"ActivityType" Alias for the table or Null if no alias
6 Each single select field or expression, or None if all fields Each field
select expression, e.g. " ActivityType.[Activity Type]" or "Problem.*" (for
all fields of all tables, there is no attribute 6 occurrence) 0 Field Alias
such as "ActivityType", or Null if no alias
7 Each join Single Join On expression, e.g. "ActivityType.Activity =
Activity.Activity" 1 for Inner Join, 2 for Left Join First table in join,
e.g. "ActivityType" Second table in join, e.g. "Activity"
8 Once for entire Where clause Where clause, e.g. "(((Activity.[Is
Equipment])=True) AND ((Equipment.[Is Sensor])=True))"
9 Each Group By expression Single Group By expression, e.g.
"ActivityType.[Activity Type]" 0
11 Each Order By expression Single Order By expression, e.g.
"ActivityType.[Model Order]"
255?
--------- end of table
at the URL
http://groups.google.com/groups?hl=pa&lr=&threadm=b1ovbc%2414ehpf%241%40ID-65843.news.dfncis.de&rnum=1&prev=/groups%3Fq%3Dmsysqueries%2B%2Battributes%26hl%3Dpa%26lr%3D%26selm%3Db1ovbc%252414ehpf%25241%2540ID-65843.news.dfncis.de%26rnum%3D1
not sure if the above one is correct but are these values static or change with newer versions of Access??
Thanks
Amrita
Amrita,
>>, in case we have only connectionstring, it wudnt work , also any idea if it works with Sql Server.
>>i am jus looking for something at the common level that works with Oledb,odbc for Access and Sql , is it possible with DAO ???
DAO will not work with SQL databases (as far as I know), at least not to show you the QueryDef. If we were to write a custom function to read the SQL string out of the "MSysQueries" table, it also would only work for Access queries, as it would be specificaly written to de-compile the data in that table.
>> not sure if the above one is correct but are these values static or change with newer versions of Access??
From looking at, it so far it looks correct...
>>, in case we have only connectionstring, it wudnt work , also any idea if it works with Sql Server.
>>i am jus looking for something at the common level that works with Oledb,odbc for Access and Sql , is it possible with DAO ???
DAO will not work with SQL databases (as far as I know), at least not to show you the QueryDef. If we were to write a custom function to read the SQL string out of the "MSysQueries" table, it also would only work for Access queries, as it would be specificaly written to de-compile the data in that table.
>> not sure if the above one is correct but are these values static or change with newer versions of Access??
From looking at, it so far it looks correct...
ASKER
hi
thnx will_scarlet7 for the help
at the end of it , i am still a little confused ,
i am thinking of Dao -- will it work with ODBC and if i dont have the path of mdb file???
oleDBGetSchemaDefintion -- does not work with ODBC
are there any other options ?? i am jus a .Net Sql server person , so newbie with msAccess or vb com objects
thnx will_scarlet7 for the help
at the end of it , i am still a little confused ,
i am thinking of Dao -- will it work with ODBC and if i dont have the path of mdb file???
oleDBGetSchemaDefintion -- does not work with ODBC
are there any other options ?? i am jus a .Net Sql server person , so newbie with msAccess or vb com objects
Amrita:
Could you give an example of your connection string? DAO would also require you to use a connection string to se the "Database" object, after which you can extract the "QueryDef.SQL"
Craig Yellick:
Would you have more to offer on how to connect to the database through DAO in .net?
Sam
Could you give an example of your connection string? DAO would also require you to use a connection string to se the "Database" object, after which you can extract the "QueryDef.SQL"
Craig Yellick:
Would you have more to offer on how to connect to the database through DAO in .net?
Sam
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
connectionstring could be either a oledb or ODBC in the format as follows
"DBQ=C:\Documents and Settings\amrita\Desktop\db
OR
"Provider=MSDASQL.1;Persis
OR
"Provider=Microsoft.Jet.OL
Also the application will not know the path of Access file , is it possible for the DAO to work without the file path and only connectionstring ??
thnx
Amrita
"DBQ=C:\Documents and Settings\amrita\Desktop\db
OR
"Provider=MSDASQL.1;Persis
OR
"Provider=Microsoft.Jet.OL
Also the application will not know the path of Access file , is it possible for the DAO to work without the file path and only connectionstring ??
thnx
Amrita
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hi
how would the above thing will work in case of the connectionstring
"Provider=MSDASQL.1;Persis
here we are not specifing any file name?
Amrita
how would the above thing will work in case of the connectionstring
"Provider=MSDASQL.1;Persis
here we are not specifing any file name?
Amrita
Amrita,
Can you use the MSDASQL driver to connect to an Access database? Or is this connection string for a SQL Server database?
Sam
Can you use the MSDASQL driver to connect to an Access database? Or is this connection string for a SQL Server database?
Sam
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
wow thanks a lot ,
this was way above of what i had expected , thanks everybody specially will_scarlet7 ,CraigYellick
but its frustating to see functionality existing in vb and not in vb.net :( (hope microsoft listening)
anyways thanks everybody
this was way above of what i had expected , thanks everybody specially will_scarlet7 ,CraigYellick
but its frustating to see functionality existing in vb and not in vb.net :( (hope microsoft listening)
anyways thanks everybody
Glad to help!
God bless!
Sam
God bless!
Sam
Have you tried reading the the value via a DAO.QueryDef object?
Sam