I've searched MSDN, StackOverflow, SQLServer Central and too many sites to mention. I’ve been trying for a couple of days to find a way to execute parameter queries in MS Access using dynamic SQL SELECT statements in Excel VBA code. The system I’m using works extremely well with SQL Server TVFs, but I have to convert it to Access due to losing server support. The VBA code starts by looping through an ‘input’ sheet in Excel picking up parameter values and function/query names that is used to build dynamic SQL SELECT statements. Here is the code that builds the connection and calls the Access Query for just one of the queries (there are 20) that requires only 1 input paramter:
Dim strSQL
' set up our connection
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\PathToDB Tables 2013-12-13.accdb;"
Set conn = CreateObject("ADODB.Connection")
conn.Open strConnection
' open the view and create the report
Set rs = CreateObject("ADODB.recordset")
Application.ODBCTimeout = 0
conn.CommandTimeout = 0
strSQL = "select * FROM "
strSQL = strSQL & strFunction
strSQL = strSQL & " (" & strParameters & ");"
'strSQL = strSQL & strOrderBy
rs.Open strSQL, conn
The value in strSQL at this point is SELECT * FROM Report_1_2_StaffAdds (#12/31/2013#);
The error occurs in the line rs.Open strSQL, conn
With the error message ‘Syntax error in FROM clause.'
Here is the MS Access Query: (Query name is Report_1_2_StaffAdds)
PARAMETERS [previous_month] DateTime;
SELECT [1_2_StaffAddsPart1].Unit, [1_2_StaffAddsPart1].Role, [1_2_StaffAddsPart1].Start_Date, [1_2_StaffAddsPart2].First_Worked, [1_2_StaffAddsPart2].Last_Worked, [1_2_StaffAddsPart1].Emp_Name, [1_2_StaffAddsPart1].Emp_Id, [1_2_StaffAddsPart2].Hours_to_Date
FROM 1_2_StaffAddsPart1 INNER JOIN 1_2_StaffAddsPart2 ON [1_2_StaffAddsPart1].Emp_Id = [1_2_StaffAddsPart2].Emp_Id;
Any help will be much appreciated. I believe if I can get an answer to this, I can revise it to include up to 3 input parameters, depending on which Access query is being executed at a given time.
Best Answer
It looks to me like you're attempting to include the parameter value in the string which contains your
SELECT
statement. However, I'm not sure whether I understand what you're trying to do, so I'll offer you a simple tested parameter query which opens a recordset from anADODB.Command
object.This is the Immediate window output from the code below:
Code: