Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
Proper formatting on variable in SQL string within VBA/MS Access
Good morning - I have done a lot of testing and trying to teach myself on this, but I've not been successful. I have an ODBC connection to PeopleSoft and am attempting to put the PeopleSoft SQL into VBA. I am successful at reproducing the same output in Access when I do not declare variables, but am getting an error, "An unexpected token "Dept" was found following "DEPT". Expected tokens may include: ":", SQLSTATE=42601.
I have taken just this variable for DeptID (DEPT) and changed the data type in the 3 places I am using the value - in a table (AcctPeriod_tbl) that is updated with values from a user form (Accounting Period, DeptID, Business Unit, Fiscal Year - DeptID is a Short Text data type here), in PeopleSoft - it is Char10 here, and in VBA - as String shown below. I think I need to maybe add double quotes, or maybe a single quote after the equal sign, and perhaps ampersands around the variable but looking for some guidance. When I changed to "AND A.DEPTID = '" & DEPT & _ the error changes to "The string constant beginning with "" does not have an ending string delimiter. SQLSTATE=42603(#-10). I can provide the rest of the variables and code if needed, as I'm trying to first isolate this one.
I have taken just this variable for DeptID (DEPT) and changed the data type in the 3 places I am using the value - in a table (AcctPeriod_tbl) that is updated with values from a user form (Accounting Period, DeptID, Business Unit, Fiscal Year - DeptID is a Short Text data type here), in PeopleSoft - it is Char10 here, and in VBA - as String shown below. I think I need to maybe add double quotes, or maybe a single quote after the equal sign, and perhaps ampersands around the variable but looking for some guidance. When I changed to "AND A.DEPTID = '" & DEPT & _ the error changes to "The string constant beginning with "" does not have an ending string delimiter. SQLSTATE=42603(#-10). I can provide the rest of the variables and code if needed, as I'm trying to first isolate this one.
Private Sub erikatesting_Click()
Dim dbs As DAO.Database
Dim myQueryDef As DAO.QueryDef
Dim strSql As String
Dim rst As DAO.Recordset
Dim DEPT As String
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("AcctPeriod_tbl")
DEPT = rst!DeptID
strSql = "SELECT A.ACCOUNT AS ACCOUNT, B.DESCR AS DESCR, A.DEPTID AS DEPT, A.PRODUCT PRODUCT, FISCAL_YEAR AS YEAR, " & _
"FROM PSFSP8.PS_LEDGER A, PSFSP8.PS_GL_ACCOUNT_TBL B " & _
"WHERE ( A.BUSINESS_UNIT = ('WDBIL') " & _
"AND A.DEPTID = DEPT & _
" GROUP BY A.ACCOUNT, B.DESCR, A.PRODUCT, A.DEPTID, A.FISCAL_YEAR " & _
" ORDER BY 3, 1 WITH UR;"
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Here is the complete code if it helps:
End Sub
Private Sub erikatesting_Click()
Dim dbs As DAO.Database
Dim myQueryDef As DAO.QueryDef
Dim strSql As String
Dim rst As DAO.Recordset
Dim AP As Integer
Dim FY As Integer
Dim AC As String
Dim DEPT As String
Dim PR As String
Dim BU As String
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("AcctPeriod_tbl")
AP = rst!EndAcctPeriod
FY = rst!FiscYear
AC = rst!AccountID
DEPT = rst!DeptID
PR = rst!Product
BU = rst!BusUnit
strSql = "SELECT A.ACCOUNT AS ACCOUNT, B.DESCR AS DESCR, A.DEPTID AS DEPT, A.PRODUCT AS PRODUCT, A.FISCAL_YEAR AS YEAR, SUM(DECIMAL(CASE WHEN ACCOUNTING_PERIOD = AP THEN POSTED_TOTAL_AMT " & _
"ELSE 0 END,19, 2)),SUM(DECIMAL(CASE WHEN ACCOUNTING_PERIOD <= AP THEN POSTED_TOTAL_AMT ELSE 0 END + (CASE WHEN BUSINESS_UNIT = ('WDUSA') THEN CASE WHEN AP >= 9 THEN CASE WHEN ACCOUNTING_PERIOD IN (998,909) THEN POSTED_TOTAL_AMT " & _
"ELSE 0 END ELSE 0 END ELSE 0 END) + (CASE WHEN BUSINESS_UNIT = ('WDUSA') THEN CASE WHEN AP >= 11 THEN CASE WHEN ACCOUNTING_PERIOD = 904 THEN POSTED_TOTAL_AMT " & _
"ELSE 0 END ELSE 0 END ELSE 0 END) + (CASE WHEN BUSINESS_UNIT = ('WDBIL') THEN CASE WHEN AP >= 4 THEN CASE WHEN ACCOUNTING_PERIOD = 904 THEN POSTED_TOTAL_AMT " & _
"ELSE 0 END ELSE 0 END ELSE 0 END) + (CASE WHEN BUSINESS_UNIT = ('WDBIL') THEN CASE WHEN AP >= 7 THEN CASE WHEN ACCOUNTING_PERIOD = 907 THEN POSTED_TOTAL_AMT ELSE 0 End ELSE 0 End ELSE 0 END)+(CASE WHEN BUSINESS_UNIT = ('WDBIL') THEN " & _
"CASE WHEN AP >= 10 THEN CASE WHEN ACCOUNTING_PERIOD = 910 THEN POSTED_TOTAL_AMT ELSE 0 End ELSE 0 End ELSE 0 END)+(CASE WHEN BUSINESS_UNIT = ('WDBIL') THEN CASE WHEN AP >= 12 THEN CASE WHEN ACCOUNTING_PERIOD = 912 THEN " & _
"POSTED_TOTAL_AMT ELSE 0 END ELSE 0 END ELSE 0 END),19, 2)) " & _
"FROM PSFSP8.PS_LEDGER A, PSFSP8.PS_GL_ACCOUNT_TBL B " & _
"WHERE ( A.BUSINESS_UNIT = BU " & _
"AND A.LEDGER = ('ACTUALS') " & _
"AND A.ACCOUNT = B.ACCOUNT " & _
"AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PSFSP8.PS_GL_ACCOUNT_TBL B_ED WHERE B.SETID = B_ED.SETID " & _
"AND B.ACCOUNT = B_ED.ACCOUNT " & _
"AND B_ED.EFFDT <= CURRENT DATE) " & _
"AND A.FISCAL_YEAR = FY " & _
"AND A.ACCOUNT = AC " & _
"AND A.DEPTID = '" & DEPT & _
"AND A.PRODUCT = PR " & _
"AND A.ACCOUNTING_PERIOD IN (904,909,910,912,998,907))) " & _
" GROUP BY A.ACCOUNT, B.DESCR, A.PRODUCT, A.DEPTID, A.FISCAL_YEAR " & _
" ORDER BY 3, 1 WITH UR;"
End Sub
Whenever you are dealing with raw SQL and it is proving troublesome, its a good idea to output it to the debug window, then take it from there, and try to paste it into the query editor. This can usually help you resolve errors quickly.
I am having trouble making sense of your query though. It seems to end with a "with" statement which seems wrong. Furthermore, you are sorting by 2 constants, namely 3 and 1, but maybe that is a Peoplesoft special syntax..
In regards to dept you are missing a single ampersand at the end of line 16(original post) line 41 (updated post).
I am having trouble making sense of your query though. It seems to end with a "with" statement which seems wrong. Furthermore, you are sorting by 2 constants, namely 3 and 1, but maybe that is a Peoplesoft special syntax..
In regards to dept you are missing a single ampersand at the end of line 16(original post) line 41 (updated post).
AND A.DEPTID = '" & DEPT & "'" & _
In Access you should exactly follow type of field in SQL statement
If A.DEPTID is numeric, use:
"AND A.DEPTID = " & DEPT & _
if it is string, use quotes:
"AND A.DEPTID = '" & DEPT & "'" &_
or
"AND A.DEPTID = " & chr(34) & DEPT & chr(34) &_
If A.DEPTID is numeric, use:
"AND A.DEPTID = " & DEPT & _
if it is string, use quotes:
"AND A.DEPTID = '" & DEPT & "'" &_
or
"AND A.DEPTID = " & chr(34) & DEPT & chr(34) &_
The others already pointed out the concrete error. To avoid it, I prefer a more C-style, formalized approach using placeholders..
gives you
The {CRLF} is a pretty-pring/debug helper and can be removed.
Option Compare Database
Option Explicit
Private Sub erikatesting_Click()
Const SQL_SELECT As String = _
"SELECT *{CRLF} " & _
"FROM PSFSP8.PS_LEDGER A, PSFSP8.PS_GL_ACCOUNT_TBL B{CRLF} " & _
"WHERE A.BUSINESS_UNIT = {0}{CRLF} " & _
"AND A.LEDGER = 'ACTUALS'{CRLF} " & _
"AND B.ACCOUNT = B_ED.ACCOUNT{CRLF} " & _
"AND A.FISCAL_YEAR = {1}{CRLF} " & _
"AND A.ACCOUNT = {2}{CRLF} " & _
"AND A.DEPTID = {3}{CRLF} " & _
"AND A.PRODUCT = {4};"
Dim strSql As String
Dim FY As Integer
Dim AC As String
Dim DEPT As String
Dim PR As String
Dim BU As String
FY = -2000
AC = "<AccountID>"
DEPT = "<DeptID>"
PR = "<Product>"
BU = "<BusUnit>"
Debug.Print FormatStr(SQL_SELECT, SqlQuote(BU), FY, SqlQuote(AC), SqlQuote(DEPT), SqlQuote(PR))
End Sub
Public Function FormatStr(AString As String, ParamArray AValues() As Variant) As String
Dim Count As Long
Dim Result As String
Result = AString
For Count = 0 To UBound(AValues())
Result = Replace(Result, "{" & Count & "}", Nz(AValues(Count), "NULL"))
Next Count
Result = Replace(Result, "{CRLF}", vbCrLf)
FormatStr = Result
End Function
Public Function SqlQuote(AString As String, Optional ADelimiter As String = "'") As String
SqlQuote = ADelimiter & Replace(AString, ADelimiter, ADelimiter & ADelimiter) & ADelimiter
End Function
gives you
SELECT *
FROM PSFSP8.PS_LEDGER A, PSFSP8.PS_GL_ACCOUNT_TBL B
WHERE A.BUSINESS_UNIT = '<BusUnit>'
AND A.LEDGER = 'ACTUALS'
AND B.ACCOUNT = B_ED.ACCOUNT
AND A.FISCAL_YEAR = -2000
AND A.ACCOUNT = '<AccountID>'
AND A.DEPTID = '<DeptID>'
AND A.PRODUCT = '<Product>';
The {CRLF} is a pretty-pring/debug helper and can be removed.
The real fun begins if you can have Null values to insert.
You may take advantage of my CSql function which I have posted several times and also here.
/gustav
You may take advantage of my CSql function which I have posted several times and also here.
/gustav
I actually copied the WITH UR; part from someone else's code in the office, but I removed and still getting unexpected token. That ran before I declared variables. Also when I did not declare variables and used actual values (AND A.DEPTID LIKE '%'). The 3, 1 also works with actual values and variables, but I see what you mean and tried to replace 3 with DEPT and 1 with PRODUCT and still getting unexpected token so I removed ORDER BY completely for now. I believe these are the columns from PeopleSoft.
I tried the next suggestion -
"AND A.DEPTID = '" & DEPT & "'" &_ -------> I fixed formatting on other variables; get a different error "An unexpected toke ")" was found following ")". Expected tokens may include: "AND OR HAVING GROUP INTERSECT ORDER FETCH EXCEPT UNION FOR". SQLSTATE=42601(#-104)
I tried the next suggestion -
"AND A.DEPTID = " & chr(34) & DEPT & chr(34) & _
But I'm having an issue translating FISCAL_YEAR from PeopleSoft set as Num(4.0) -----> tried Num(4) and Nm(4), compile error. Tried " & Int(4) & FY & Int(4) & _ (Came and checked created query in access and show A.FISCAL_YEAR = 420174 instead of 2017.
So here is what I have and then what shows under SQL query design within Access. Error is still "An unexpected token "," was found following ".". Expected tokens may include: ",FROM INTO".
VBA:
SQL Query design results:
SELECT A.ACCOUNT AS ACCOUNT, B.DESCR AS DESCR, A.DEPTID AS DEPT, A.PRODUCT AS PRODUCT, A.FISCAL_YEAR AS YEAR, FROM PSFSP8.PS_LEDGER A, PSFSP8.PS_GL_ACCOUNT_TBL B WHERE ( A.BUSINESS_UNIT = ('WDBIL') AND A.LEDGER = ('ACTUALS') AND A.ACCOUNT = B.ACCOUNT AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PSFSP8.PS_GL_ACCOUNT_TBL B_ED WHERE B.SETID = B_ED.SETID AND B.ACCOUNT = B_ED.ACCOUNT AND B_ED.EFFDT <= CURRENT DATE) AND A.FISCAL_YEAR = 420174AND A.ACCOUNT =
116050 AND A.DEPTID = % AND A.PRODUCT = % AND A.ACCOUNTING_PERIOD IN (904,909,910,912,998,907))
Wondering now if it's the ('WDBIL') or ('ACTUALS') as the other strings (DEPTID, PRODUCT) do not have parenthesis or quote.
I tried the next suggestion -
"AND A.DEPTID = '" & DEPT & "'" &_ -------> I fixed formatting on other variables; get a different error "An unexpected toke ")" was found following ")". Expected tokens may include: "AND OR HAVING GROUP INTERSECT ORDER FETCH EXCEPT UNION FOR". SQLSTATE=42601(#-104)
I tried the next suggestion -
"AND A.DEPTID = " & chr(34) & DEPT & chr(34) & _
But I'm having an issue translating FISCAL_YEAR from PeopleSoft set as Num(4.0) -----> tried Num(4) and Nm(4), compile error. Tried " & Int(4) & FY & Int(4) & _ (Came and checked created query in access and show A.FISCAL_YEAR = 420174 instead of 2017.
So here is what I have and then what shows under SQL query design within Access. Error is still "An unexpected token "," was found following ".". Expected tokens may include: ",FROM INTO".
VBA:
strsql = "SELECT A.ACCOUNT AS ACCOUNT, B.DESCR AS DESCR, A.DEPTID AS DEPT, A.PRODUCT AS PRODUCT, A.FISCAL_YEAR AS YEAR, " & _
"FROM PSFSP8.PS_LEDGER A, PSFSP8.PS_GL_ACCOUNT_TBL B " & _
"WHERE ( A.BUSINESS_UNIT = ('WDBIL') " & _
"AND A.LEDGER = ('ACTUALS') " & _
"AND A.ACCOUNT = B.ACCOUNT " & _
"AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PSFSP8.PS_GL_ACCOUNT_TBL B_ED WHERE B.SETID = B_ED.SETID " & _
"AND B.ACCOUNT = B_ED.ACCOUNT " & _
"AND B_ED.EFFDT <= CURRENT DATE) " & _
"AND A.FISCAL_YEAR = " & Int(4) & FY & Int(4) & _
"AND A.ACCOUNT = " & Chr(10) & AC & Chr(10) & _
" AND A.DEPTID = " & Chr(10) & DEPT & Chr(10) & _
" AND A.PRODUCT = " & Chr(10) & PR & Chr(10) & _
" AND A.ACCOUNTING_PERIOD IN (904,909,910,912,998,907))) " & _
" GROUP BY A.ACCOUNT, B.DESCR, A.PRODUCT, A.DEPTID, A.FISCAL_YEAR;"
SQL Query design results:
SELECT A.ACCOUNT AS ACCOUNT, B.DESCR AS DESCR, A.DEPTID AS DEPT, A.PRODUCT AS PRODUCT, A.FISCAL_YEAR AS YEAR, FROM PSFSP8.PS_LEDGER A, PSFSP8.PS_GL_ACCOUNT_TBL B WHERE ( A.BUSINESS_UNIT = ('WDBIL') AND A.LEDGER = ('ACTUALS') AND A.ACCOUNT = B.ACCOUNT AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PSFSP8.PS_GL_ACCOUNT_TBL B_ED WHERE B.SETID = B_ED.SETID AND B.ACCOUNT = B_ED.ACCOUNT AND B_ED.EFFDT <= CURRENT DATE) AND A.FISCAL_YEAR = 420174AND A.ACCOUNT =
116050 AND A.DEPTID = % AND A.PRODUCT = % AND A.ACCOUNTING_PERIOD IN (904,909,910,912,998,907))
Wondering now if it's the ('WDBIL') or ('ACTUALS') as the other strings (DEPTID, PRODUCT) do not have parenthesis or quote.
Num 4.0 for year should mean 2017, for example
remove all except number:
A.FISCAL_YEAR = " & FY & _
If your value is stored as string, use quotes around this number (I hope FY is 4 digit year)
PS.
I see, it is short text, so:
A.FISCAL_YEAR = " & chr(34) & FY & chr(34) & _
remove all except number:
A.FISCAL_YEAR = " & FY & _
If your value is stored as string, use quotes around this number (I hope FY is 4 digit year)
PS.
I see, it is short text, so:
A.FISCAL_YEAR = " & chr(34) & FY & chr(34) & _
I am attempting both, Anders. When I do a passthrough the sql below, I get expected results. When I attempt to do against + variables, I am stuck.
As passthrough and working as expected (but with no variables):
As passthrough and working as expected (but with no variables):
ELSE 0
END)+
(CASE WHEN BUSINESS_UNIT = ('WDBIL') THEN
CASE WHEN 10 >= 7 THEN
CASE WHEN ACCOUNTING_PERIOD = 907 THEN
POSTED_TOTAL_AMT
ELSE 0
END
ELSE 0
END
ELSE 0
END)+
(CASE WHEN BUSINESS_UNIT = ('WDBIL') THEN
CASE WHEN 10 >= 10 THEN
CASE WHEN ACCOUNTING_PERIOD = 910 THEN
POSTED_TOTAL_AMT
ELSE 0
END
ELSE 0
END
ELSE 0
END)+
(CASE WHEN BUSINESS_UNIT = ('WDBIL') THEN
CASE WHEN 10 >= 12 THEN
CASE WHEN ACCOUNTING_PERIOD = 912 THEN
POSTED_TOTAL_AMT
ELSE 0
END
ELSE 0
END
ELSE 0
END),19, 2))
FROM PSFSP8.PS_LEDGER A, PSFSP8.PS_GL_ACCOUNT_TBL B
WHERE ( A.BUSINESS_UNIT = ('WDBIL')
AND A.LEDGER = ('ACTUALS')
AND A.ACCOUNT = B.ACCOUNT
AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PSFSP8.PS_GL_ACCOUNT_TBL B_ED WHERE B.SETID = B_ED.SETID
AND B.ACCOUNT = B_ED.ACCOUNT
AND B_ED.EFFDT <= CURRENT DATE)
AND B.SETID = ('WDUSA')
AND A.FISCAL_YEAR = 2017
AND A.ACCOUNT = ('116050')
AND A.DEPTID LIKE '%'
AND ( A.ACCOUNTING_PERIOD <= 10 OR A.ACCOUNTING_PERIOD IN (998,909,904,907,910,912)))
GROUP BY A.BUSINESS_UNIT, A.ACCOUNT, B.DESCR, A.PRODUCT, A.DEPTID, A.FISCAL_YEAR
ORDER BY 1, 2, 4 WITH UR;
I see. Now you can define or modify the sql of a passthrough before executing it. I think the root problem here is that there are differences in syntax between Access SQL and whichever database is used by peoplesoft. E.g. access does not have a case statement (You can use IIF statements, but that can get nasty quickly if nested).
So I think you should create your sql, then assign it to a passthrough query object (either pre-created, or created on the fly), and then execute that.
So I think you should create your sql, then assign it to a passthrough query object (either pre-created, or created on the fly), and then execute that.
Currentdb.QueryDefs("myPassThroughQuery").SQL=strSQL
Yes, the case statements are the main reason I was looking to make this happen because I've gotten it to run with hardcoded values for Accounting Period, Fiscal Year...not sure how to modify the variables? Do you mean through joining my user input table to the passthrough and transforming the case statements into IIF? I've been looking at this and testing for so many hours.
Let's say my passthrough is PSFSP8_PS_Ledger. I want to tie it to the values the user inputs for Account, FiscYear, AcctPeriod on the form that I have updating to table AcctPeriod_tbl. I can basically use those 3 and hard code Business Unit as WDBIL, Product as wildcard % and DeptID as %.
I've created a new query from the passthrough and called it PS_Ledger (so that I can look at the design of SQL because I can't when it's a table). I've put the criteria for LEDGER as Actuals. Now I have to figure out the CASE part and variables. I'm back to square one I feel like. The part that I'm just brain dead at this point is the variables. The query in SQL design view is now:
So I just want to be sure I am understanding, you are saying my best bet is to figure out the IIF statements ?
Let's say my passthrough is PSFSP8_PS_Ledger. I want to tie it to the values the user inputs for Account, FiscYear, AcctPeriod on the form that I have updating to table AcctPeriod_tbl. I can basically use those 3 and hard code Business Unit as WDBIL, Product as wildcard % and DeptID as %.
I've created a new query from the passthrough and called it PS_Ledger (so that I can look at the design of SQL because I can't when it's a table). I've put the criteria for LEDGER as Actuals. Now I have to figure out the CASE part and variables. I'm back to square one I feel like. The part that I'm just brain dead at this point is the variables. The query in SQL design view is now:
SELECT PSFSP8_PS_LEDGER.BUSINESS_UNIT, PSFSP8_PS_LEDGER.LEDGER, PSFSP8_PS_LEDGER.ACCOUNT, PSFSP8_PS_LEDGER.DEPTID, PSFSP8_PS_LEDGER.PRODUCT, PSFSP8_PS_LEDGER.FISCAL_YEAR, PSFSP8_PS_LEDGER.ACCOUNTING_PERIOD, PSFSP8_PS_LEDGER.POSTED_TOTAL_AMT
FROM PSFSP8_PS_LEDGER
WHERE (((PSFSP8_PS_LEDGER.LEDGER)="ACTUALS"));
So I just want to be sure I am understanding, you are saying my best bet is to figure out the IIF statements ?
I did have this portion below originally when I was trying to figure out how to run it I copied this from another DB and changed it to meet what I'm trying to do. When would I use this method - when I do or do not have a passthrough...when I do not, correct?
On Error Resume Next
DoCmd.DeleteObject acQuery, "PS_Ledger"
On Error GoTo 0
Set myQueryDef = dbs.CreateQueryDef("PS_Led
myQueryDef.Connect = "ODBC;DSN=PSFSP8;UID=XXXX;
myQueryDef.ODBCTimeout = 6000
myQueryDef.SQL = strsql
myQueryDef.Close
On Error Resume Next
DoCmd.DeleteObject acQuery, "PS_Ledger"
On Error GoTo 0
Set myQueryDef = dbs.CreateQueryDef("PS_Led
myQueryDef.Connect = "ODBC;DSN=PSFSP8;UID=XXXX;
myQueryDef.ODBCTimeout = 6000
myQueryDef.SQL = strsql
myQueryDef.Close
I think I would change the Set myQueryDef = dbs.CreateQueryDef("PS_Led
This is quickly getting complicated :=), as I don't know how familiar you are with the different terms.
When access executes a LOCAL query, even if it is against linked external tables, the access database engine (ACE if 2007 or newer) will try to evaluate the best way to execute that query. I would say that overall Access (or ACE) is actually pretty good at this. However sometimes, if the query is complex (or poorly written) ACE is forced to download the entire table and do joins, or functional expressions local. This can be painfully slow. Sometimes ACE will be smart enough to send parts of the query directly to the server, if it believes its a good idea. Now when using ODBC this also means that the SQL you typed is being read, and interpreted by ACE, then translated by the ODBC driver into a syntax that the Server can understand. Sometimes this can cause issues, but usually its quite painless and transparant for users.
Now the other option is to specify a passthrough. In this case, the sql string is passed directly, and unaltered to the server for processing there.
This also means the passthrough needs to use the SQL syntax of the server, completely ignoring the syntax of Access.
The downside of a passthrough is that they are not updateable. So if that is a requirement, then yes, perhaps an if statement is the way to go.
If you are mainly doing reporting, then a passthrough can be an excellent choice.
The code shown is post is one way of defining a passthrough in code.
So if you need variables for that passthrough, you alter the sql.
Lets take a simple example, we want to select from the customers table, everyone with firstname Joe.
Now you can build the SQL string any way you like, to include your parameters.
When access executes a LOCAL query, even if it is against linked external tables, the access database engine (ACE if 2007 or newer) will try to evaluate the best way to execute that query. I would say that overall Access (or ACE) is actually pretty good at this. However sometimes, if the query is complex (or poorly written) ACE is forced to download the entire table and do joins, or functional expressions local. This can be painfully slow. Sometimes ACE will be smart enough to send parts of the query directly to the server, if it believes its a good idea. Now when using ODBC this also means that the SQL you typed is being read, and interpreted by ACE, then translated by the ODBC driver into a syntax that the Server can understand. Sometimes this can cause issues, but usually its quite painless and transparant for users.
Now the other option is to specify a passthrough. In this case, the sql string is passed directly, and unaltered to the server for processing there.
This also means the passthrough needs to use the SQL syntax of the server, completely ignoring the syntax of Access.
The downside of a passthrough is that they are not updateable. So if that is a requirement, then yes, perhaps an if statement is the way to go.
If you are mainly doing reporting, then a passthrough can be an excellent choice.
The code shown is post is one way of defining a passthrough in code.
So if you need variables for that passthrough, you alter the sql.
Lets take a simple example, we want to select from the customers table, everyone with firstname Joe.
Dim SQL as string
'Note that I use single quotes, like SQL server, unlike access where I might use double quotes
Dim SQL as string
SQL="Select * from dbo.tbl_customers where firstname='joe'"
dim myQueryDef as dao.QueryDef
Set myQueryDef = dbs.CreateQueryDef("") 'Pass in a empty string to create a query and not save it. Saves the hazle of deleting it again.
myQueryDef.Connect = "ODBC;DSN=PSFSP8;UID=XXXX;PWD=XXXXX;MODE=SHARE;DBALIAS=PSFSP8;IGNOREWARNINGS=1;DISABLEUNICODE=1;DISABLEKEYSETCURSOR=1;"
myQueryDef.ODBCTimeout = 6000
myQueryDef.SQL = SQL
dim myRecordset as dao.Recordset
Set myRecordset=myQueryDef.OpenRecordset(dbOpenSnapshot,dbSQLPassThrough)
Now you can build the SQL string any way you like, to include your parameters.
Ah, ok! I was starting to think, "why the heck did I go down this rabbit hole again??" when I started re-creating my regular passthrough. But now that you have reinforced - it's because of the variables. They will change each month (just accounting period, and then next year - fiscal year). I'm going to digest this and work on it this weekend. Thanks - I will update when I've made progress!! I appreciate all of your help guys, have a great weekend and talk next week.
I forgot to ask this question - for the wildcard % for Product and Dept, should I use equal sign or "LIKE" in my strSql ?
I've made progress and now have some my variables working. The user will just have limited use for this db and pulling just these two accounts. I My variables for Fiscal Year and Accounting Period are passing successfully, however, I'm stuck on the second set of CASE statements that will create a final column that sums different accounting balances.
I believe this is a new question, so I will post that separately and mark this one complete. Thank you for all of your help, here is what finally worked (minus case statements):
Private Sub Command43_Click()
Dim dbs As DAO.Database
Dim myQueryDef As DAO.QueryDef
Dim strsql As String
Set dbs = CurrentDb()
I believe this is a new question, so I will post that separately and mark this one complete. Thank you for all of your help, here is what finally worked (minus case statements):
Private Sub Command43_Click()
Dim dbs As DAO.Database
Dim myQueryDef As DAO.QueryDef
Dim strsql As String
Set dbs = CurrentDb()
Private Sub Command43_Click()
Dim dbs As DAO.Database
Dim myQueryDef As DAO.QueryDef
Dim strsql As String
Set dbs = CurrentDb()
strsql = "SELECT A.ACCOUNT, B.DESCR, A.DEPTID, A.PRODUCT, A.FISCAL_YEAR,SUM( DECIMAL( CASE WHEN ACCOUNTING_PERIOD = " & Me.AcctPer & " THEN POSTED_TOTAL_AMT ELSE 0 END,19, 2)), " & _
"FROM PSFSP8.PS_LEDGER A, PSFSP8.PS_GL_ACCOUNT_TBL B " & _
"WHERE (A.BUSINESS_UNIT = 'WDBIL' AND A.LEDGER = 'ACTUALS' AND A.ACCOUNT IN ('116050','116200') AND B.SETID = 'WDUSA' AND A.ACCOUNT = B.ACCOUNT) AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PSFSP8.PS_GL_ACCOUNT_TBL B_ED WHERE B.SETID = B_ED.SETID AND B.ACCOUNT = B_ED.ACCOUNT AND B_ED.EFFDT <= CURRENT DATE) " & _
"AND A.FISCAL_YEAR = " & Me.FiscYear & " " & _
"AND A.ACCOUNTING_PERIOD = " & Me.AcctPer & " " & _
"OR A.ACCOUNTING_PERIOD IN (904,909,910,912,998,907) " & _
"GROUP BY A.ACCOUNT, B.DESCR, A.DEPTID, A.PRODUCT, A.FISCAL_YEAR " & _
"ORDER BY DEPTID, PRODUCT WITH UR;"
Premium Content
You need an Expert Office subscription to comment.Start Free Trial