The Mich
asked on
SQL term to automate mk table and append
I have a SQL database where I need to cycle thru 25 queries to make and append to a table so I can pull the right information. I have one table w/25 values spread across 25 fields and in MS Access I have a form button the user presses and it runs the mk table and append queries. I tried to replicate this in SQL starting w/creating a view and then using the Instead trigger - but that approach didn't work. I'm not sure how to approach this problem or what terms/key words I should be looking at. Any assistance would be greatly appreciated.
In access a query def can be different types, such as select, update, insert or delete.
In SQL server a VIEW can only be a SELECT statement. You can attach triggers to views, but that would not be the right use case for this scenario.
This is just to be clear about the fact that SQL server VIEWS and access QUERY DEFS are not the same thing.
As always, there are more than 1 way to approach this, and it depends a bit on circumstances, such as where your source data is.
If some of the source data for your append queries are in the local frontend, then that complicates matters a bit, as we are now actually dealing with 2 databases.
So for now, I will assume all your data is in SQL server. You then have 2 options:
A) Create a stored procedure in SQL server
A single stored procedure can contain multiple SQL statements (Such as Insert, Update, Delete). So in fact the 25 queries you have could be contained in a single stored proc.
You would then have your access frontend execute the stored procedure (more on this later if need be)
B) Create 25 passthrough queries in access
A passthrough query "sends" the SQL statement directly to the server, byPASSING the Access database engine. As such, this also means the syntax used my be SQL server SQL and not Access SQL (There are slight differences)
In this way, you can execute all the queries still from your access frontend.
Both of these approaches only work if all the data is in SQL server already. If that is not the case, let us know some more details on your setup. Also try to explain your process a bit more. Perhaps an alternative solution that doesn't require 25 queries could be an option?
In SQL server a VIEW can only be a SELECT statement. You can attach triggers to views, but that would not be the right use case for this scenario.
This is just to be clear about the fact that SQL server VIEWS and access QUERY DEFS are not the same thing.
As always, there are more than 1 way to approach this, and it depends a bit on circumstances, such as where your source data is.
If some of the source data for your append queries are in the local frontend, then that complicates matters a bit, as we are now actually dealing with 2 databases.
So for now, I will assume all your data is in SQL server. You then have 2 options:
A) Create a stored procedure in SQL server
A single stored procedure can contain multiple SQL statements (Such as Insert, Update, Delete). So in fact the 25 queries you have could be contained in a single stored proc.
You would then have your access frontend execute the stored procedure (more on this later if need be)
B) Create 25 passthrough queries in access
A passthrough query "sends" the SQL statement directly to the server, byPASSING the Access database engine. As such, this also means the syntax used my be SQL server SQL and not Access SQL (There are slight differences)
In this way, you can execute all the queries still from your access frontend.
Both of these approaches only work if all the data is in SQL server already. If that is not the case, let us know some more details on your setup. Also try to explain your process a bit more. Perhaps an alternative solution that doesn't require 25 queries could be an option?
ASKER
John & Anders - Thank you.
All of my data is on the SQL side in the same database. I will look into stored procedures. Thank you for your help and clarification.
All of my data is on the SQL side in the same database. I will look into stored procedures. Thank you for your help and clarification.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
You also have a table that holds then names of these append queries (SP)? e.g Sp1,Sp2...etc
You want to execute them one by one in order to populate the resulting table?
The easy way would be to have a pass through query in Access that gets the table records of the table that holds the names of the SPs....with the info returned from the passthrough you will populate a recordset...then iterate the records/fields and use them to populate a temp "querydef" that will issue the command
Open in new window
where SPX is the Stored Procedure Name