StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This is how I would do it... just tested it and it works fine with my UDF:</p> <p>One thing - are you required to <strong>not</strong> use New Access.Application?</p> <pre><code>Sub GetMyDataWithUDF() Dim oApp As Access.Application Dim qd As QueryDef sFileName = "C:\Users\AUser\Desktop\adatabase.mdb" Set oApp = New Access.Application oApp.OpenCurrentDatabase (sFileName) Set qd = oApp.CurrentDb.QueryDefs("Query1") If oApp.DCount("*", "MSysObjects", "Name='dataTableResults'") > 0 Then _ oApp.CurrentDb.TableDefs.Delete "dataTableResults" qd.Parameters("avalue") = "4" qd.Execute oApp.Quit Set oApp = Nothing Dim oRS As ADODB.Recordset sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFileName & ";User Id=admin;Password=;" Set oRS = New ADODB.Recordset oRS.Open "SELECT * FROM dataTableResults", sConn Sheet1.Cells.Clear Sheet1.Range("A1").CopyFromRecordset oRS oRS.Close Set oRS = Nothing End Sub </code></pre> <p><strong>Note</strong> that I made my underlying query a SELECT ... INTO query that creates a table called 'dataTableResults'</p> <p>This is my query (QueryDef) in Access:</p> <pre><code>SELECT dataTable.Field1, dataTable.Field2 INTO dataTableResults FROM dataTable WHERE mysqr(dataTable.Field1)=[avalue]; </code></pre> <p>My MS-Access DB has a function called "mysqr", which gets used in the SQL above.</p> <pre><code>Function mysqr(Num) mysqr = Num * Num End Function </code></pre> <p>The table "dataTable" I'm querying against is just a list of numbers, so if my parameter "avalue" is "16", then I get the row "4" back. If I enter "4" (as in my code), I get "2" back.</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