473,239 Members | 1,453 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,239 software developers and data experts.

Is there a way to insert Queries programmatically?

3
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.
Jul 16 '07 #1
5 4422
ADezii
8,834 Expert 8TB
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.
Jul 17 '07 #2
DonnaL
3
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.
Jul 18 '07 #3
ADezii
8,834 Expert 8TB
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.
Jul 18 '07 #4
Hi!

You can do this by ADOX:

Add Reference: COM: "Microsoft ADO Ext. 2.x for DDL and Security"
Add Reference: .NET: adodb
Expand|Select|Wrap|Line Numbers
  1. using ADOX;
  2. using ADODB;
  3.  
  4. namespace X
  5. {
  6.     class a
  7.     {
  8.         public void b()
  9.         {
  10.             string connectionString = "Provider='Microsoft.Jet.OLEDB.4.0'; Data  Source='c.mdb'";
  11.             ADOX.CatalogClass cat = new ADOX.CatalogClass();
  12.             ADODB.Connection connection = new ADODB.ConnectionClass();
  13.             connection.Open(connectionString, "", "", 0);
  14.             cat.ActiveConnection = connection;
  15.  
  16.             StringBuilder sb = new StringBuilder ();
  17.             CommandClass command = new CommandClass();
  18.             command.CommandType = ADODB.CommandTypeEnum.adCmdText;
  19.             command.CommandText = GetQueryCommandText();
  20.             cat.Procedures.Append("Query", command);
  21.             connection.Close();
  22.             cat = null;
  23.         }
  24.     }
  25. }
  26.  
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.
Aug 2 '07 #5
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
Expand|Select|Wrap|Line Numbers
  1. using ADOX;
  2. using ADODB;
  3.  
  4. namespace X
  5. {
  6.     class a
  7.     {
  8.         public void b()
  9.         {
  10.             string connectionString = "Provider='Microsoft.Jet.OLEDB.4.0'; Data  Source='c.mdb'";
  11.             ADOX.CatalogClass cat = new ADOX.CatalogClass();
  12.             ADODB.Connection connection = new ADODB.ConnectionClass();
  13.             connection.Open(connectionString, "", "", 0);
  14.             cat.ActiveConnection = connection;
  15.  
  16.             StringBuilder sb = new StringBuilder ();
  17.             CommandClass command = new CommandClass();
  18.             command.CommandType = ADODB.CommandTypeEnum.adCmdText;
  19.             command.CommandText = GetQueryCommandText();
  20.             cat.Procedures.Append("Query", command);
  21.             connection.Close();
  22.             cat = null;
  23.         }
  24.     }
  25. }
  26.  
If you like ADOX. You can even build a brand new Access Database with ADOX.

Cheers

Ralf
Aug 7 '07 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

16
by: Philip Boonzaaier | last post by:
I want to be able to generate SQL statements that will go through a list of data, effectively row by row, enquire on the database if this exists in the selected table- If it exists, then the colums...
8
by: Kragen Sitaker | last post by:
ERROR: Cannot insert a duplicate key into unique index pg_class_relname_nsp_index We've been getting this error in our application every once in a while --- typically once an hour to once a day,...
1
by: Lumpierbritches | last post by:
Thank you in advance. Is there a way to programmatically fill an Unbound Form with a command button, using Queries built? I have a program that has about 10 different queries that all fill the...
11
by: Gord D via AccessMonster.com | last post by:
I have a small ASP website with an Access2000 database. Seems someone has found it and INSERTS records into a table by typing the URL and not via web page/form. I know this as I have some...
9
by: Jack | last post by:
Hi, I am gathering the input values to a form using Request.form method from the processing page. After all the data is captured, I am building sql statement out of it. Using a response.write...
3
by: Bob Bedford | last post by:
hello I'm looking for some functions or objects allowing to select-insert-update-delete from any table in a mysql database without the need to create a new query every time. Example: ...
11
by: Eugene Anthony | last post by:
The code bellow functions well when I attemp to delete a record. But when I attemp to insert a record I am getting the following error -2147217900:Syntax error in INSERT INTO statement. How do I...
3
by: Dave | last post by:
I have an old web app that ues an Access database and ASP 3.0. I need to build an INSERT statement based on the contents of a form. What is the best way to handle blank text boxes that are...
2
by: paulmitchell507 | last post by:
I think I am attempting a simple procedure but I just can't figure out the correct syntax. My asp (classic) page runs a SELECT query to obtain dates and ID's from 2 tables uSQL = "SELECT...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.