Dynamic Pass Through Query in VBA

[lmonaghan]

I have created several pass through queries in Access, but I would like to execute one in VBA because I want to create a dynamic SQL string. I am using Access 2007.

Here is the existing SQL I am using in my pass through query (Oracle 11g):

SELECT ticket_id, short_code FROM vw_billing_transaction WHERE ticket_id IN ('21089-0215K-1708C-03WG1','2108C-02113-2004Q-02YTD','2108A-0211H-19576-03PQ7','21081-0215K-17407-03JJM');

Here is my connection string:

ODBC;DSN=OMPUBLIC;UID=OMPUBLIC;PWD=ompublic;DBQ=PROD ;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;

<B>So first and foremost, I would love to know how to execute that in VBA in MS Access.</B>

Secondly, I would like that SQL string to be dynamic based on the results from another query within my database. I have a table that contains the complete list of ticket_ids I need to look up in the Oracle database. Is there a way to pass in that list in the SQL statement above?

Here is the query to generate the list of ticket_ids needed:

SELECT qryUSCellularRefundsInterim.[Partner Transaction ID] AS ticket_id
FROM qryUSCellularRefundsInterim;

Thanks in advance for any help.

 

[lmonaghan]

Okay, I looked some more in the help file and was able to get my first question resolved with the following code:

<blockquote><code>
Sub passthrough_query_test()
Dim dbs As Database
Dim qdfPassThrough As QueryDef
Dim qdfTemp As QueryDef

Set dbs = CurrentDb()

Set qdfPassThrough = dbs.CreateQueryDef("ShortCodes")

qdfPassThrough.Connect = _
"ODBC;DSN=OMPUBLIC;UID=OMPUBLIC;PWD=ompublic;DBQ=PROD ;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F ;FWC=F;FBS=64000;TLO=O;"

qdfPassThrough.SQL = "SELECT ticket_id, short_code FROM vw_billing_transaction WHERE ticket_id IN ('21089-0215K-1708C-03WG1','2108C-02113-2004Q-02YTD','2108A-0211H-19576-03PQ7','21081-0215K-17407-03JJM');"

qdfPassThrough.ReturnsRecords = True

With dbs
Set qdfTemp = .CreateQueryDef("tmpTable", "SELECT * from ShortCodes")
DoCmd.OpenQuery "tmpTable"
.QueryDefs.Delete "tmpTable"
End With

dbs.QueryDefs.Delete "ShortCodes"
dbs.Close

End Sub</code></blockquote>

Now I need to find a way to generate a list of the ticket_ids to use as criteria in my pass through query. Since each ticket_id is text, I need the " ' " symbol around each one. Also, is there a string length limitation?

 

[ComputerCharmer]

I am new to VBA as well. I do see one error, a subroutine will not produce results. Try changing from a subroutine to a function. Look at this for an indepth explanation: http://www.excelfunctions.net/VBA-Functions-And-Subroutines.html

Good luck!

 

[james_lankford]

well its been 3 years
hopefully he's found a solution by now