Editing Passthrough SQL Query with Replace in Microsoft Access VBA

Question

In Access I have a SQL passthrough query that returns an entire table filtered by first name.

Example

SELECT * FROM dbo.Original_Data
WHERE first_name = 'Mike';

I am trying to write some VBA that on click of a button takes the passthrough query and replaces 'Mike' with any name I put in (this will later be done by a combo box but that's another step).

I have this where TempPT is the name of the passthrough query.

Private Sub Command12_Click()

TempPT = Replace(TempPT, "Mike", "Sam")    
DoCmd.OpenQuery "TempPT"

End Sub

This doesn't work.

Is there a way that whatever I type in where I have 'Sam' is what the passthrough query will filter by?

I am using passthrough because the DB is huge and clogs up Access.

Kostas K. · Accepted Answer · 2021-04-01 15:09:17Z

2

You need to edit the SQL property of the query. Probably something like this:

With CurrentDb().QueryDefs("TempPT")
    .SQL = Replace(.SQL, "Mike", "Sam") 
End With

DoCmd.OpenQuery "TempPT"

edited Apr 1, 2021 at 15:09

answered Apr 1, 2021 at 15:03

Kostas K.

8,5282 gold badges26 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sandy Tinker Over a year ago

Absolutely smashed it first time. Thanks a lot dude, plugged this in it works perfectly. Now to add that combo box in :)

Collectives™ on Stack Overflow

Editing Passthrough SQL Query with Replace in Microsoft Access VBA

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related