Pass-through query access with value from form textbox (1 Viewer)

rvsebi

Registered User.
Local time
Today, 08:13
Joined
Jun 1, 2015
Messages
77
Hi, i want to make pass-trough quey in access with value from a textbox. Everything is ok except with part where i want to bring value from textbox.

Code:
Update aa
SET
aa.x1 = ac.x1,
aa.x2 = ac.x2, 
aa.x3 = ac.x3
from Table1 as aa
INNER JOIN Table2 as ac ON aa.y1 = ac.y1 AND aa.z1 = ac.z1
WHERE aa.w1 = '" & [Forms]![Form1]![w1] & "'

How can i bring that value to query?
Thank you!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:13
Joined
May 7, 2009
Messages
19,169
you can always modify the SQL part of the pass-through query.
create a pass-through without the "where" clause.
Code:
Dim strOrigSQL As String
Dim strModSQL As String
With Currentdb.QueryDefs("yourPassThroughQuery")
  'save the original sql statement
   strOrigSQL = .SQL
  'modify the sql
   strModSQL = strSQL & " Where aa.w1 = '" & [Forms]![Form1]![w1] & "'"
  'save modified sql back
   .SQL = strModSQL
  'execute the query
   .Execute
   're-instate the sql back
   .SQL = strOrigSQL
End With
 

rvsebi

Registered User.
Local time
Today, 08:13
Joined
Jun 1, 2015
Messages
77
Thank you for help. I did like u said and now i get "Run-time error '3146':ODBC--call failed" on .Execute :confused:

I think error is here:
'''
strModSQL = strSQL & " Where aa.w1 = '" & [Forms]![Form1]![w1] & "'"
'''


supose to be strOrigSQL. i will try again with this modify.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:13
Joined
May 7, 2009
Messages
19,169
sorry about that, I think I made some errors on this line:
Code:
'modify the sql
   strModSQL = strSQL & " Where aa.w1 = '" & [Forms]![Form1]![w1] & "'"
should be:
Code:
'modify the sql
   strModSQL = strOrigSQL & " Where aa.w1 = '" & [Forms]![Form1]![w1] & "'"
 

rvsebi

Registered User.
Local time
Today, 08:13
Joined
Jun 1, 2015
Messages
77
sorry about that, I think I made some errors on this line:
Code:
'modify the sql
   strModSQL = strSQL & " Where aa.w1 = '" & [Forms]![Form1]![w1] & "'"
should be:
Code:
'modify the sql
   strModSQL = strOrigSQL & " Where aa.w1 = '" & [Forms]![Form1]![w1] & "'"

:) i just saw the same and edited previous post
 

rvsebi

Registered User.
Local time
Today, 08:13
Joined
Jun 1, 2015
Messages
77
Hi again, can u help me a little bit please. I have problems translate this code
Code:
INSERT INTO Table1 
SELECT Table2.*
FROM Table2
into transact-sql because i want to update in the same time last column from Table1 (DateModify) with Date() and i dont know how. Both tabels have same columns except this last one from Table1(DateModify).
In fact code for this pass-trough query will be something like this
Code:
INSERT INTO Table1
SELECT * FROM Table2
WHERE condition;
, The part with Where u teach me already how to do it.
 
Last edited:

Minty

AWF VIP
Local time
Today, 06:13
Joined
Jul 26, 2013
Messages
10,355
I think you would need something like
Code:
INSERT INTO Table1
SELECT *, GetDate() FROM Table2
To fill in the missing field in the target table. GetDate() is the Equivalent of Now() in Access.
 

rvsebi

Registered User.
Local time
Today, 08:13
Joined
Jun 1, 2015
Messages
77
i already have this query on local, if i go on SQL view query look like this
Code:
INSERT INTO Table1 (DateModify)
SELECT Table2.*, Date() AS Expr1
FROM Table2
WHERE ....;
if i modify for pass-trough query your code is enough?
 

Minty

AWF VIP
Local time
Today, 06:13
Joined
Jul 26, 2013
Messages
10,355
That is a different query. That is only going to insert one field, and as written I'm pretty sure it would fail.
A pass through is simply a query or set of instructions, passed straight to the target database system and run in the that environment.

Have a read about the INSERT INTO syntax https://www.w3schools.com/sql/sql_insert.asp
 

rvsebi

Registered User.
Local time
Today, 08:13
Joined
Jun 1, 2015
Messages
77
Access show me query on SQL view like that. On local client it work very well but i wanted to make it pass-trough. Anyway this is another discussion. Maybe because my bad english i couldnt make u understand what i want.
So ... I have 2 tables (Table1 and Table 2) with same columns (a1,a2,a3). Table1 have one more column (ModifyDate).
In Table1 i keep history of Table2 data so every time when i change data in Table2 (columns a1,a2 or a3) i copy all in Table1 ( a1 to a1, a2 to a2, a3 to a3 and in ModifyDate i put Date() to know when i changed last time data). Now i know this
Code:
INSERT INTO Table1
SELECT * from Table2
will work to copy columns a1,a2,a3. Question is how to modify this code to put in the column ModifyDate the Date() or GETDATE() (u said) in the same time.
Thank you.
 

rvsebi

Registered User.
Local time
Today, 08:13
Joined
Jun 1, 2015
Messages
77
I think i solved problem much easier then query code :). In Table1 column ModifyDate i put Default Value GETDATE().
Thank you for your time!
 

Minty

AWF VIP
Local time
Today, 06:13
Joined
Jul 26, 2013
Messages
10,355
That will work, and is nice and simple, as would the initial response I gave you, however if your data is held in SQL Server (depending on the version) you could turn on change tracking and it would do the auditing for you.

Might save you a lot of work.
 

Users who are viewing this thread

Top Bottom