Databases
StackOverflow2013
Postsdbo
POExcel VBA query to access is failing
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POExcel VBA query to access is failing
    text
    copied!<p>I am trying to query an access file from excel using VBA and it is giving an error on the line <code>rs.Open queryStatement, conn, adOpenStatic, adLockOptimistic</code>. The error is "Run-Time error '-2147217904 (80040e10)': No Value given for one or more required parameters."</p> <pre><code>Function queryAccess() 'inputs: filterID, desired output Dim toSheet As Worksheet Set toSheet = ThisWorkbook.Sheets("Sheet3") Dim filterID As String filterID = "CH0002" Dim conn As ADODB.Connection Dim rs As ADODB.recordSet Dim connStr As String Dim queryStatement As String Dim cmd As ADODB.Command connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\rtpwfil03\etu2\cat\projects\asbestos\STREAMS TO101\Testing\TO101 Testing Data.mdb;Persist Security Info=False;" 'conn.Provider=""Microsoft.Jet.OLEDB.4.0" Set conn = New ADODB.Connection Set rs = New ADODB.recordSet Set cmd = New ADODB.Command queryStatement = "SELECT Filters.NominalLoading FROM Filters WHERE Filters.FilterID=" &amp; filterID &amp; ";" conn.Open connStr Debug.Print connStr Debug.Print queryStatement rs.Open queryStatement, conn, adOpenStatic, adLockOptimistic toSheet.Range(toSheet.Cells(1, 1)).CopyFromRecordset rs conn.Close rs.Close End Function </code></pre> <p>when I do a debug.print on my connStr and queryStatement I get this, respectively: <code>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\rtpwfil03\etu2\cat\projects\asbestos\STREAMS TO101\Testing\TO101 Testing Data.mdb;Persist Security Info=False;</code></p> <p><code>SELECT Filters.NominalLoading FROM Filters WHERE Filters.FilterID=CH0002;</code></p> <p>I got the connection string from <a href="http://www.connectionstrings.com/access-2007" rel="nofollow">http://www.connectionstrings.com/access-2007</a> "standard security"</p> <p>Any ideas on how to fix this error?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload