Is there a way to insert Queries programmatically?

[DonnaL]

I'm using Access 2000, but this question likely pertains to any version of Access. Simply put, is there a programmatic way of inserting a new Query in whatever master system table stores these (maybe QueryDefs?), so I wouldn't have to use the IDE and open Access to do it?

Here's more information if that helps: I can open Access itself and create a Query. I can save it and name it something meaningful. I can then execute that query just by double-clicking on it from within Access. But better yet, I can execute that query programatically via VB, ASP.NET, etc.

Meanwhile, my database naturally has data tables where I can insert records also programatically, with either SQL INSERT statements directly in my ASP.NET code or by calling stored queries that perform INSERT actions and passing parameters. This is typical for displaying an insert form on a web page and having the user fill out fields and then the code inserts a new record into the database table(s) as needed.

However, I occasionally want to add more stored queries to my Access database that has already been uploaded to my web host server and updated by various people. In the past, I've been bringing down the site for maintenance, downloading the entire Access .mdb file locally, adding my new stored queries, and reuploading the entire Access .mdb to the server, then bringing my site back up for use.

However, this would all be much easier and quicker if I could create an "admin" page where only I log in and I could create my own new stored queries in SQL, just as if I were using the Access IDE, and my code would then insert them into whatever system table stores these, etc. I just don't know the name of the table, its structure, and/or whether this would be possible.

I hope this makes sense. And while I don't think I'm the first person to want to do this, I did try to search high and low on your forum and other places on the Net and couldn't find anything that addressed this. Please forgive me if I just overlooked it in your numerous FAQs and Tutorials, etc.

Thanks.

[ADezii]

I'm using Access 2000, but this question likely pertains to any version of Access. Simply put, is there a programmatic way of inserting a new Query in whatever master system table stores these (maybe QueryDefs?), so I wouldn't have to use the IDE and open Access to do it?

Here's more information if that helps: I can open Access itself and create a Query. I can save it and name it something meaningful. I can then execute that query just by double-clicking on it from within Access. But better yet, I can execute that query programatically via VB, ASP.NET, etc.

Meanwhile, my database naturally has data tables where I can insert records also programatically, with either SQL INSERT statements directly in my ASP.NET code or by calling stored queries that perform INSERT actions and passing parameters. This is typical for displaying an insert form on a web page and having the user fill out fields and then the code inserts a new record into the database table(s) as needed.

However, I occasionally want to add more stored queries to my Access database that has already been uploaded to my web host server and updated by various people. In the past, I've been bringing down the site for maintenance, downloading the entire Access .mdb file locally, adding my new stored queries, and reuploading the entire Access .mdb to the server, then bringing my site back up for use.

However, this would all be much easier and quicker if I could create an "admin" page where only I log in and I could create my own new stored queries in SQL, just as if I were using the Access IDE, and my code would then insert them into whatever system table stores these, etc. I just don't know the name of the table, its structure, and/or whether this would be possible.

I hope this makes sense. And while I don't think I'm the first person to want to do this, I did try to search high and low on your forum and other places on the Net and couldn't find anything that addressed this. Please forgive me if I just overlooked it in your numerous FAQs and Tutorials, etc.

Thanks.

First things first:

1. Tools ==> Options ==> View Tab ==> Under the Show Section ==> Check System Objects.
2. The above sequence will turn ON the display of System Objects, in your case System Tables, in the Database Window.
3. To the best of my knowledge, Query related information is stored in 2 System Tables, namely: MSysObjects and MSysQueries.
4. In my humble opinion, trying to create Queries and save them directly to the System Tables bypassing the Access GUI, would be a daunting task to say the least. I'm not even sure if this is possible.
5. It seems as though Microsoft does not provide any documentation on the format of these System Tables, since direct manipulation can cause disastrous results. I imagine that it would be analogous to directly manipulating the System Registry.
6. Hope this helps a little - should you ever actually implement your ideas, please let us know since I'm sure that many of our Moderators/Experts would be very interested in how you accomplished this task.
7. Good luck in your venture.

[DonnaL]

Thanks for your reply. In looking at those two System tables and experimenting with a few new queries, I could probably guess at many of the fields and rows that I would need, but some fields are storing LOBs and that just raises a red flag for me.

I'm dealing with CLOBs in Oracle at work and I really don't need to learn the Access way of the comparable binary objects just for my simple little application. I'll do it the old-fashioned way and create new queries through the IDE and reupload my .mdb each time.

It just seemed like it would be a nifty way to do something advanced, but like you implied (or said), Microsoft undoubtedly keeps a lot of this secret since they don't want people messing with System tables that could cause other problems.

Thanks again. If I ever do revisit this topic and find a way to do it, I'll post it.

[ADezii]

Thanks for your reply. In looking at those two System tables and experimenting with a few new queries, I could probably guess at many of the fields and rows that I would need, but some fields are storing LOBs and that just raises a red flag for me.

I'm dealing with CLOBs in Oracle at work and I really don't need to learn the Access way of the comparable binary objects just for my simple little application. I'll do it the old-fashioned way and create new queries through the IDE and reupload my .mdb each time.

It just seemed like it would be a nifty way to do something advanced, but like you implied (or said), Microsoft undoubtedly keeps a lot of this secret since they don't want people messing with System tables that could cause other problems.

Thanks again. If I ever do revisit this topic and find a way to do it, I'll post it.

You're quite welcome.

[Sharky1]

Hi!

You can do this by ADOX:

Add Reference: COM: "Microsoft ADO Ext. 2.x for DDL and Security"
Add Reference: .NET: adodb

using ADOX;
using ADODB;
 
namespace X
{
    class a
    {
        public void b()
        {
            string connectionString = "Provider='Microsoft.Jet.OLEDB.4.0'; Data  Source='c.mdb'";
            ADOX.CatalogClass cat = new ADOX.CatalogClass();
            ADODB.Connection connection = new ADODB.ConnectionClass();
            connection.Open(connectionString, "", "", 0);
            cat.ActiveConnection = connection;
 
            StringBuilder sb = new StringBuilder ();
            CommandClass command = new CommandClass();
            command.CommandType = ADODB.CommandTypeEnum.adCmdText;
            command.CommandText = GetQueryCommandText();
            cat.Procedures.Append("Query", command);
            connection.Close();
            cat = null;
        }
    }
}
 

If you like ADOX. You can even build a brand new Access Database with ADOX.

Cheers

Ralf

I'm using Access 2000, but this question likely pertains to any version of Access. Simply put, is there a programmatic way of inserting a new Query in whatever master system table stores these (maybe QueryDefs?), so I wouldn't have to use the IDE and open Access to do it?

Here's more information if that helps: I can open Access itself and create a Query. I can save it and name it something meaningful. I can then execute that query just by double-clicking on it from within Access. But better yet, I can execute that query programatically via VB, ASP.NET, etc.

Meanwhile, my database naturally has data tables where I can insert records also programatically, with either SQL INSERT statements directly in my ASP.NET code or by calling stored queries that perform INSERT actions and passing parameters. This is typical for displaying an insert form on a web page and having the user fill out fields and then the code inserts a new record into the database table(s) as needed.

However, I occasionally want to add more stored queries to my Access database that has already been uploaded to my web host server and updated by various people. In the past, I've been bringing down the site for maintenance, downloading the entire Access .mdb file locally, adding my new stored queries, and reuploading the entire Access .mdb to the server, then bringing my site back up for use.

However, this would all be much easier and quicker if I could create an "admin" page where only I log in and I could create my own new stored queries in SQL, just as if I were using the Access IDE, and my code would then insert them into whatever system table stores these, etc. I just don't know the name of the table, its structure, and/or whether this would be possible.

I hope this makes sense. And while I don't think I'm the first person to want to do this, I did try to search high and low on your forum and other places on the Net and couldn't find anything that addressed this. Please forgive me if I just overlooked it in your numerous FAQs and Tutorials, etc.

Thanks.

[Sharky1]

Hi!

Explanation in German, samples in English, language of samples VB:

http://www.activevb.de/tutorials/tut_adox/adox.html

It's good to get some ideas ;-)

Have fun

Ralf

Hi!

You can do this by ADOX:

Add Reference: COM: "Microsoft ADO Ext. 2.x for DDL and Security"
Add Reference: .NET: adodb

using ADOX;
using ADODB;
 
namespace X
{
    class a
    {
        public void b()
        {
            string connectionString = "Provider='Microsoft.Jet.OLEDB.4.0'; Data  Source='c.mdb'";
            ADOX.CatalogClass cat = new ADOX.CatalogClass();
            ADODB.Connection connection = new ADODB.ConnectionClass();
            connection.Open(connectionString, "", "", 0);
            cat.ActiveConnection = connection;
 
            StringBuilder sb = new StringBuilder ();
            CommandClass command = new CommandClass();
            command.CommandType = ADODB.CommandTypeEnum.adCmdText;
            command.CommandText = GetQueryCommandText();
            cat.Procedures.Append("Query", command);
            connection.Close();
            cat = null;
        }
    }
}
 

If you like ADOX. You can even build a brand new Access Database with ADOX.

Cheers

Ralf