SELECT Count either bad syntax or hangs access

[MLogue9]

"SELECT COUNT(directoryShort) FROM tclips WHERE filename LIKE 'C151%';" <<---- WTF. I get a syntax error:

2019-01-02 08:27:16 509: -2147217900 >> 0 / Other error: -2147217900 Syntax error in query expression 'SELECT COUNT(directoryShort)'.

And yet with "SELECT COUNT(directory) from tclips WHERE filename LIKE 'C151%';" I get no error; BUT takes long time or stuck in a loop. It's still trying, 55k records. Directory and directoryShort are both text fields in table tclips. I had to reset Access as it was clear it wouldn't finish. I called it from the immediate window with debug.print tableread("DirectSQL",SELECT...,0,0,0).



I'm using my function tableRead which handles the ADODB connection and it takes SQL and poops out any given row.

[isladogs]

Two issues
1. Filename may be a reserved word in Access so shouldn't be used for field names. Either change it or enclose in []
2. The default wild card in Access is * not % unless you are using ANSI-92 syntax

So use

SELECT COUNT(directoryShort) FROM tclips WHERE [filename] LIKE 'C151*'

This could be slightly faster

SELECT COUNT(*) FROM tclips WHERE [filename] LIKE 'C151*'

Or another method is using a domain function

DCount("*", "clips", "[Filename] Like 'C151*'")

[MLogue9]

I tried that. No error but it still is hanging access. I even made it more specific, SELECT COUNT(*) from tclips where [filename] like 'c151*' AND directoryShort = '18';

I wondered if queries shouldn't be invoked from immediate window. Technically the function tableread is handling the query. Oh well, I'm going to finish writing this reply and then give up waiting. My problem is my main code project isn't "finding" all of the occurrences of c151 in a table. I may try getting OCR to work again for my inventory db.

[isladogs]

Not sure how big your table is but the best way to speed up your count would be to index all the fields involved.
This could reduce the time needed to a fraction of a second instead of taking many seconds.

For example a count taking almost 10 minutes on an unindexed field was reduced to less than a second after indexing

[MLogue9]

Thanks. I didn't think of turning on indexing.

[isladogs]

Let us know if that solves your problem

[MLogue9]

It's still hanging. May be something with my own code although it works well otherwise, just not when called from immediate window. I tried a much much smaller table too, 450 records. It shouldn't take minutes to count a column.

[orange]

Can you tell us what db software you are using?
We all agree that counting 450 records or so should not take minutes.

If not possible to post copy of the database, is it possible to do some sort of extract and post a section of the data along with your vba/procedures etc.?

[MLogue9]

Yeah. This only hangs from immediate window so far I can tell.

Function tableRead(strTable, strField, strWhere, strEqualsValue, intRow)
'* This lovely function will read a table and return a value
' Table Name, SelectONEField, WhereField, EqualsValue, Row
'-- OR ("DirectSQL",SQL Statement,,,intRow)
pXname = "tableRead"
pXStack = Left(pXStack, 500) & ">" & pXname
On Error GoTo err_hand
Dim vRows As Variant
Dim strSQL, strSQL1, strSQL2, strSQL3, strSQL4, strSQL5, strSQL6 As String

    Dim cn4 As ADODB.Connection
    Set cn4 = CurrentProject.Connection
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    
    
        '****Argument Checker
       If (strTable = "DirectSQL") Or (strTable = "SQLDirect") Then '*Direct SQL Mode
            intdirect = 1
            GoTo sKip1
            End If
       
        If IsMissing(strTable) = True Then
            Call CStatus("No arguments specified", 408)
            Exit Function
            End If
            
        If IsMissing(strField) = True Then
            Call CStatus("Missing strField", 408)
            Exit Function
            End If
        
        If IsMissing(strWhere) = True Then
            Call CStatus("Missing strWHERE", 408)
            Exit Function
            End If
            
         If IsMissing(strEqualsValue) = True Then
            Call CStatus("Missing strEqualsValue", 408)
            Exit Function
            End If
            
        If IsMissing(intRow) = True Then
            Call CStatus("Missing IntRow", 408)
            Exit Function
            End If
            
sKip1:
'cn4 = Nothing
    'Skip Opening CN if it's already open!
     
    
    
sKip1b:
If (intdirect = 1) Then
    strSQL = strField
    GoTo sKip2
    End If
'* SQL SET
    strSQL1 = "SELECT" & " "
    strSQL2 = strField & " "
    strSQL3 = "FROM" & " "
    strSQL4 = strTable & " "
    strSQL5 = "WHERE" & " "
    strSQL6 = strTable & "." & strWhere & "="
    strSQL7 = "" & strEqualsValue & ""
    strSQL = strSQL1 & strSQL2 & strSQL3 & strSQL4 & strSQL5 & strSQL6 & strSQL7 & ";"
    
sKip2:
 
rs.Open strSQL, cn4, adOpenDynamic, adLockOptimistic
   
err_Res:
    
    vRows = rs.GetRows
    
    rs.Close
    Set rs = Nothing
    cn4.Close
    Set cn4 = Nothing
If (vRows(0, intRow) = Null) Then vRows(0, intRow) = -1
tableRead = vRows(0, intRow)

Exit Function
err_hand:
Select Case Err.Number
Case 9: 'Record X not found
    'call CStatus("Record X not found.", 308) 'Disabled 12-18 d/t too many records produced
   tableRead = -1
    Exit Function
Case 3021:
tableRead = ""
    Call CStatus("No records found", 309)
    tableRead = -1
    Exit Function
End Select
Call CStatus("Other error:", 539, Err.Number, Err.description, strSQL)
End Function

tagNo
room
zone
zone1
container
owner
contents
category
description

s
1
7
7
6
2
0
1
Initial Record

l5j
9
12
12
2
2
0
15
mixxed bag from cleanup of bedroom and/or murano Dec 18 2013, is there tape in there? Not hanging but stuck to right of bed.

l22v
9
9
9
2
2
0
21
unknown lot - Update: Bag is falling apart, re-inenvory ASAP

l19f3
2
1
1
2
2
0
2
bras, etc

131122B
1
1
1
2
7
0
10
video games, dvds, etc

l19f4
1
1
1
2
2
0
21
part out, re-sort

l22t
9
10
10
5
3
0
13
quilt, etc

l22k
9
0
0
1
2
0
21
Stuff from Ron's trailer

l22h
9
11
11
5
4
0
2
do NOT sale, first clothes!

l22r
14
18
18
2
4
0
1



l22n
9
18
18
2
4
0
1



l20w
14
18
18
2
4
0
1
Toys

l24f
9
11
11
1
2
0
8
error in initial record, this box contains these items. Mil trg certificate, some of Sherrys jewry.

l24v
9
0
0
1
3
0
17
DVDs

l24u
9
11
11
1
3
0
17
music CDs

l24r
9
32
32
1
3
0
8
re-audit. Curios, need internal picture. Box says CD' but has another label #.

l24q
9
0
0
5
3
0
1
stuffed animals

l24j
9
11
11
1
2
0
21
Some photos in here too, investigate and scan photos.

l22w
10
0
12
1
7
0
16
X-Mas, stuff not used 2013. LED B/W, Incandesant gold, bulbs, silver and white beads. NO GOLD ornaments :(

l24x
9
0
0
1
2
0
17
scan in? photo albums, etc

l24b
9
11
11
1
2
0
9
Stuff from Rons etc

l22j
9
10
10
1
7
0
4
kitchen stuff, containers, serving platters, gadgets - reaudited

l24m
9
0
0
1
2
0
4
RE-PACK ASAP. glasses, pending re-pack. A similar lot fell and two glasses busted.

l24s
9
11
11
1
4
0
1
matchbox cars, 3 boxes full of them!

l22d
9
11
11
1
3
0
21
jenny doesn't even know whats in vcr box

[orange]

I was suggesting a "pared down" database --only some data, but all of the relevant code as an accdb file (preferably inside a zip).


This does not work as you might think

Dim strSQL, strSQL1, strSQL2, strSQL3, strSQL4, strSQL5, strSQL6 As String

Only strSQL6 is considered text/string; the others will be variants.

Have you tried using explicit data types in you Function arguments?
tableRead(strTable, strField, strWhere, strEqualsValue, intRow) --these will be variants. Just a thought.

I don't think the data type issue would ever cause the long compute times.

[isladogs]

I just tried to use this but got too many undefined variable errors so was unable to do so
Also didn't understand what the intRow argument meant

Also unsure why you are using ADO instead of DAO

Suggest you add a sub to the same module for testing purposes

Sub TestTableRead()
    'add your own table, field, where condition etc
    TableRead "Postcodes", "PostcodeArea", "Quality", 9, 2600000
End Sub

For comparison I just ran this query on a table of 2.6 million records

SELECT Count(Postcodes.PostcodeArea) AS CountOfPostcodeArea 
FROM Postcodes
WHERE (((Postcodes.Quality)=9));

The result took 4 seconds

By comparison this took a fraction of a second

DCount("PostcodeArea","Postcodes","Quality=9")

So I have to ask, why is your (non functioning) function so 'lovely'?

[June7]

Show the Immediate Window statement you use to call the function.

Where are you calling the function when it works?

What goes in the intRow argument? I see you are setting an array to the recordset - why? intRow is referencing a row of array?

Variables pXname and pXStack are not declared and not used - why are they there? Variable intdirect is used but not declared. Should have Option Explicit in the module header.

Provide code for the CStatus() procedure. Never mind, I stripped it and the error handler out so I could test the core code which is failing on rs.Open - odd. Dang, Position is a reserved word, I will have to change my field name. The core code works for me calling function from Immediate Window.

What are you really trying to accomplish?

[MLogue9]

Show the Immediate Window statement you use to call the function.
debug.Print tableread("DirectSQL","SELECT COUNT(filesize) from tclips WHERE [filename] LIKE 'c151%';",0,0,0)

Where are you calling the function when it works?
From within VBA modules.

What goes in the intRow argument? I see you are setting an array to the recordset - why? intRow is referencing a row of array?
I'm not entirely sure on recordsets. I knew it was compatible with an array and maybe easier to access. IntRow indeed specifies which row of array to return.

Variables pXname and pXStack are not declared and not used - why are they there? Variable intdirect is used but not declared. Should have Option Explicit in the module header.
pxname and pxstack are used elsewhere - they are global and used for debugging.

Provide code for the CStatus() procedure. Never mind, I stripped it and the error handler out so I could test the core code which is failing on rs.Open - odd. Dang, Position is a reserved word, I will have to change my field name. The core code works for me calling function from Immediate Window. I will anyhow... below.

What are you really trying to accomplish? Windows 8 takes forever to sort files, I figure I'd code and engineer something faster than it would sort 25k files. I was wrong. I want to visualize what cameras are taking up space and when, in addition the added ability to delete clips in an non-linear pattern with one batch. Theres several terabytes of video.

This code for cstatus is messy and has a few loose ends. It is used to display status on a number of forms and also eventlog those statuses.

Function CStatus(strStatus, ByRef intType As Integer, Optional ByRef erNo, Optional erMsg, Optional strDatum)
'pXname = "CStatus"
'pXStack = Left(pXStack, 500) & ">" & pXname
'Updates and manages the status bar
Dim strPreamble, strOut, strType, strForm, strComment, strSQL, strPxStack, strCErrStack As String
Dim intColor As Double
Dim intPreLen As Integer
Dim bEcho As Boolean
On Error GoTo err_hand

'Color Codes
'12632256 = Lt Grey
'33023 = Orange
'65280 = Green
'16744576 = Steel Grey
'Define "Constants"
intPreLen = 350 'Length of previous message cache
'** Fix missings
    If (IsMissing(strDatum) = True) Then strDatum = "[N/A]"
'** intDebug ' Minimum Level of to report to status
bEcho = True 'Whether to echo to status
If (Left(intType, 1) = 3) Then intType = intType + 100

Select Case Left(intType, 1) 'Copy exists in ErrNoColor as of 170517
    Case 1: 'Debug Level
        strType = "Debug"
        intColor = 16699845
            If intDebug < 2 Then bEcho = True
    Case 2: 'Info
        strType = "Info"
        intColor = 16777215
            If intDebug < 3 Then bEcho = True
    Case 4: 'Minor Error
        strType = "Error"
        intColor = 33023
            If intDebug < 4 Then bEcho = True
    Case 5: 'Major Error
        strType = "Fatal Exception"
        intColor = 2829311
            If intDebug < 5 Then bEcho = True
End Select
'Error-level idiot explanations
strComment = "0"
    If IsMissing(erNo) Then erNo = 0
    If IsMissing(erMsg) = False Then strComment = erMsg
'strComment = errorTree(erNo)
strPreamble = Left(strPreamble, intPreLen) & "..."
strErrStack = Left(strErrStack, intPreLen) & " > " & pXname & ":" & intType
strCErrStack = strErrStack
 
reS:
Screen.ActiveForm.timeStatusUpdated = Now() 'Small field keeps time
If bEcho = True Then
    strPxStack = ""
    strCErrStack = "" 'Internal error stack
    End If
strOut = Now() & " " & intType & ": " & erNo & " " & strCErrStack & " >> " & strComment & " / " & strStatus & " [" & strDatum & "] .. " & strPreamble
    If bEcho = True Then Screen.ActiveForm.txtStatus = strOut
Screen.ActiveForm.txtStatus.ForeColor = intColor
If Screen.ActiveForm.Name = fInvMain Then strTag = Screen.ActiveForm.Controls("txttag").value
'Event Log
    If erNo = "" Then erNo = 0
    If IsMissing(erMsg) = True Then erMsg = ""
    If IsMissing(strDatum) = True Then strDatum = ""
    If Len(strPreamble) < 2 Then strPreamble = "[None]"
strForm = Screen.ActiveForm.Name

'Fixxed - Syntax Error for Some Odd Reason! Apr 27th
If ((strTag = Empty) And (Screen.ActiveForm.Name = "fInvMain")) Then strTag = Screen.ActiveForm.txtTag
strStatus = cleanString(strStatus)
strDatum = cleanString(strDatum)
strComment = cleanString(strComment)
strSQL = "INSERT INTO tEvents(txtdate, myerrno, interrno, myerrmsg, interrmsg, txtform, stack, process, Datum, idLink) VALUES ('" & Now() & "','" & intType & "','" & erNo & "','" & strStatus & "','" & strComment & "','" & strForm & "','" & strErrStack & "','" & pXname & "','" & strDatum & "','" & strTag & "');"
DoCmd.SetWarnings (warningsoff)
DoCmd.RunSQL strSQL
DoCmd.SetWarnings (warningson)
Exit Function
err_hand:
If Err.Number = 2475 Then
    bEcho = False
    Resume reS
    Else: MsgBox "555: CStatus Internal Error, Turn off error handling to view"
    End If
  
    
End Function

[June7]

Copied both functions to a general module. Disabled Option Explicit. Called tableRead from Immediate Window. Runs without error until I try the alternate "DirectSQL".

But it worked the second time, must have had a typo. The % wildcard returns records, * returns 0. Interesting. Guess I've never used wildcard in ADO SQL.

Still not clear to me what you want to accomplish. This code opens a recordset and returns recordcount. A simple DCount() can do that.

[isladogs]

Global variables need to be declared in the declarations section at the top of a module

I wouldn't use debug.print on that function in the immediate window.
Use a sub to test it. See my previous answer

You are still using % as a wildcard instead of *.

I asked a question at the end of my last answer. Why use this function at all?
To my mind its like using a sledgehammer to crack a nut .... slowly....instead of using faster built in functions