Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <blockquote> <p>Does that mean i can not have select query in my SP?</p> </blockquote> <p>No, the error message is telling you that Access thinks your query is a SELECT query (or some type of query that returns records) and you are trying to <code>.Execute</code> it. Access doesn't like that because it thinks you should be assigning the returned records to a <code>Recordset</code>.</p> <p>To create a pass-through query (PTQ) in DAO you must </p> <ul> <li>set the <code>.Connect</code> property of the QueryDef to at least <code>ODBC;</code>, and</li> <li>set the <code>.ReturnsRecords</code> property to <code>False</code> if it does not return a rowset</li> </ul> <p>Here is the bare-bones code to create a PTQ:</p> <pre><code>Sub CreatePTQ() Dim cdb As DAO.Database, qdf As DAO.QueryDef Set cdb = CurrentDb Set qdf = cdb.CreateQueryDef("ptq") qdf.Connect = "ODBC;" qdf.ReturnsRecords = False qdf.SQL = "EXEC foo" qdf.Close Set qdf = Nothing Set cdb = Nothing End Sub </code></pre> <p>Also, in your code you are replacing the placeholders with the <em>names</em> of the form controls, e.g., ...</p> <pre><code>strSQL = Replace(c_strSQL, "{P1}", "Forms!CopyVendor!CurCo") </code></pre> <p>...when you should be replacing those placeholders with the controls' <em>values</em>...</p> <pre><code>strSQL = Replace(c_strSQL, "{P1}", Forms!CopyVendor!CurCo) </code></pre> <p>...and remember that if any of the parameters are strings then you'll need to put quotes around them in the <code>EXEC</code> statement.</p>
    singulars
    1. This table or related slice is empty.
    1. POhow can i use access pass through query to access sql sp
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USGord Thompson
    1. USGord Thompson
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POhow can i use access pass through query to access sql sp
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COThanks, i will try it, as you know i am so new to access
      singulars
      1. PO
      1. USLan Cui
    2. COI get the refernce from the following link http://www.dbforums.com/microsoft-access/1661807-sql-pass-through-query-combo-box-paramater.html. Would you help me to look at it?
      singulars
      1. PO
      1. USLan Cui
    3. CO@user1723572 Have you already created the [QueryCopyVendor] query as a pass-through query (icon in Access looks like a globe), with the appropriate `.Connect` string, and with the `EXEC sp_xCopy ...` command using actual parameters so you can double-click the query in Access and verify that it actually works? Try that first, and then work on executing it from your VBA code.
      singulars
      1. PO
      1. USGord Thompson
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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