SQL Query return single result without using listobject

PDMeat

New Member
Joined
Nov 8, 2010
Messages
3
Hi all,

Hoping someone can assist with a sample to return a single result from SQL without using a listobject.

e.g. "Select Count (*) from Table1" should give me a single number like 1234. I don't want a listobject, I just want the number to drop into a single cell in Excel.

The objective is perhaps also stated differently as to get rid of the list object header/filter row and just return the data. Unfortunately all of the samples I've come across all use listobject which has the header/filter row.

Thanks!
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Hi all,

Hoping someone can assist with a sample to return a single result from SQL without using a listobject.

e.g. "Select Count (*) from Table1" should give me a single number like 1234. I don't want a listobject, I just want the number to drop into a single cell in Excel.

The objective is perhaps also stated differently as to get rid of the list object header/filter row and just return the data. Unfortunately all of the samples I've come across all use listobject which has the header/filter row.

Thanks!

Maybe something similar to this? I use a variation of this to return SQL results to form controls.

Note, this requires a reference to "Microsoft ADO 3.6 Object Library" (or whatever version of ADO your Excel VBA has).

Code:
Sub QuerySQL()
Dim ws As Workspace, db As DAO.Database
dim sqlstr as string

On Error GoTo errhandler:

Call OpenODBC(ws, db, "AC")

'Replace "TABLE1", "HEADER", "SEARCH_TERM" (leave the apostrophes around the search term) with your parameters
sqlstr = "Select * from db2.TABLE1 where HEADER = 'SEARCH_TERM' FETCH FIRST 1 ROWS ONLY"

Set rs = db.OpenRecordset(sqlstr, dbOpenSnapshot)

'Replace "HEADER" with the recordset you want returned (leave the quote marks though) & change the cell to whatever range you want it to go to.
Cells(1, 1) = rs("HEADER")

GoTo ending

errhandler:
If Err.Number = 1004 Then
    GoTo ending
Else
'add desired error handler procedures
End If
  
ending:

Set rs = Nothing

End Sub

Sub OpenODBC(ws As Workspace, db As DAO.Database, dsn As String)
Dim dsnStr As String

On Error GoTo ErrorHandling

'Replace "USER_ID", "PASSWORD" with your credentials
Set ws = CreateWorkspace("ODBCWorkspace", "", "", dbUseODBC)
Workspaces.Append ws
ws.LoginTimeout = 300
dsnStr = "ODBC;DSN=" & dsn & ";UID=USER_ID;PWD=PASSWORD"
Set db = ws.OpenConnection(dsn, dbDriverNoPrompt, False, dsnStr)
db.QueryTimeout = 900
GoTo Proc_Exit
  
ErrorHandling:
'add desired error handler procedures
Resume Next
  
Proc_Exit:
  
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top