|
Is there any way to find out the status of opening a recordset in VBA? I thought it was the SQL that was slow but it is instantaneous. It is the open of the recordset that is slow and I would like to be able to monitor its progress.
ORIGINAL QUESTION SHOWN BELOW FOR HISTORY
I'm running an SQL query in VBA in Excel to return a record set. Is there any way to know the progress of the query so I can show it on the status bar (or elsewhere)? If there are millions of records that would be better than "please be patient" or "processing 15,000,000 records".
modified 5-May-16 19:04pm.
|
|
|
|
|
There is no status information returned by the SQL Server while the query is running, so no.
|
|
|
|
|
If you are processing 15,000,000 records in VBA you have shot yourself already, feedback is not going to save you.
That is an outrageous number to be dealing with, especially with VBA.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
That would be an extreme example. Generally the max might be in the range 300,000 records and it whips through that in about 10 seconds. It might go to a million or more. Probably it would never hit 15 million, and I agree that number would be outside reasonableness for the tools used.
|
|
|
|
|
You're still screwed.
No database engine reports back any notification of it's progress. If the query is taking 10 seconds, you won't know anything about it for 10 seconds.
The problem with doing this in VBA is that it doesn't support threading and the UI will be blocked (not able to update) while the query is running.
The only time you're going to be able to get any progress information is if you're returning records from a Reader, you read one record at a time from the result set. The problem with this is the Reader has no idea how many records it's returning. It just keeps reading records until the end of the dataset shows up from the database.
|
|
|
|
|
I have practically no experience with VBA so I can't guarantee that the following would work but I assume it will (maybe someone else can comment on this). Also I'm deliberately not addressing the issue with the high amount of records you want to deal with and the issue with the unresponsive UI. This is a kind-of workaround to be able to display progress-information:
1) Issue your query but modified to return only the count of found records.
2) Divide the record-count into batches of some size that suits you (e.g. 5000) and issue your query but with added paging-instructions: sql server paging - Google Search[^]
After one batch/page has been retrieved, you can update the progress information, then retrieve the next batch/page, until finished.
The price you're paying for the benefit of progress information is more overall time required because the database server has to execute the query multiple times. That's why the batch/page size shouldn't be too small (while a smaller size would improve the resolution of the progress update).
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
modified 9-May-16 11:38am.
|
|
|
|
|
The answer is the same for 300.000 or 15.000.000 records.
DON'T DO IT.
Think about it, do you need to handle 300.000 records for the dialog with user ?
If you need to update your 300.000 records, see if it is possible to device a sophisticated SQL commend that will do it on server and avoid transfering the records.
You should tell the reason of the need of 300.000 records, there is certainly a solution that don't need such transferts.
Patrice
“Everything should be made as simple as possible, but no simpler.” Albert Einstein
|
|
|
|
|
OK - well - it works. The user doesn't see the records, all I do is ask the user between what dates then I use that as input to the SQL. So they get a slice of the file. I write that out then I do a join to a third file and write out that file. That end file is used as input for a pivot table. Therefore except for initially asking the date range, there is no user presentation except via the pivot table which is extremely fast because by then I have trimmed all the fields I don't want and all the records I don't want.
Originally I was trying to do this as SQL in Excel itself through the SQL Editor but note well that if you try to have more than two tables in joins in a query it will not work. (I posted this in SQL questions already). That is why I went to VBA in the first place. So I open a box, get the date from the user, process the records, then refresh the pivot. It seems to work well. Technically I -might- be able to combine the two queries but really for clarity sake it's almost better as 2 queries because I do field reformatting and so on. Yes doing two writes instead of one is slower so I may check this out later however I have something that works. I am of course open to suggestions how could it be better - but I don't see a horrible performance issue (yet). I think what happens is when you set a recordset equal to an SQL statement, it doesn't really run it until you try to open the recordset. Which would kind of make sense, I guess.
Thanks for all help, many thanks.
|
|
|
|
|
Also.... if I could perform SQL statements on a recordset that would be ideal. That would then allow me only 1 write. I don't think you can do this but if anyone does do it I'd like to know. It would also reduce a bit of code where I am manipulating things also in VBA before I do the write, which could be done in the SQL statements more simply.
|
|
|
|
|
You should show you SQL code.
SQL is veru powerful and can do many things without intermediate database writing.
Patrice
“Everything should be made as simple as possible, but no simpler.” Albert Einstein
|
|
|
|
|
myQuery = "SELECT *, Format([End Time],'YYYY-MM-DD') AS EndTimeDate from CDRecords.csv " & " WHERE Format([End Time],'YYYY-MM-DD') BETWEEN '" & searchStartDate & "' AND '" & searchEndDate & "'" & " ORDER BY [End Time] ASC"
myQuery = "SELECT *, R.[AreaName] from DateSlicedCDR.csv AS Q LEFT OUTER JOIN AreaCodes.csv AS R ON Q.[AreaPrefix] = R.[AreaPrefix] ORDER BY [EndTimeDate] ASC"
Those are the two queries. They work fine. NB: Formats have to match on the dates.
searchStartDate and searchEndDate variables are properly formatted dates obtained from the user form.
|
|
|
|
|
Not serious !
csv files gave never been databases.
You need to use real databases, bt setting indexes, you will get dramatic improvement.
Patrice
“Everything should be made as simple as possible, but no simpler.” Albert Einstein
|
|
|
|
|
Well.... it comes in text file format, so, it would be yet another step to convert it to something else, but I could do that. ?What would you suggest, if I did do that?
Performance isn't horrible right now but definitely yes I'd like to speed it up plus as numbers of records get higher it will get worse.
Use currently appends the text file with new data, so chances are the first file I get will be in text format - however - looking to the future, yes, it could be way better. I'm listening...
|
|
|
|
|
Situation is complicated, would need to know full details on data flow to have global view and give correct advices.
All I can say is that actual situation resort to brute force and need heavy changes to optimize.
This is getting by far out of scope of this forum.
You need to hire a professional.
Patrice
“Everything should be made as simple as possible, but no simpler.” Albert Einstein
|
|
|
|
|
Also if R returns null on the match for area code, I write *Not Found* to area name field in the final file, that gets rid of null which is sometimes bothersome, also it points out to the user, that they need to add some codes (if they want).
|
|
|
|
|
Break you application into a number of components.
ExtrcLoadTransform (ELT)
Load the data from your text file into a database (I prefer SQL server). You need to change the way the user inputs the data (append may not be the best way to go forward).
Your target table should be an exact match to the excel source file. Don't try and format or type the data, take it in as varchar.
Transform the loaded data into your working table doing any validation and data typing in the transform.
Now you can use SQL to query the data as required - this not becomes trivial and speed will not be an issue. You may need to tune the indexes but SQL Server supplies a tool for that as well.
As you get more familiar with TSQL you will find that you can probably output the data in the exact format you require (although pivot is excellent in Excel).
Good luck
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks Mycroft. This is helping a Lot.
Jim
|
|
|
|
|
I am new to vb.net programming.The CButton ctrls by SSdiver2112 look interesting but I have no idea on how to use.
Any help would be appreciat  ed.
|
|
|
|
|
You also appear to be new to talking to people in a forum environment.
What are you talking about? If it's an article here on CP, there's forum at the bottom of the article to ask questions like this.
If it's not an article here, you're going to have to provide a link to whatever it is you're talking about.
|
|
|
|
|
I assume you're talking about this article: Custom Button Control with Gradient Colors and Extra Image (VB.NET)[^]
The source download includes a test/demo-Form. Did you take a look at that?
Please note that this forum has nothing to do with that article besides being part of the same website. If you need help with an article you should post your question to the message board of the article which you'll always find at the bottom of the article. The author of the article will then (hopefully) respond to you and obviously he's the one best suited to answer your question. In case you don't get a response from the author you're certainly free to ask here but then you should a) mention the article which you're talking about and b) explain your problem in more detail than you did here.
If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson
|
|
|
|
|
I am planning to use OpenQA.Selenium to automate website filling in Google Chrome.
Here is the part of vb.net code,
<pre>Public driver1 As ChromeDriver = Nothing
Public WithEvents driver As EventFiringWebDriver
Dim service As ChromeDriverService = ChromeDriverService.CreateDefaultService
Dim chromeOptions As New OpenQA.Selenium.Chrome.ChromeOptions()
chromeOptions.AddExcludedArgument("ignore-certifcate-errors")
chromeOptions.AddArgument("test-type")
service.HideCommandPromptWindow = True
driver1 = New ChromeDriver(service, chromeOptions)
Here,
driver_Navigated fired only when pages are navigated using code. When I navigate manually in Chrome browser driver_Navigated is not firing.
What mistake is there in my code?
Are there any other working example codes for Website automation in Chrome?
Please help.
|
|
|
|
|
Did you install the chrome driver? Is it available in the system path?
ChromeDriver is maintained / supported by the Chromium project iteslf. WebDriver works with Chrome through the chromedriver binary (found on the chromium project’s download page). You need to have both chromedriver and a version of chrome browser installed. chromedriver needs to be placed somewhere on your system’s path in order for WebDriver to automatically discover it.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
I have installed chrome driver and it is available for the program.
|
|
|
|
|
"Available" means that it can be found in the system path? If unsure, open a console and type "path", it should include the directory where the driver can be found. It would also be helpfull to test it with another driver (like one for IE) to ensure that it is the driver that is causing the problems, not some other part.
Next to that, you could check chromedriver issues[^], to see whether it is a known problem.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Chromedriver is included in the project ( project\bin\debug). As I said 'driver_Navigated' firing only when pages are navigated using code. When I navigate manually in Chrome browser driver_Navigated is not firing.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
