title: How to call MS Access Parameter queries using VBA dynamic SQL SELECT statements
body: <p>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:</p>
  
  <pre><code>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 &amp; strFunction
  strSQL = strSQL &amp; " (" &amp; strParameters &amp; ");"
  'strSQL = strSQL &amp; strOrderBy
  rs.Open strSQL, conn
  </code></pre>
  
  <p>The value in strSQL at this point is <code>SELECT * FROM Report_1_2_StaffAdds (#12/31/2013#);</code></p>
  
  <p>The error occurs in the line   rs.Open strSQL, conn
  With the error message  ‘Syntax error in FROM clause.'</p>
  
  <p>Here is the MS Access Query: (Query name is Report_1_2_StaffAdds)</p>
  
  <p><code>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;</code></p>
  
  <p>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.</p>