Warning! Newbie at play: pass-thru querys in VBA

[jive6243]

Hiya-

I'm using the self-teach method for VBA, folks, and so far it hasn't been very effective, so bare with me...

i am performing a pass-thru query in VBA using code that i updated from another db. it works fine, however, i would like to have the results appear so i can do a quick, one-time glance at the data.

Can someone please tell/show me how to do this? Thanks in advance!

Function PTQ2()
Dim mydb As DAO.Database, myq As QueryDef, thesql As String
Dim myptq As QueryDef
Dim myparams As String
Dim rst As Recordset
Dim strQry As String
Dim strUPC As String
Dim strSep As String
Dim weekBegin, weekEnd, formatWeekEnd

'***********************************
Set mydb = CurrentDb()
Set myptq = mydb.QueryDefs("q   4 midweek results")
myptq.Connect = "ODBC;DSN=tdata;UID=123456;PWD=123456;database=vproddly"

weekBegin = InputBox("Enter Begin Date in YYYY-MM-DD format", "Date Prompt")
weekEnd = DateAdd("d", 7, weekBegin)
formatWeekEnd = Format(weekEnd, "yyyy-mm-dd")

strQry = "SELECT p_upc_id " & _
"FROM [t 1 Item Codes];"
strUPC = "p_upc_id"
strSep = ","

thesql = "select p_upc_id, day_dt, ut_id, tg_sl_am, tg_sl_qt " & _
"FROM DLY_UT_P_AD_HST " & _
"WHERE ut_id in(169, 178, 182, 183, 198, 206, 214, 215, 218, 228) " & _
"AND day_dt BETWEEN Date '" & weekBegin & "' AND Date '" & formatWeekEnd & "' " & _
"AND p_upc_id IN (" & MegaString(strQry2, strUPC2, strSep2) & ")"

myptq.SQL = thesql

myptq.Close
mydb.Close

End Function

 

[KeithG]

on up a regular query and go to Query/ SQL Specific/ PassThrough. Then you can type in your SQL statement and supply the connetion information by select the properties button.

 

[jive6243]

Thank you, Keith. However, i need to have this in vba as i cannot use changing parameters in a pass-through, from what i'm told. so i have an access table that holds the updated information, and via the vba, the string is appended to the existing pass-through query.

I think i may have answered my own question. I added the following to my code--

docmd.openquery "q 4 midweek results",acViewNormal, acReadOnly

 

[KeithG]

Yes that is true you really can't have parameters in the pass through query but I made a function that I use to change the values in the where clause of the pass through queries to the correct parameter value. I could post a sample if you would like.

 

[jive6243]

Yes please!!!

 

[KeithG]

Here you go, when a user opens form2 and enters there UserID Password and the date parameter, the modifySQL function will update the connection string with the new password and UserId (so that more than one user can login) and then it also changes the date in the where clause of the pass through queries SQL statements. I have more advanced copies of the ModifySQL function but all those database have senitive info and I can't post them.

 

[jive6243]

Thanks a lot! I'll definately investigate some ways i can incorporate this into my database. Thanks so much for your help!