Why will this SQL recordset not open in outlook VBA ? ! ?

Question

0.00/5 (No votes)

See more:

I use similar code to this in other areas of my program, my Database seems to be working OK, the SQL line works OK in Management Studio, but every time I get to this point, the program exits the routine, it just skips to the End Sub as soon as it hits the .Open line ... If there is a problem with the Database, surely I should get an error when I check the State (which returns 0) ?!?

VB

Sub LoopDatabaseLeague()

On Error GoTo LoopDatabaseLeagueError

SQLstr = "SELECT Name, LeagueType_ID From League Where League.[LeagueType_ID] = '2';"

myEmailFolder = ""

Set KA_RS_League = New ADODB.Recordset

If KA_RS_League.State = adStateOpen Then KA_RS_League.Close <<< State = 0

KA_RS_League.Open SQLstr, KA_DB, adOpenDynamic, adLockOptimistic <<< This line skips to the end

Do Until KA_RS_League.EOF
    KA_Which_Newsletter.ComboBox1.AddItem (KA_RS_League![Name])
    If FindString(mySubject, KA_RS_League![Name]) Then
        myEmailFolder = KA_RS_League![Name]
    End If
    KA_RS_League.MoveNext
Loop
    
KA_RS_League.Close

LoopDatabaseLeagueError:

End Sub

What I have tried:

Different code, searched internet, looked for error codes

Posted 20-Mar-17 22:45pm

Gary Heath

Updated 21-Mar-17 1:06am

v2

Add a Solution

Comments

CHill60 21-Mar-17 5:31am

You are "swallowing" the error by having an empty error handler - this is very very bad practice. Comment out On Error GoTo LoopDatabaseLeagueError, run the code and tell us what the error actually is

Gary Heath 21-Mar-17 5:59am

It makes very little difference, instead of the next statement being the End Sub, it is the line following the call to this module :-(

Is there not a variable somewhere that holds the result of the statement ? If so what / where can I find it ?!?

CHill60 21-Mar-17 10:15am

It makes a huge difference. By getting rid of the on error goto, a VISIBLE exception would be raised which would help you find out what the problem is. You have "no idea why the program cannot just tell me that !!!" ... It doesn't tell you that because YOU told the program NOT to tell you anything about ANY errors.

Gary Heath 21-Mar-17 10:47am

I have tried Goto 0 & Goto Next but it didn't tell me anything, what I meant by my comment was that if there is no Connection to the Database to where I am creating a Recordset, why does it not report that, under any of the Goto options ?!?

CHill60 21-Mar-17 10:15am

Notice the Reply link next to comments? If you use that link then the poster is notified of your response. In other words, I could have given you further assistance 4 hours ago

Gary Heath 21-Mar-17 10:44am

Ahh yes, it only appears when my cursor is over it so didn't spot it !! Will try to remember in future ...

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Gary Heath · Accepted Answer · 2017-03-21T01:06:00

Solution 1

I was losing my Connection to the database somewhere along the line, no idea why & no idea why the program cannot just tell me that !!!

Resolved now anyway, thanks for your help ...

Posted 21-Mar-17 1:06am

Gary Heath

Comments

Richard MacCutchan 21-Mar-17 10:01am

It would have told you if you had not chosen to ignore errors in your program.

CHill60 21-Mar-17 10:13am

A virtual 5 for this comment :-)

Richard MacCutchan 21-Mar-17 10:38am

I often wonder how many real released business apps are running around the world with this sort of nonsense left in.

Gary Heath 21-Mar-17 11:24am

As I said to CHill60, Richard, I tried Goto 0 & Goto Next, it just dropped out of the routine and the Recordset was closed / didn't exist in my logic ... this was because the Connection to the Database was obviously being reset somewhere, but I don't know where. I have now closed it at the end of the previous Module and Connect again at the start of this Module & that works OK.

What I do not understand is why the program will process the lines ...

Set KA_RS_League = New ADODB.Recordset
If KA_RS_League.State = adStateOpen Then KA_RS_League.Close

... without a Connection, regardless of the Goto I was using, I was getting no error message or code, just an "Out of Context" message in the Watch.

Still all sorted now, cheers :-)