By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,273 Members | 1,032 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,273 IT Pros & Developers. It's quick & easy.

Inserting a value from an Access form into a SQL pass through query

P: 94
I am creating a small Access db which performs a series of updates to a SQL database.

The Access db consists of a ‘Main Form’, from which the user can run each update via a series of command buttons.

Each update has been written as a SQL pass-through query.

Example Update:
UPDATE unit_instance_occurrences uio
SET fes_active_places =
(SELECT COUNT(*) FROM registration_units ru
WHERE ru.fes_unit_instance_code = uio.fes_uins_instance_code
AND ru.uio_occurrence_code = uio.calocc_occurrence_code
AND ru.progress_status = 'A'
AND ru.uio_occurrence_code = 07);
Each query is dependant on a year (Eg: 07) however I want the user to determine that year from a combo box on the ‘Main Form’.

How do I best go about this?

The key thing is that i want to be able to run my pass through query from a button on the form. Before clicking the button the user must select the year. Somehow I either want to be able to insert that year as a parameter into the body of the PTQ or if necessary I can rewrite the pass-through update queries as pure Access-update queries and insert it there instead.

Have pretty much hit a wall with this so any help would be greatly appreciated!
Sep 13 '07 #1
Share this Question
Share on Google+
22 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
The best way to do this is to create the query dynamically in VBA. For the code to work the query will already have to be created.

For the purposes of this code I have put it on a command button called cmdExecuteQuery and I've given the combo box the name cboYear as I don't know it.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdExecuteQuery()
  2. Dim db As DAO.database
  3. Dim qdfPassThru As DAO.QueryDef
  4. Dim strSQL As String
  5.  
  6.     Set db = CurrentDb
  7.     strSQL = "UPDATE unit_instance_occurrences uio " & _
  8.              "SET fes_active_places = " & _
  9.              "(SELECT COUNT(*) FROM registration_units ru " & _
  10.              "WHERE ru.fes_unit_instance_code = uio.fes_uins_instance_code " & _
  11.              "AND ru.uio_occurrence_code = uio.calocc_occurrence_code " & _
  12.              "AND ru.progress_status = 'A' " & _
  13.              "AND ru.uio_occurrence_code = " & Me!cboYear & ");"
  14.  
  15.     'create query
  16.     Set qdfPassThru = db.CreateQueryDef("QueryName")
  17.     qdfPassThru.SQL = strSQL
  18.     qdfPassThru.Execute ' you can leave this out if you don't want to run the query
  19.  
  20.     Set qdfPassThru = Nothing
  21.     Set db = Nothing
  22.  
  23. End Sub
  24.  
Sep 14 '07 #2

P: 94
Thanks for your reply!
Have copied the code in exactly and renamed my button and combo box accordingly but am still having a problem. Nothing is happening at all when i click the button. By placing a series of messsage boxes in the code i have narrowed it down to the fact that my VB doesn't seem to understand the first 2 commands.

Dim db As DAO.database
Dim qdfPassThru As DAO.QueryDef

Even putting a message box infront of those two lines still nothing happens.
When i cut those two lines out i see the message boxes.

Any ideas???

Private Sub cmdExecuteQuery_Click()
MsgBox ("Test1")
Dim db As DAO.database
Dim qdfPassThru As DAO.QueryDef
Dim strSQL As String
MsgBox ("Test2")
Set db = CurrentDb
strSQL = "UPDATE unit_instance_occurrences uio " & _
"SET fes_active_places = " & _
"(SELECT COUNT(*) FROM registration_units ru " & _
"WHERE ru.fes_unit_instance_code = uio.fes_uins_instance_code " & _
"AND ru.uio_occurrence_code = uio.calocc_occurrence_code " & _
"AND ru.progress_status = 'A' " & _
"AND ru.uio_occurrence_code = " & Me!cboYear & ");"
MsgBox ("Test3")
'create query
Set qdfPassThru = db.CreateQueryDef("QueryName")
qdfPassThru.SQL = strSQL
qdfPassThru.Execute ' you can leave this out if you don't want to run the query
MsgBox ("Test4")
Set qdfPassThru = Nothing
Set db = Nothing
MsgBox ("Test5")
End Sub
Sep 17 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks for your reply!
Have copied the code in exactly and renamed my button and combo box accordingly but am still having a problem. Have narrowed it down to the fact that....

Dim db As DAO.database
Dim qdfPassThru As DAO.QueryDef

doesn't seem to be recognised in my visual basic. Any ideas?????
In the VBA editor, go to Tools - References and in the list of ticked reference libraries find the one called Microsoft DAO X.X Object Library and tick it.
Sep 17 '07 #4

FishVal
Expert 2.5K+
P: 2,653
Hi, there.

Check whether your button name is [cmdExecuteQuery]
Check whether your button "OnClick" property is still properly set.
  • Go to form design view.
  • Open the button properties window.
  • Check whether "OnClick" is set to [Event Procedure]
  • Click on [...] button rightward to get to cmdExecuteQuery_Click sub.

P.S. Never mind. Haven't read your last post thoroughly. The problem definitely seems to be in references.
Sep 17 '07 #5

P: 94
Thanks for both your help!

At the risk of sounding stupid.........It is now falling over after MsgBox3
Private Sub cmdExecuteQuery_Click()
MsgBox ("Test1")

Dim db As DAO.database
Dim qdfPassThru As DAO.QueryDef
Dim strSQL As String
MsgBox ("Test2")

Set db = CurrentDb
strSQL = "UPDATE fes_unit_instance_occurrences uio " & _
"SET fes_active_places = " & _
"(SELECT COUNT(*) FROM fes_registration_units ru " & _
"WHERE ru.fes_unit_instance_code = uio.fes_uins_instance_code " & _
"AND ru.uio_occurrence_code = uio.calocc_occurrence_code " & _
"AND ru.progress_status = 'A' " & _
"AND ru.uio_occurrence_code = " & Me!cboYear & ");"
MsgBox ("Test3")

'create query
Set qdfPassThru = db.CreateQueryDef("Test")
MsgBox ("Test4")

qdfPassThru.SQL = strSQL
qdfPassThru.Execute ' you can leave this out if you don't want to run the query
MsgBox ("Test5")

Set qdfPassThru = Nothing
Set db = Nothing
MsgBox ("Test6")

End Sub
I can see that the update query "Test" is being created correcly but it does not run.
When trying to run "Test" independently i get the error message "Operation must use an updateable query"

Hope you can help again!!
Sep 18 '07 #6

FishVal
Expert 2.5K+
P: 2,653
Hi, there.

At the risk of sounding stupid......... Did you delete the query before run the code 2nd time?
Sep 18 '07 #7

P: 94
I have tried that, it just doesn't seem to get any further than trying to execute the update query...
Sep 18 '07 #8

FishVal
Expert 2.5K+
P: 2,653
I have tried that, it just doesn't seem to get any further than trying to execute the update query...
I suggest you to create working pass-through query in query builder.
Then in code just change SQL expression.
Expand|Select|Wrap|Line Numbers
  1. CurrentDb.QueryDefs("qrySomeQuery").SQL="......."
  2.  
Sep 18 '07 #9

P: 94
I'm a bit confused....how does that code fit into mine? Which bits do i need to replace?
Sep 19 '07 #10

FishVal
Expert 2.5K+
P: 2,653
I'm a bit confused....how does that code fit into mine? Which bits do i need to replace?
  • create working pass-through query in Query builder and save it as e.g. [qrySomeQuery]
  • then place code like the following in your form module
    Expand|Select|Wrap|Line Numbers
    1. Private Sub cmdExecuteQuery_Click()
    2.     Dim strSQL As String
    3.  
    4.     strSQL = "UPDATE unit_instance_occurrences uio " & _
    5.              "SET fes_active_places = " & _
    6.              "(SELECT COUNT(*) FROM registration_units ru " & _
    7.              "WHERE ru.fes_unit_instance_code = uio.fes_uins_instance_code " & _
    8.              "AND ru.uio_occurrence_code = uio.calocc_occurrence_code " & _
    9.              "AND ru.progress_status = 'A' " & _
    10.              "AND ru.uio_occurrence_code = " & Me!cboYear & ");"
    11.  
    12.     With CurrentDb.QueryDefs("qrySomeQuery")
    13.         .SQL = strSQL
    14.         .Execute
    15.     End With
    16. End Sub
    17.  
Sep 19 '07 #11

P: 94
Thanks!

Unfortunately still finding the same error "Operation must use an updateable query". I have just started another thread at....
http://www.thescripts.com/forum/show...22#post2826022

Any help on that would be great!!
Sep 19 '07 #12

FishVal
Expert 2.5K+
P: 2,653
Does the query built in query builder work? If so, post the query SQL.
Sep 19 '07 #13

P: 94
Unfortunately I don't have query builder. Can you suggest somewhere to get it from?
Sep 20 '07 #14

FishVal
Expert 2.5K+
P: 2,653
Unfortunately I don't have query builder. Can you suggest somewhere to get it from?
I've meant query design which is the part of all Access versions.
Sorry if have confused you.
Sep 20 '07 #15

MMcCarthy
Expert Mod 10K+
P: 14,534
Is "unit_instance_occurrences uio" a query or a table?
Sep 21 '07 #16

P: 94
Is "unit_instance_occurrences uio" a query or a table?
"unit_instance_occurrences uio" is a table, as is "registration_units ru"
Sep 21 '07 #17

MMcCarthy
Expert Mod 10K+
P: 14,534
"unit_instance_occurrences uio" is a table, as is "registration_units ru"
I'm trying to remember general SQL syntax. In Access a tablename which included spaces would have to be enclosed with square brackets. Whats the rule in your backend. You don't mention what backend system you are using.
Sep 21 '07 #18

P: 94
My latest code...
Private Sub New_Click()
On Error GoTo err_New_Click
Dim db As DAO.Database
Dim qdfPassThru As DAO.QueryDef
Set db = CurrentDb
CurrentDb.QueryDefs("Active Update Query").SQL =
"UPDATE fes_unit_instance_occurrences uio " & _
"SET fes_active_places = " & _
"(SELECT COUNT(*) FROM fes_registration_units ru " & _
"WHERE ru.fes_unit_instance_code = uio.fes_uins_instance_code " & _
"AND ru.uio_occurrence_code = uio.calocc_occurrence_code " & _
"AND ru.progress_status = 'A' " & _
"AND ru.uio_occurrence_code = " & Me!cboYear & ");"
CurrentDb.QueryDefs("Active Update Query").Updatable
Set qdfPassThru = db.QueryDefs("Active Update Query")
qdfPassThru.Execute ' you can leave this out if you don't want to run the query
Set qdfPassThru = Nothing
Set db = Nothing
exit_New_Click:
Exit Sub
err_New_Click:
MsgBox (Err.Description)
End Sub
"fes_unit_instance_occurrences" is the table name "uio" it's alias and
"fes_registration_units" is the table name "ru" is it's alias.
Both tables are in my access db as ODBC linked tables to a SQL server.
Also, the combo box "cboYear" is linked to another ODBC table to pick up the year.
Sep 21 '07 #19

P: 94
Sorry, the line...

"CurrentDb.QueryDefs("Active Update Query").Updatable"

has since been removed.
Sep 21 '07 #20

MMcCarthy
Expert Mod 10K+
P: 14,534
Sorry, the line...

"CurrentDb.QueryDefs("Active Update Query").Updatable"

has since been removed.
You have set up "Active Update Query" as a pass through query though? It is an action query, yes?

Check the properties of the query.
Sep 21 '07 #21

P: 94
Success at last!!!!!!!

Thank you so much.

Turns out it was as simple an error as the fact the "Active Pass Through" query was initially created as a Access update query. Upon recreating as pass through everything worked fine.
Sep 21 '07 #22

MMcCarthy
Expert Mod 10K+
P: 14,534
Success at last!!!!!!!

Thank you so much.

Turns out it was as simple an error as the fact the "Active Pass Through" query was initially created as a Access update query. Upon recreating as pass through everything worked fine.
That's great. Glad you got it working.
Sep 21 '07 #23

Post your reply

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