As noted in post 14, % wildcard works in the ADO recordset SQL. And I read something today that says % is used in ADO.
As noted in post 14, % wildcard works in the ADO recordset SQL. And I read something today that says % is used in ADO.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
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
This refers to the code posted in Post #13
In "Function CStatus", there is a line
The variable "strCErrStack" is declared as a string; all other variables default to Variant type.Code:Dim strPreamble, strOut, strType, strForm, strComment, strSQL, strPxStack, strCErrStack As String
In VBA, variables MUST be explicitly declared. (EDIT: What orange said in Post #10)
At the bottom of the code, there are lines
"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).Code:DoCmd.SetWarnings (warningsoff) DoCmd.RunSQL strSQL DoCmd.SetWarnings (warningson) Exit Function
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
Generally it is best to use:Code:DoCmd.SetWarnings False DoCmd.RunSQL strSQL DoCmd.SetWarnings True
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!Code:Currentdb.Execute strSQL, dbfailonerror
Last edited by ssanfu; 01-03-2019 at 06:26 PM.
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?
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.
I agree with that & often use the immediate window for the same reason. I would have written:I used the immediate window for fact it is immediate. I wanted to test it quickly without adding code I'd dispose later.
However, using a sub is useful if you want to try out various valuesCode:?tableread("DirectSQL","SELECT COUNT(filesize) from tclips WHERE [filename] LIKE 'c151%';",0,0,0)
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?
OR it should then beCode:SELECT COUNT(filesize) from tclips WHERE [filename] LIKE 'c151%';
However, I doubt I'm going to persuade you to change your approach at this stageCode:SELECT COUNT(filesize) from tclips WHERE [filename] LIKE 'c151*';
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
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
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.Code:rs.Close Set rs = Nothing cn4.Close Set cn4 = Nothing If (vRows(0, intRow) = Null) Then vRows(0, intRow) = -1 tableRead = vRows(0, intRow)
Block form
With the block form syntax it is easier to see the problem..... which isCode: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)
The problem is that you cannot check for NULLs this way.Code:If (vRows(0, intRow) = Null) Then
NULL is not equal to anything, not even NULL!
You must use the IsNull() function.
(or you could use If Len((vRows(0, intRow) & "")) > 0 Then )Code:If IsNull(vRows(0, intRow)) 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
Code: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)