SELECT Count either bad syntax or hangs access

[June7]

As noted in post 14, % wildcard works in the ADO recordset SQL. And I read something today that says % is used in ADO.

[isladogs]

Hi June
Its not quite as simple as that.
See for example: https://access-programmers.co.uk/for...d.php?t=262327

Of course, DAO code is far simpler to use and I still see no reason for using ADO here instead of DAO.
In fact I see no reason to use the TableRead function at all - though I haven't had time to study the new code in post 13

[ssanfu]

This refers to the code posted in Post #13

In "Function CStatus", there is a line

Dim strPreamble, strOut, strType, strForm, strComment, strSQL, strPxStack, strCErrStack As String

The variable "strCErrStack" is declared as a string; all other variables default to Variant type.
In VBA, variables MUST be explicitly declared. (EDIT: What orange said in Post #10)


At the bottom of the code, there are lines

DoCmd.SetWarnings (warningsoff)
DoCmd.RunSQL strSQL
DoCmd.SetWarnings (warningson)
Exit Function

"warningsoff" and "warningson" are not declared and they are not intrinsic constants (so there must not be a line "Option Explicit" at the top of the module - otherwise you would get a compile error).

Since "warningsoff" and "warningson" are not declared and "Option Explicit" has not been set, the two variables default to a value of 0 (zero).
So both DoCmd.SetWarnings lines set the warnings to Off/False.



The correct syntax, per Access, is

DoCmd.SetWarnings False

DoCmd.RunSQL strSQL

DoCmd.SetWarnings True

Generally it is best to use:

Currentdb.Execute strSQL, dbfailonerror

as this won't trigger a message and also won't turn off all the system messages as the DoCmd.SetWarnings will do after a failing query!

[MLogue9]

ridders52, Sorry I did not see that post. I may not be understanding the database functions correctly as I am a bit rusty. There isn't a function like tableRead where you can get a datum like foo = tableRead(...). Instead you need to define a connection and open and close it. It's slower, but easier to use, in my opinion. There problably is a better way.

I used the immediate window for fact it is immediate. I wanted to test it quickly without adding code I'd dispose later.

I tried using *, it doesn't work as a wildcard. I get no results. So I switched all back to %, which screwed up my multiplication operators too .

-----------
June7 - TableRead returns whatever single datum you tell it to. Hence purpose of intRow. But ridders52 has suggested it as redundant. We both know it's slow, because each iteration of it has to open and close connections. ...If the connection is left open, any code saying to open it will error. SAME goes for closed connections. I couldn't figure a way to test it without throwing errors, so I have the open and close within 3 lines in tableread.


I have several modules, tableread is in one. The code I presented that is a mile long is in a Access Class Object for the respective form, fclips. Do I need to declare a variable passed through many of these at top of each or a single module?

[MLogue9]

ssanfu



I had no IDEA in VBA declaration worked like that. I must have a hundred or so strings of declarations that go "one, two, three AS integer". I best get to replacing them although I haven't had a bunch of errors.

Thanks for correcting me on system warnings syntax. True and false make more sense and I don't know what made me use warningson and warningsoff.

[isladogs]

I used the immediate window for fact it is immediate. I wanted to test it quickly without adding code I'd dispose later.

I agree with that & often use the immediate window for the same reason. I would have written:

?tableread("DirectSQL","SELECT COUNT(filesize) from tclips WHERE [filename] LIKE 'c151%';",0,0,0)

However, using a sub is useful if you want to try out various values

I must be missing something but I still can't see why you are using a function that is VERY slow and doesn't seem to work properly ... yet you call it 'lovely'!
I gave two alternative approaches to counting records in post 11.

Both are simple to code, run quickly (if using indexed fields) and don't require the use of recordsets.
If you need to use wildcards, * will definitely work in those cases.

Have you tried just running this as a query?

SELECT COUNT(filesize) from tclips WHERE [filename] LIKE 'c151%';

OR it should then be

SELECT COUNT(filesize) from tclips WHERE [filename] LIKE 'c151*';

However, I doubt I'm going to persuade you to change your approach at this stage
I've asked before why you are using ADO rather than DAO
DAO is simpler to code and more powerful ... so what is special about your code that requires the use of ADO?

Good luck with solving your problem

[ssanfu]

Some comments on the code for "Function tableRead"

It is very rare to use the "GOTO" statement. In the IBM BASIC days, it resulted in very messy code that actually had a name: Spaghetti code.
The command is still in VBA for backward compatibility.

Here are two instances with changed code to eliminate the GOTO command:
(1)


and ----------------------------------------------------------------------------------------------
(2)



The last part of code I have comments on is

    rs.Close
    Set rs = Nothing
    cn4.Close
    Set cn4 = Nothing
    If (vRows(0, intRow) = Null) Then vRows(0, intRow) = -1
    tableRead = vRows(0, intRow)

IMHO, using the single line syntax of the IF statement is just plain lazy and makes code harder to read compared to the block form syntax.

Block form

    rs.Close
    Set rs = Nothing
    cn4.Close
    Set cn4 = Nothing
    If (vRows(0, intRow) = Null) Then
        vRows(0, intRow) = -1
    End If
    tableRead = vRows(0, intRow)

With the block form syntax it is easier to see the problem..... which is

   If (vRows(0, intRow) = Null) Then

The problem is that you cannot check for NULLs this way.
NULL is not equal to anything, not even NULL!
You must use the IsNull() function.

   If IsNull(vRows(0, intRow)) Then

(or you could use If Len((vRows(0, intRow) & "")) > 0 Then )


Note: you can test this by going to the immediate window in the IDE and entering
? 1=1

then try
? null = 1

Next try
? mull = null

Use

    rs.Close
    Set rs = Nothing
    cn4.Close
    Set cn4 = Nothing
    If IsNull(vRows(0, intRow)) Then
        vRows(0, intRow) = -1
    End If
    tableRead = vRows(0, intRow)