I know your frustration. I tried everything they recommended 3 years ago and
wasted my time too. What I use is a Access table to hold all my stored SQL
command strings that I'm going to need. The first field contains the
abbreviated command name that acts as a key to retrieve the SQL command.
The command string may contain Argument fields (?ARG1? to ?ARG#?) that I use
to insert anything I want into the command string, ie. data or commands.
While you must be cognisent of what your are doing it's a simple to use and
Sample table entry: (3 fields defined and are named immediately below)
SQLCmdName SQLCmd TrgtTbl
GetInvoices "Select invoice.co_id, invoice.st_id, invoice.inv_date,
invoice.inv_id,
invoice.cust_id, invoice.inv_acct_yr_mon, invoice.inv_acct_day,
invoice.inv_trans_type, invoice.inv_cntr_per, invoice.inv_sale_per,
invoice.inv_cashier_id, invoice_part.prod_line,
invoice_part.prod_grp_id,
invoice_part.sku_id, invoice_part.invp_item_billing,
invoice_part.invp_qty_billed, invoice_part.invp_unit_price,
invoice_part.invp_unit_cost, invoice_part.invp_unit_rebate,
invoice_part.invp_rebate_item
From invoice, invoice_part, customer
where invoice.co_id = invoice_part.co_id AND invoice.cust_id =
customer.cust_id AND
customer.co_id = invoice.co_id AND
customer.cust_category <> 17 and
customer.cust_category <> 20 and
invoice.st_id = invoice_part.st_id And
invoice.inv_date = invoice_part.inv_date AND
invoice.inv_date >= ?ARG1? AND
invoice.inv_date <= ?ARG2? AND
invoice.inv_id = invoice_part.inv_id AND
invoice.inv_id > 0 AND
invoice.inv_trans_type < 5 AND" Transactions
Here's the VB code:
Option Compare Database
Option Explicit
'This is a standalone function that returns an edited SQLcmd string
Public Function EditSqlcmd(Cmd As String, ArgNum As Integer, Argmt As
Variant) As String
'Replaces templated argument strings "?ARG##?" with some date, number or
string value
EditSqlcmd = Replace(Cmd, "?ARG" & ArgNum & "?", Argmt)
End Function
Public Sub GetCmdRS(RecSet As ADODB.Recordset, cnxn As ADODB.Connection)
' This subroutine retrieves all of the SQL Command string templates used in
this subsystem
Dim SQLsdscmdtxt As String
SQLsdscmdtxt = SQLCmd ' SQLCmd is a function call
RecSet.Open SQLsdscmdtxt, cnxn
End Sub
' This function builds the standard command to retrieve the table of all SQL
commands
Public Function SQLCmd() As String
SQLCmd = "SELECT SQLCmds.SQLCmdName, SQLCmds.SQLCmd, SQLCmds.TrgtTbl
From SQLCmds WITH OWNERACCESS OPTION"
End Function
Public Function GetSQLCmd(SQLCmdRs As ADODB.Recordset, SQLCmdstr As String)
As String
' Retrieve the SQL command from the table of SQL command Templates
SQLCmdRs.Filter = adFilterNone
' SQLCmdstr contains the name of the command to retrieve
SQLCmdRs.Filter = "SQLCmdName = '" & SQLCmdstr & "'"
GetSQLCmd = SQLCmdRs.Fields("SQLCmd")
End Function
This code is called in vb or vba using the following:
SQLCmd2 = GetSQLCmd(SQLCmds, "UPTrnMthYr")
SQLCmd2 = EditSqlcmd(SQLCmd2, 1, nxtMTH)
SQLCmd2 = EditSqlcmd(SQLCmd2, 2, nxtYR)
' Update the Transaction Month and Year table to the month and year
being processed
DoCmd.RunSQL SQLCmd2
Define all the variables lile SQLCMD2 as string, etc.
I create the SQLCmd table manually because it doesn't change.
The application using the above opens and gets the complete SQLCmd table
once and keeps the recordset around for use by other apps. These apps filter
the table retrieving the template desired, modifies the command and executes
it.
Good luck,
WylieCoyote
