can literally be anything - could be a web application, a C# desktop application, an Access application, heck, even an Excel XLSM (which Mat did) but basically it provides interface for users to carry out their tasks --- salespeople entering their sales order, shipping people deducting shipped stuff from the inventory, that kind of thing.
it's very open ended definition but you also can think of it as "mission-critical" since it tells everyone what's what at the business.
I got the spreadsheet to go from sequential calculation to use HPC (High Performance Computing) for Excel which lets the calculation be offloaded to a computing cluster.
@this In one column for Field Foo you have A,B,C,D. Another is Bar 1,2,3,4. Combine them together and you have 16 combinations. Add another field/column Duck with Cat,Dog you get 32 combination... etc...
@this I'm very interested in learning about this.
Not specifically about you but about properly setting stuff up.
/badjoke
@PeterMTaylor Easy to poke distractions out of your office though.
without knowing more i can't really advise any good recommendation beyond the fact that if you do need to be able to run your calculations without watching paint dry, you likely need a SQL Server (which can be used in headless automation)... Depending on your security policies, you might be able to use SQL Azure, which in turn enable you to use other stuff in Azure that would help with running calculations and sending you the results.
if EXCEL is used primarily as datastore and you're running long-running VBA procedures to extract any meaningful data, you should probably change to a Database
if EXCEL is primarily used to generate BI and other soft data-requirements, you can still go and use a database, just be aware that generating nice charts from databases is a bit more programmery than selecting a range and saying "generate chart"
I don't know very much of what I don't know for SQL - the main thing I know I don't know is the intricacies of why using (nolock) all the time is a bad idea.
@Mat'sMug Does github have an easy way for me to search all the code in a repository for a specific phrase, without opening or going to each file's page individually?
@this Right, that's how my team uses it. We use it because we're generally okay with dirty reads and don't want to hold up more important database actions.
and if you're not careful, you can kick the consistency of transactions right into the nuts when they're executed with nolock and modify the same records
@Mat'sMug I may have one more question in a bit - I'm trying to implement ADODB "try again" functionality because one of our databases has been having connection issues. I'm searching through your VBTOOLs to see how you did it for my pre-question homework.
the best justification, IMO is when you are orchestrating other "transactions" (let's say another database that can't be linked directly), but even so, staging tables still is much better solution.
or SSIS if it's a regular thing and can be automaed.
@Mat'sMug @this I'm guessing you'd both severely frown upon something like "if continually unsuccessful, try again every second for 30 seconds, then alert the user"
All that's needed to fix it is to try again, so I'm trying to have my queryer try again automatically rather than needing the user to push Debug, Continue.
Private Function OpenConnection(ByVal QueryMetaParameters As classQueryMetaParameters) As ADODB.Connection
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.ConnectionTimeout = 0
Dim strConn As String
With QueryMetaParameters
If .strSQLApp = "Golden" Then strConn = (string)
If .strSQLApp = "Teradata" Then strConn = (string)
If .strSQLApp = "SQL Server" Then strConn = (string)
End With
On Error GoTo ErrorHandler
cnn.Open strConn
@this Good point: no, I do not. However, I think I dodge that because I'm only trying to retry on error. An error doesn't occur if the query's still running.
@this Yeah, I have my ConnectionTimeout as unlimited, basically. But I think it's returning that error when the server can't be reached to start the command.
@this Good advice. I instantly understood the distinction between connect and command timeout. I think that the SO answer I originally got ConnectTimeout = 0 from was actually trying to protect against commandTimeout and didn't know how to properly do so.
so yeah, first find out for a fact that your query shouldn't take more than 30 seconds
and that it doesn't cause blocking.
if it could get blocked, that would cause timeout especially if you only get it during busy time.
Again, I don't know Oracle but I am told that it's more optimistic than SQL Server WRT its locking model so that might be less of an issue but #VerifyFirst.
of all databases I've installed, my experience with Oracle was the worst. Kept had to restarting it because.... I don't know what. It's kind of sad that it was an "express" (which mean it should be easy to setup).
Private Function OpenConnection(ByVal QueryMetaParameters As classQueryMetaParameters) As ADODB.Connection
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.ConnectionTimeout = 0
Dim strConn As String
With QueryMetaParameters
If .strSQLApp = "Golden" Then strConn = (string)
If .strSQLApp = "Teradata" Then strConn = (string)
If .strSQLApp = "SQL Server" Then strConn = (string)
End With
On Error GoTo ErrorHandler
Dim lngRetryCount As Long
^ For reference/anyone's interest, that's my quick solution for now.
FWIW, I think that function tries to do too much - it should be only concerned with opening and returning a connection but in the error handler you say the query will be skipped. that's not really in the function's scope of responsibility. Better to throw an error back to the calling function which presumably knows what query it needs to run and thus report it properly from there.
@Mat'sMug at least it's something more manageable than Bobby's full name. ;)
Great, thanks, that's proper bubbling up right there.
Mat will be happy that you helped me bubble up better.
Do I even need to Err.Raise? It's already been raised, right, since I'm in the error handler? Or are user-raised errors dealt with differently than system-raised errors?
Got it. But if there's already an error, can I just do Exit Function? Will higher level function still be able to detect the existing error without re-raising it? Nm, I'll just Google if errors are global.
@this I did some experimenting, and I see you need to Err.Raise because (system errors, at least) errors aren't global. So you have to use Err.Raise to pass it up.
@puzzlepiece87 there is no distinction between a system or user error
but yes, Err.Raise will cause you to leave the current procedure unless there's an error handler and it's not already active.
@Mat'sMug at least he's only overwriting only one entry. I guess he didn't want the burden of dealing with a text file.
I wonder how hard it would be to get logging to event log set up. That would more "better" in the sense that there's no text file to manage but registry isn't abused, either.
@this there's gotta be a COM API for that somewhere
@this TBH the file logger is an easy cause of headaches, given a hard-coded path and/or multiple users. Best log would be to the Application event log indeed.
VBA Rubberducking
VBA Rubberducking