Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

Can I execute multiple queries in MS Access?

I want to execute something like this:
update Quotation set Brand='IBM by QUANTUM' where PN_IBM='10L6006';
insert into Quotation values ('','D7947-69001','PIII 500MHZ/512KB SL35E','HP by INTEL','','PIII 500MHZ/512KB SL35E','','','1','','',0,0,0,'0',0,'','2/16/2005 12:27:30 PM','01/01/2000','01/01/2000','si','si','','');
update Quotation set Brand='HP by INTEL' where PN_IBM='D7947-69001';
insert into Quotation values ('','D8155-69015','VECTRA VEI8/450MHZ FRU D7600-60005','HP','','VECTRA VEI8/450MHZ FRU D7600-60005','','','1','','',0,0,0,'0',0,'','2/18/2005 3:45:29 PM','01/01/2000','01/01/2000','si','si','','');
don't pay attention to the data, just the idea about creating a query, and try to execute more than one query to the database? It's that possible?
Thanks a lot for your help.
0
mjimeno
Asked:
mjimeno
  • 5
  • 3
  • 3
  • +2
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Sure.  

(1)  Pre-compile all of these queries by saving them, and remember the names.
(2)  Then in code, execute them sequentially like this... (uses ADO)

Dim cn as ADODB.Connection
Set cn = CurrentProject.Connection

With cn
  .Execute "Your First Query"
  .Execute "Your Second Query"
  .Execute "Your Third Query"
  .Execute "etc."
End With

Hope this helps.
-Jim
0
 
dannywarehamCommented:
You can run multiple queries (use Jim's method), but you will have to run them seperately and sequentially.

You cannot run two update queries (or any other type) simultaneously.

As well as saving the qeruies as objects, you can do it all through code:

Dim mySQL as String
   mySQL ="Select blah blah"       '<--- 1st query
DoCmd.RunSQL mySQL
   mySQL ="Select blah blah"       '<--- 2nd query
DoCmd.RunSQL mySQL
   mySQL ="Select blah blah"       '<--- 3rd query
DoCmd.RunSQL mySQL
'etc
0
 
BevinManianCommented:
You can write code as dannywareham put it.

Alternatively, you can save all the queries you want to run and call them one by one.

docmd.openquery "qry1"
docmd.openquery "qry2"
docmd.openquery "qry3"

Another way is by macros.
Run one macro that executes multiple queries.

Bev

0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
dannywarehamCommented:
If you're going to save the queries as objects, use Jim's method. ADO with Execute is slightly (marginally) faster than teh old DoCmd.Statement.
0
 
GRayLCommented:
You can lump the same type of Update query:
These two:
update Quotation set Brand='IBM by QUANTUM' where PN_IBM='10L6006';
update Quotation set Brand='HP by INTEL' where PN_IBM='D7947-69001';
Become:
Update Quotation set Brand=Switch(PN_IBM = '10L6006', 'IBM by QUANTUM',
                                                    PN_IBM = 'D7947-69001', 'HP by INTEL');

0
 
mjimenoAuthor Commented:
thanks everybody for your posts. I have 2 little questions: Where should I use the code suggested? in a macro or where else?
GRayL: if the update queries are for the the same table, can I use your method? and what about inserts? can I use it the same way?
Everybody: I have a text file with the queries, and according to what dannywareham and jimhorn posted, if I have about 30 queries in the file, I would need to create the queries objects for all of them, or at least copy and paste all of them to the code that runs them. Do you understand me? I'm not sure if that's possible but it would be the fastest way, like mysql for example, that the only thing you need is to load the script file and ready.
Thanks.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Where should I use the code suggested?
Wherever you want:  In a separate function which you call, in a form button's .Click event.
For multiple reasons, you should always use VBA code instead of macros.

>Everybody: I have a text file with the queries, and
Can you provide more detail?  I'm not sure what you mean here.
0
 
GRayLCommented:
mjimeno:  I just discovered that switch returns a null if the match is not found so it will null all records not in the switch.  I don't think you want to do that.  I am working on an alternative.
0
 
GRayLCommented:
mjimeno:  Change my query to the following:

Update Quotation set Brand=Switch(PN_IBM = '10L6006', 'IBM by QUANTUM', PN_IBM = 'D7947-69001', 'HP by INTEL') where Switch(PN_IBM = '10L6006', 'IBM by QUANTUM', PN_IBM = 'D7947-69001', 'HP by INTEL') is not null;

This will let you update the same field in the same table with whatever number of switch elements you use.  It will not set Brand to null where there is not a matching switch because of the WHERE clause.

I am still working on the insert problem.
0
 
GRayLCommented:
I do not see a way of combining multiple 'insert into' queries by listing values for every field in multiple records.  If you are going to take the time to do that, it would be just as easy to create a table named newRecords, with the structure identical to the Quotation table,  carefully enter your new data, validate by another party if necessary, and then when you are satisfied the new data are correct, do an insert query:

Insert into Quotation Select * from newRecords;

This will add all the records in newRecords to the existing Quotation table.
0
 
mjimenoAuthor Commented:
jimhorn: I have a text file generated by a program I developed with updates and inserts executed in a db I can't reach manually, just the file. I generated this file hoping I could use it easily, no matter how many queries were generated through a period. But now I realized, that it will not be just copying and pasting the whole text file with the 100 lines (for example) and run them in my access db. It would be easier for me, if I don't need to copy and paste every line. Even so, your solutions still help me, so I probably will not need to do as lot as work as I guess I need to do.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
If the schema is different for these lines, what you could do is instead of creating one text file, create many text files where the files have the same schema/specifications, then use the DoCmd.TransferText command (to really do it right create an Import Specification with each one) to import these files into many temporary Access tables that match the schema.  Once the data is in the tables, you can run queries to append/update/whatever all day long.

-Jim

0
 
mjimenoAuthor Commented:
I guess it will not be as easy as I though, but anyway thanks for your suggestions, I'll work on them. Now I'll split some points.
M
0
 
GRayLCommented:
Thanks, but why the B?  Looks more like you didn't like the answers rather than the quality of the answers.  Every app has its limits and this was one of them.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 5
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now