VBA pass through query

  • Thread starter Thread starter Jason
  • Start date Start date
J

Jason

Is is possible to write a pass through query using VBA to
build the SQL string? Here's the code I have to build the
string, I just need to know how to use it to build a pass
through query.

Thanks in advance,

Jason

Dim strSQL As String

Dim strStart, strEnd, strVendor

strStart = [Forms]![frmDate]![StartDateAS400]
strEnd = [Forms]![frmDate]![EndDateAS400]
'strVendor = [Forms]![frmDate]![Vendor]

strSQL = "SELECT ORDN09 as PO, VNDR09 as VndrNum,
ITEM09 as PN, " & _
"OQTY09 as OrdQty, TQTY09 as TransQty, DUED09 as
DueDate, RECD09 as RecvDate " & _
"FROM ""PMP09"" " & _
"WHERE TQTY09>0 AND RECD09>" & strStart & " AND
RECD09<" & strEnd & " " & _
"IN ODBC;DSN=QDSN_AS400;"
'AND VNDR09 LIKE '" & strVendor & "'"
 
Jason,

Whenever I want to use a passthru query, I usually build a dummy using
the query grid that stores the connection information and a basic
SELECT statment.

Then, I use the QueryDef object to rewrite the SQL property of the
query to my string. something like:

Dim strSQL As String
Dim strStart, strEnd, strVendor

strStart = [Forms]![frmDate]![StartDateAS400]
strEnd = [Forms]![frmDate]![EndDateAS400]
'strVendor = [Forms]![frmDate]![Vendor]

strSQL = "SELECT ORDN09 as PO, VNDR09 as VndrNum, " _
& "ITEM09 as PN, OQTY09 as OrdQty, " _
& "TQTY09 as TransQty, DUED09 as DueDate, " _
& "RECD09 as RecvDate " _
& "FROM PMP09 " _
& "WHERE TQTY09 > 0 " _
& "AND RECD09>" & cdate(strStart) " " _
& "AND RECD09<" & cdate(strEnd)
Currentdb.Querydefs("myPassThru").sql = strSQL

--
HTH

Dale Fye


Is is possible to write a pass through query using VBA to
build the SQL string? Here's the code I have to build the
string, I just need to know how to use it to build a pass
through query.

Thanks in advance,

Jason

Dim strSQL As String

Dim strStart, strEnd, strVendor

strStart = [Forms]![frmDate]![StartDateAS400]
strEnd = [Forms]![frmDate]![EndDateAS400]
'strVendor = [Forms]![frmDate]![Vendor]

strSQL = "SELECT ORDN09 as PO, VNDR09 as VndrNum,
ITEM09 as PN, " & _
"OQTY09 as OrdQty, TQTY09 as TransQty, DUED09 as
DueDate, RECD09 as RecvDate " & _
"FROM ""PMP09"" " & _
"WHERE TQTY09>0 AND RECD09>" & strStart & " AND
RECD09<" & strEnd & " " & _
"IN ODBC;DSN=QDSN_AS400;"
'AND VNDR09 LIKE '" & strVendor & "'"
 
Back
Top