Retrieving Count of rows returned from MS Access Query

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

0
Don4849 (203)
6/27/2004 1:42:02 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
564 Views

Similar Articles

[PageSpeed] 44

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


"Don" <Don@discussions.microsoft.com> wrote in message
news:F42FE263-F677-4197-9A34-83C67DC36943@microsoft.com...
> 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
>


0
twogilvy (1078)
6/27/2004 3:29:08 PM
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

    

"Tom Ogilvy" wrote:

> 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
> 
> 
> "Don" <Don@discussions.microsoft.com> wrote in message
> news:F42FE263-F677-4197-9A34-83C67DC36943@microsoft.com...
> > 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
> >
> 
> 
> 
0
Don4849 (203)
6/27/2004 6:54:02 PM
Reply:

Similar Artilces:

number rounding in MS Excel
Hi, This may have been answered many times. In MS EXCEL is there a way that it calculates all numbers to 2 decimal digit accuracy instead of the 15 digit default? Thanks in advance for the answer. Hi You can set your decimal places in your cells as 2 and then check Precision As Displayed on the Tools / Options / Calculation page. Be careful though, it means what it says!! -- Andy. "mahusain" <abidh@bdnet.net> wrote in message news:ab59c6f6.0404200315.5196e8aa@posting.google.com... > Hi, > > This may have been answered many times. > > In MS EXCEL is ther...

Business Portal Error-SQL server does not exist or access denied
Hi, We are running business portal 4.0 for one of our customer. It was running correctly, however, they have changed the SQL server port (previously it was set as default 1433). After that the business portal becomes very slow and while creating a new request (purchase requisition) if we open the item pop up; it is showing exception "SQL Server does not exist or access denied...." Can any body tell me how can I provide the new port number to business portal connection to the database. Thanks and Regards, Waliullah, Thanks for using the newsgroups. I have a...

What's happened to the findfirst function in Access 2010?
I have a working Access 2007 Application which is now failing miserably in 2010. It hinges on location the first available working document in a table runing the following VBA code: With Me.Recordset .FindFirst "([fld1] + [fld2]) = 0" If .NoMatch Then .FindLast "([fdl1] + [fld2]) <> 0" Exit Sub End If .... FURTHER PROCESSING ... What must I do to correct this? Go through a record by record search? End With In 2007, this works correctly, stopping at the appropriate record (approc. rec 1385 in the recordset...

visual basic
Hi, I trying to retrieve values from a table to calculate the 14days average value of a stock closing price. However, i encounter some problem as stated beside the code as follows: Function DaysAvgs() 'Calculate the average value of a given value. Dim db As DAO.Database Dim rst As DAO.Recordset Dim varBookmark As Variant Dim numAve, numDaysAvg As Double Dim intA, intB, lngCount As Integer Set db = CurrentDb 'Open Table Set rst = db.OpenRecordset("SGX Individual Historical", dbOpenTable) rst.MoveFirst Do While Not rst.EOF intA = 1 intB = 0 varBookmark = rst.Bookmark n...

open two different Access reports
Hi, I was wondering if i can get some help here. I have two different reports that i want to open when a user clicks a button to view the reports for printing. Is there any way of popping them up at the same time in VBA? Thank you in advance Associates wrote: >Hi, > >I was wondering if i can get some help here. I have two different reports >that i want to open when a user clicks a button to view the reports for >printing. Is there any way of popping them up at the same time in VBA? > >Thank you in advance Yes. Call the DoCmd.OpenReport command twic...

Summing Alternate Rows
We have spreadsheets with many rows and need to sum alternate rows. Is there a way to do this without entering a formula using the number of each row? -- KarenQ ------------------------------------------------------------------------ KarenQ's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19608 View this thread: http://www.excelforum.com/showthread.php?threadid=395115 Try this: =SUMPRODUCT(--(MOD(ROW($A$1:$A$18),2)=1),$A$1:$A$18) That formula sums odd-row values in the range A1:A18. To sum even-row values the formula is: =SUMPRODUCT(--(MOD(ROW($A$1:$A$18...

Access 2007 and Vista code problem
I am developing a database on a XP SP2 machine. On a form I have created some buttons with event procedures that run 2 queries and open a form. I sent the db to someone running Access 2007 on a Vista machine and nothing happens when she clicks the buttons and there are no messages. I know nothing about Vista so thanks in advance for any help. Tim Might be a reference problem, have them open the open a code window and click tools references. See what says missing and tell them what should be there, Add it and it should work. Duff "Tim Reid" <TimReid@discussions.micr...

Web query timeout setting.
Dear Group, I fill an Excel table using data that I take from an Internet site. Unfortunately, this site is very slow and so I often get a "query did not provide any data" error message. How can I increment the default web query timeout limit? Thanks in advance, Enrico. ...

Vlookup returns "0"
I am using the following formula =VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE and it is returning a zero if there is no data found in that cell. If there is no data found I would like it to display nothing. How can I do this? Carolyn, There are a couple ways so do this. One is to test it, which makes for doing the VLOOKUP function twice. 1) =if(VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE)=0, "" , VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE)) This gets a little messy. Better yet, and simpler is to hide the original cell (column, whatever), an...

IsOutLookClient() returns wrong value
IsOutLookClient() returns wrong value when both web client of crm and outlook client are running on the same workstation It looks like the same cookie(used for determining what client is running) is used by the sessions of each client. Look for "LightClient" in IsOutlookWorkstationClient() in global.js Oeps...I seem to have made a wrong assumption... Between the to clients IsOutlookClient() seems to work ok... But in outlook client the IsOutlookClient() function gives false for me...after I have opened a page from the Microsoft Crm folder structure... On another workstation it...

Outlook 2007 Search always returns no matches
Any search I do in Outlook 2007's new search box returns no matches. I watched the Indexing Status as it progressed, and now it says "Outlook has finished indexing all of your items" But, searching on anything, even the word "The" or "a" returns no results. I'm searching all Outlook items. I'm on an Exchange Server via VPN. Anyone have any suggestions? I uninstalled Office and re-installed and the problem went away... "Tom" <none@none.com> wrote in message news:%23yGP6NPOHHA.2140@TK2MSFTNGP03.phx.gbl... > Any search I do i...

Global Column Row Preview Font Size
I know I can change the column, row and preview font size for the current email folder's view, but how do I do it for all of the email folders? I have loads of email addresses each with lots of folders. I don't want to have to do each one at a time. Surely there must be a default font setting (even if it's only in the registry)? Thanks in advance, Tim. I too would love an answer to this. Ian "Timie Milie" <tim_milstead@yahoo.co.uk> wrote in message news:45782ee8$0$27107$db0fefd9@news.zen.co.uk... >I know I can change the column, row and preview font ...

Using Access 2003 on client to see Sharepoint 2007
Testing a Sharepoint environment and I can easily manipulate and run the database from Access 2007, but when I try to use Access 2003 it wants me to save the database to a location. The Access 2003 has the compatiblity pack, but it seems to not work when I download it. So the question is using Sharepoint 2007 and posting the Access 2007 format to it, can a user go to the sharepoint site and use a 2003 Access database that has the compatibility pack and open without having to download it? -- Message posted via http://www.accessmonster.com Send check for $500.00 to: David A Jenn...

running reports from MS CRM prompt for username and password
Senario: When running a report from Microsoft CRM client it prompts for username and password. When given it's runs the report correct question: if MS CRM 3 is configured correctly is it normal that when trying to generate a report in MS CRM prompt for username and password? No, ideally it should pass the users AD credentials to the reporting IIS server (single sign on). If you were to close the browser and run a different report does it still prompt for credentials? I've noticed this does also depend on the hostname. For instance if the reporting server uses a differe...

compact database in VBA access 2007
Hello, I have recently upgrade to office 2007 from office 2003. To compact a database from within the database itself, I used the follwing code. Unfortunately it no longer works in access 2007. Is there some similar code that will work? Public Function FncCompactTheCurrentDB() CommandBars("Menu Bar"). _ Controls("Tools"). _ Controls("Database utilities"). _ Controls("Compact and repair database..."). _ accDoDefaultAction End Function Thank You, SL On Thu, 28 Jan 2010 17:34:01 -0800, SL <SL@discussions.microsoft....

LDAP Write access?
My ldap server allows Write access to entries - and a few clients now support this. Any plugins available for Outlook to allow this too? Thanks None that I'm aware of. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net Subscribe to Exchange Messaging Outlook newsletter: EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM Outlook Tips: http://www.outlook...

MS Access 2007 code not running
I have created a brand new database in Access 2007 and linked some VB code to run on Form_Load. This code worked perfect with no problems the day I created it. Now today I opened the same form and it is not running the Form_Load code anymore (and yes it is still there, I did not delete it). I got frustrated so I copied and pasted the code to Form_Current and several On_Change events and set breakpoints at the beginning of all the events just to see if it was the Form_Load that was not triggering, but NO events are triggering my VB code. Any ideas? See this page: http://accessjunkie.com/fa...

unable to grant existing user access to TEST company
Set up test company using live company data; ran the script referenced in CustomerSource article ID#871973; can grant and remove user access in all other companies; ran grantsql 9.2; the dexsql log shows the user already has access probably because of using the other company data for the test; I can set up a new user and grant them access, existing users who already have access can view the company...but, I cannot figure out how to grant access to the TEST company to an existing user. The error popup reads "The user could not be added to one or more databases." Dynamics 9.0,...

Adding blank "separator" Rows in a Pivot Table
Does anyone know how to insert blank rows into a pivot table (say, between groupings, etc.)? There has to be a way, since some of the various table formats that come with Excel include blank rows. Double-click the field button that you want to format Click Layout Add a checkmark to Insert blank line after each item Click OK, twice Bucko wrote: > Does anyone know how to insert blank rows into a pivot table (say, > between groupings, etc.)? There has to be a way, since some of the > various table formats that come with Excel include blank rows. -- Debra Dalgleish Contextures http...

Accessing another user's calendar OL2000/OL2003/Ex2000
Here's the situation: Running Exchange2000, v 6.0, sp4. Users are on XP/OL2003 or NT/OL2000. (we're in the process of upgrading all of the workstations to XP.) VIP user is on XP/OL2003. He can access his calendar just fine on his workstation. His secretary and other office admin type people are still on NT/OL2000. They have reviewer or editor rights to his calendar. They try to access his calendar by chosing File/Open/Other User's Folder... and put in his name and calendar. When they do this, the process hangs. In Task Manager, Outlook is "Running." End the Outloo...

How to use count for calls per hour
I am trying to create a pivot table and chart that will show me the # of calls per hour by day for a range of dates Aug 1 - Sept 19. My data looks like this where each line is the date and time of the call: How do I get a count of the # of the calls per hour by day? 8/1/08 12:48 AM 8/1/08 1:53 AM 8/1/08 2:36 AM 8/1/08 3:24 AM 8/1/08 3:37 AM 8/1/08 4:04 AM 8/1/08 4:44 AM 8/1/08 4:55 AM 8/1/08 4:58 AM 8/1/08 7:02 AM 8/1/08 7:28 AM 8/1/08 7:43 AM 8/1/08 7:47 AM 8/1/08 7:56 AM 8/1/08 7:58 AM 8/1/08 8:13 AM 8/1/08 8:33 AM 8/1/08 9:07 AM 8/1/08 10:28 AM 8/1/08 10:38 AM 8/1/08 10:44 AM 8/1/08 ...

landed costs in MS RMS
Is there a standard functionality on handling landed costs (allocating freight and other related costs to the value of goods purchased)? We've heard that there's add-on that need to purchased in addition to RMS to support this feature and so far we couldn't hear of someone who has used it. Try contacting www.retailrealm.us directly for a reference on the Landed Cost add-on. Rob "Kstylian" <Kstylian@discussions.microsoft.com> wrote in message news:83D5F933-4592-4AEE-AE31-7673261F88EC@microsoft.com... > Is there a standard functionality on handling landed ...

MS Project 2007 question
Hello, I am developing a simple schedule with no resources but 2 tasks out of 100 are hazardous and I would like to make the schedule so that when these tasks occur, no other tasks can occur. In other words, even though all tasks are moving around as the schedule is being developed, I would like to make sure no other tasks can be scheduled wherever these 2 hazardous tasks fall. These 2 tasks are serial to every other task in the schedule. Is there a way to do this? Thanks for your help. Mike Create two milestones. Use milestone 1 as the successor task to all tasks th...

MS Money '04 Reference Books
Is anyone aware of any good MS Money '04 reference books? I've checked the MS Press website, but they seem to have one for every software program other than Money. Any suggestions? Thanks much, Too many books ended up getting pulped--I'm not sure there are any for Money anymore. You might check on http://www.amazon.com. "Mike" <anonymous@discussions.microsoft.com> wrote in message news:e72001c3f056$477b1c10$a501280a@phx.gbl... > Is anyone aware of any good MS Money '04 reference > books? I've checked the MS Press website, but they seem > to...

MS CRM Security 05-26-04
Hi, I have 2 business units: 1. New Business Sales 2. Account Management (management of current customers) I want to use the MS CRM security settings so that users in New Business Sales cannot see any account records for our current customers. This includes if the New Business Sales users select the &#8216;All Accounts&#8217; view. Is this possible with MS CRM? Yes. When you assign access rights to the role, only give them "Business Unit" access, they will only be able to see data within their BU. Matt Parks ---------------------------------------- ------------------...