Retrieving Count of rows returned from MS Access Query

[Don]

Hi Folks,

probably a pretty routine question here ...

I am trying to determine the number of rows that would be returned prior to executing an Access query .

for example:

If the user selects more than 65,536 possible rows, I want to warn them and then either proceed with a subset, or give them the option of cancelling their query and specifying more selective criteria.

It is a dynamic query, which lets the users click on multiple list boxes to select the "Where" component of the excel query, and I want to enable them to select right up to the maximum number of rows.

Ultimately, it will be ODBC'ing to a Sybase database, but that is likely irrelevant.

Here is a sample of the query ...


With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:\Documents and Settings\Don McGee\My Documents\countries and cities.mdb;DefaultDir=C:\Document" _
), Array( _
"s and Settings\Don McGee\My Documents;DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
)), Destination:=Range("N1"))
.CommandText = strQuery
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With


Thanks in advance

Don

 

[Tom Ogilvy]

You probably need to use ADO instead of a query table:

See Mr. Erlandsen's site:
http://www.erlandsendata.no/english/index.php?t=envbadac

some other sources/references:

http://support.microsoft.com/default.aspx?scid=kb;en-us;244761&Product=xlw
XL2000: How to Use ADO to Return Data to a ListBox or ComboBox

Andy Wiggins:
This might be a help for getting data to and from Excel and Access: It
includes examples of using variables in SQL queries.
http://www.bygsoftware.com/examples/sql.html

Or you can get there from the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

--
Regards,
Tom Ogilvy

Hi Folks,

probably a pretty routine question here ...

I am trying to determine the number of rows that would be returned prior to executing an Access query .

for example:

If the user selects more than 65,536 possible rows, I want to warn them

and then either proceed with a subset, or give them the option of cancelling
their query and specifying more selective criteria.

It is a dynamic query, which lets the users click on multiple list boxes

to select the "Where" component of the excel query, and I want to enable
them to select right up to the maximum number of rows.

Ultimately, it will be ODBC'ing to a Sybase database, but that is likely irrelevant.

Here is a sample of the query ...


With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:\Documents and Settings\Don

McGee\My Documents\countries and cities.mdb;DefaultDir=C:\Document" _

), Array( _
"s and Settings\Don McGee\My Documents;DriverId=281;FIL=MS

Access;MaxBufferSize=2048;PageTimeout=5;" _

 

[Don]

Tom,

Thanks very much for the suggestion; I am a little green when using ADO, and I am trying to implement your solution; I have referred to the example you provided, but the references made to the ADO connections are a little ambiguous to me ...

Given my database and path are outlined below in the VBA code, how does this translate to the ADO code (highlighted)

Thanks in advance...

my code:

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:\Documents and Settings\Don McGee\My Documents\countries and cities.mdb;DefaultDir=C:\Document" _
), Array( _
"s and Settings\Don McGee\My Documents;DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
)), Destination:=Range("J1"))
.CommandText = strQuery
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With


ADO code: from resouce link you provided...

Public Sub PopulateControl()

Dim cnn1 As ADODB.Connection
Dim rstEmployees As ADODB.Recordset
Dim strCnn As String

' Open connection. Replace the word servername next to Data Source
' with the actual name of the SQL Server.
strCnn = "Provider=sqloledb; Data Source=servername;Initial Catalog=pubs;" & _
"User Id=sa;Password=; "
Set cnn1 = New ADODB.Connection
cnn1.Open strCnn

where my database name is "Cities_and_Countries"; there is no password ...

Thanks again, you are a great resource on this board

Don