Databases
StackOverflow2013
Postsdbo
POCalling SQL Query with VBA Variable Function from Excel
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCalling SQL Query with VBA Variable Function from Excel
    primarykey
    data
    text
    <p>I'm a bit of newbie when it comes to these things, so apologies if this is a stupid question.</p> <p>I need to run an SQL query from a piece of VBA. The query is a little odd, because it contains a VBA variable in its function. Otherwise everything is pretty straight forward. The VBA should call the query and then insert it into a client excel document.</p> <p>Every time I run the query within Access everything is fine, the function returns the correct value and filters down the columns. Every time I run it from VBA in Excel it says <code>"Run-Time Error "3085"</code>: Undefined Function 'CutOff' in expression. </p> <p>I've look for info and have found old sites saying that Access 2003 sometimes has an issue doing this sort of thing, but I'm running 2010 (I think). Just hoping the problem is solve-able and greatly appreciate any advice. </p> <p>The query is as follows : </p> <pre><code> SELECT [&lt;TableName&gt;].ID...* FROM [&lt;TableName&gt;] WHERE ((([&lt;TableName&gt;].ID)&gt;CutOff())) ORDER BY [&lt;TableName&gt;]].ID; Public Function Cutoff() Dim WB1 As Excel.Workbook, WS1 As Excel.Worksheet Dim y As Long Set WB1 = Workbooks.Open("C:\filepath.z.xlsm") Set WS1 = WB1.Sheets("Sheet2") y = WS1.Range("A1").End(xlDown).Offset(0, 0).Value 'Debug.Print y Cutoff = y 'Debug.Print Cutoff End Function </code></pre> <p>The VBA that runs it is operated from Excel. I have tried the following: </p> <pre><code>Sub Export2() Dim db2 As Database Dim rs2 As DAO.Recordset, i As Long, sFormat As String Dim WB2 As Excel.Workbook, WS2 As Excel.Worksheet Set WB2 = Workbooks.Open("C:\FilePath.z.xlsm") Set WS2 = WB.Sheets("Sheet2") Set db2 = OpenDatabase("C:\FilePath.x.mdb") Set qd2 = db2.QueryDefs("ExportCount") Set rs2 = qd2.OpenRecordset() If rs2.EOF Then GoTo EndLoop End If WS2.Range("a1").End(xlsDown).Offset(1, 0).CopyFromRecordset rs2 WS2.Cells.EntireColumn.AutoFit: WS2.Cells.EntireRow.AutoFit EndLoop: Set WB = Nothing Set WS2 = Nothing Set db2 = Nothing Set qd2 = Nothing Set rs2 = Nothing End Sub </code></pre> <p><strong>EDIT:</strong></p> <p>Have also tried:</p> <pre><code>Sub SQLquery1() Dim WB1 As Excel.Workbook, WS1 As Excel.Worksheet Dim wt As DAO.Database Dim we As DAO.Recordset Dim wd As DAO.QueryDef Set WB1 = Workbooks.Open("C:\x.xlsm") Set WS1 = WB1.Sheets("Sheet2") mySQLVariable = WS1.Range("A1").End(xlDown).Offset(0, 0).Value 'Debug.Print mySQLVariable Set wt = OpenDatabase("C:\z.mdb") Set wd = wt.QueryDefs("ExportCount") Set we = wd.OpenRecordset("h") WS2.Range("a1").End(xlsDown).Offset(1, 0).CopyFromRecordset wd WS2.Cells.EntireColumn.AutoFit: WS2.Cells.EntireRow.AutoFit Set WB1 = Nothing Set WS1 = Nothing Set wt = Nothing Set we = Nothing Set wd = Nothing End Sub </code></pre> <p><strong>EDIT2</strong></p> <pre><code>Sub CreateQueryDef() Dim WB1 As Excel.Workbook, WS1 As Excel.Worksheet Dim dbPP As Database Dim qdfTemp As QueryDef Dim Counter As DAO.Recordset Dim mySQLVariable As String Dim rs5 As DAO.Recordset Set dbPP = OpenDatabase("C:\filepath\z.mdb") Set Counter = dbPP.OpenRecordset("j") Set WB1 = Workbooks.Open("C:\filepath\x.xlsm") Set WS1 = WB1.Sheets("Sheet2") mySQLVariable = WS1.Range("A1").End(xlDown).Offset(0, 0).Value 'Debug.Print mySQLVariable With dbPP Set qdfTemp = dbPP.CreateQueryDef("NewQueryDef", "SELECT * FROM [j]") 'WHERE ((j.[ID])=&gt;(mySQLVariable)))") I can't get the syntax of these lines right - they are supposed to all be on the same line Set rs5 = qdfTemp.OpenRecordset() ' maybe Set rs5 = qdfTemp.OpenRecordset("NewQueryDef")? End With WS1.Range("a1").End(xlsDown).Offset(1, 0).CopyFromRecordset rs5 WS1.Cells.EntireColumn.AutoFit: WS2.Cells.EntireRow.AutoFit dbPP.QueryDefs.Delete "NewQueryDef" End Sub </code></pre> <p>Or</p> <pre><code> Sub CreateQueryDef() Dim dbPP As Database Dim qdfTemp As QueryDef Dim Counter As DAO.Recordset Dim mySQLVariable As String Dim rs5 As DAO.Recordset Set dbPP = OpenDatabase("C:\filepath\z.mdb") Set Counter = dbPP.OpenRecordset("j") mySQLVariable = CutOff 'Debug.Print mySQLVariable With dbPP Set qdfTemp = dbPP.CreateQueryDef("NewQueryDef", "SELECT * FROM [j] WHERE ((j.[ID])=&gt;(mySQLVariable)))") Set rs5 = qdfTemp.OpenRecordset("NewQueryDef") End With WS1.Range("A1").End(xlsDown).Offset(1, 0).CopyFromRecordset rs5 WS1.Cells.EntireColumn.AutoFit: WS2.Cells.EntireRow.AutoFit dbPP.QueryDefs.Delete "NewQueryDef" dbPP.Close Set dbPP = Nothing Set qdfTemp = Nothing Set Counter = Nothing Set mySQLVariable = Nothing Set rs5 = Nothing End Sub Public Function Cutoff() Dim WB1 As Excel.Workbook, WS1 As Excel.Worksheet Dim y As Long Set WB1 = Workbooks.Open("C:\filepath.z.xlsm") Set WS1 = WB1.Sheets("Sheet2") y = WS1.Range("A1").End(xlDown).Offset(0, 0).Value 'Debug.Print y Cutoff = y 'Debug.Print Cutoff End Function </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDhanish Jose
    1. USOrphid
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COdoes the cutoff function exist in both the database and the excel spreadsheet, just the spreadsheet (if so how does the MDB find it?), or just the database? if in excel, you'll need to modify the query in access to accept the parameter for exportcount which contains the value of the cutoff.
      singulars
      1. POCalling SQL Query with VBA Variable Function from Excel
      1. USxQbert
    2. COThanks for this. However, I put the function in both excel and access just in case that was the problem. Originally I set it up in access and, as I say, it derives the correct value when run in access and the query results display correctly. The problem comes when the query is called from excel vba.
      singulars
      1. POCalling SQL Query with VBA Variable Function from Excel
      1. USOrphid
    3. COMaybe I need to change mySQLVariable in the SQL query (in EDIT 2) to a call to the CutOff function in excel? Or define mySQLVariable by CutOff before initiating the query?
      singulars
      1. POCalling SQL Query with VBA Variable Function from Excel
      1. USOrphid
 

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