Dont understand Immediate windows ?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

steve - 26 Feb 2007 15:55 GMT

I have the following code.

Public Function fTableExists1(strTableName As String) As Boolean
Dim db As Database
Dim tdf As Tabledef
Set db = DBEngine(0)(0)
fTableExists1 = False
For Each tdf In db.TableDefs
If tdf.Name = strTableName Then fTableExists1 = True
Next tdf
Set db = Nothing
End Function

I have a file called "Atable".

When I put
debug.Print y = fTableExists1("Atable")
Into the Immediate windows I get False.

When I put
?fTableExists1("Atable")
Into the immediate window I get True.

When I rename the table to something else I get the exact same answers
as above.
Can anyone tell me why I dont get the answers that I think I should
get ?? That is True when the table exists and false when it does not.

Regards

Reply to this Message

Jason Lepack - 26 Feb 2007 16:01 GMT

I suspect that the reason you get false is because that is the value
of y. What you should be doing is this:

y = fTableExists1("Atable")
debug.Print y

> I have the following code.
>
[quoted text clipped - 25 lines]
>
> Regards

Reply to this Message

steve - 26 Feb 2007 16:52 GMT

Thank you for your answer.

I still get odd results depite your change. It sort of works but then
if I change the name of table in the db tables windows it doesnt seem
to recognize that there was a change. It still registered the result
as true.
eg.
If I change the name of the db to xAtable in the Access tables I still
get true.

y = fTableExists1("Atable")
debug.Print y
True

In fact if I change the table name in the script to the new table name
that does exist I get false. As if it is still thinking (I suppose)
that the table name is Atable, that does not exist now. I dont get
it.

y = fTableExists1("xAtable")
debug.Print y
False

Reply to this Message

Jason Lepack - 26 Feb 2007 17:18 GMT

Sorry, are you typing all of this:

> y = fTableExists1("Atable")
> debug.Print y
> True

into the Immediate Window? If so then don't. Your function works
based on:

> When I put
> ?fTableExists1("Atable")
> Into the immediate window I get True.

if that table actually exists in your database.

I would rather suggest using your function in a query and testing that
way.

The immediate window does not have a memory. It will call functions
for you, display it's output and then forget everything.

If you use Debug.Print from within a code module then the result will
be output in the immediate window.

If you're still having problems and you are actually using a code
module then paste it here and we'll have a look.

Cheers,
Jason Lepack

> Thank you for your answer.
>
[quoted text clipped - 5 lines]
> If I change the name of the db to xAtable in the Access tables I still
> get true.

> In fact if I change the table name in the script to the new table name
> that does exist I get false. As if it is still thinking (I suppose)
[quoted text clipped - 4 lines]
> debug.Print y
> False

Reply to this Message

Evan Camilleri - 26 Feb 2007 16:01 GMT

?fTableExists1("Atable")
runs the function and tells you its result
-
debug.Print y = fTableExists1("Atable")
runs the functions and checks if the result is equal to y

if i understood well you need to do :
y = fTableExists1("Atable")
debug.Print y

>I have the following code.
>
[quoted text clipped - 25 lines]
>
> Regards

Reply to this Message

steve - 26 Feb 2007 17:15 GMT

Thank you for your answer.
Sorry for being so dense but I still seem to get unexpected odd
results. I have renamed the table to xAtable in the database.
Now I run the following commands. I think each answer is wrong.

y = fTableExists1("Atable") 'remember Atable does not exist now
debug.Print y
True

debug.Print y = fTableExists1("Atable")
True

debug.Print fTableExists1("xAtable") 'xAtable DOES EXIST
False

The bottom line is the answers seem to be all over the map and Im
confused by it.

Reply to this Message

Albert D. Kallal - 26 Feb 2007 18:01 GMT

> y = fTableExists1("Atable") 'remember Atable does not exist now
> debug.Print y
> True

The above tells me that "atable" does exists. Do a compact and repair Exit
the database...re-enter.

the above session tells me that "Atable" does exist.

if you go in the immediate window:

> y = fTableExists1("Atable") 'remember Atable does not exist now
> debug.Print y
> True

The above means that atable exists. You just mistaken that "atable" is not
there...it is. Perhaps the table is hidden or something, but I would suggest
you test this again, and use a different name...eg: table1

also, does all of the code in the application compile??? You need to do a
debug->compile and make sure all code is compiled.

> debug.Print y = fTableExists1("Atable")
> True

careful with the above. Assuming Atable does not exists, then we get

y = fTableExists1("Atable")
debug.print y = fTableExists1("table1")
true

You will ALWAYS get true for the above. Lets assume that table100 does NOT
exist. we go:
y = fTableExists1("table100")
tthe above beomes
y = false
at his point, now "y" has a value of false
We then go

debug.print y = fTableExists1("table100")

the above becomes
debug.print false = false

and, the result printed is true. If you execute any other code in the
immediate window that causes a exectuion error, then ALL varaibles are
re-set.

Check your results again.

Try typing in the following in the debug window:

debug.print currentdb.tabledefs("atable").name

then

debug.print currentdb.tabledefs("junk").name

Signature

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

Reply to this Message

Norman Yuan - 26 Feb 2007 18:44 GMT

If the database have more than one tables, you'll see you have a bad logic
in your "For Each...Next" loop: it loop through all tables and reset the
Function's value. Since you can only have one table, named as "Atable", the
Function will be set to True once in the loop and set to False for other
table. Since there is not particular order the table are looped, hence the
rondom return values of True or False.

The loop should be that once you find match, exit the loop/Function:

For Each tdf In db.TableDefs
If UCase(tdf.Name) = Ucase(strTableName) Then
fTableExists1 = True
Exit For
End
Next tdf

Note, you may also want to make sure to compare the striing value in the
same case by simply using Ucase() or LCase().

>I have the following code.
>
[quoted text clipped - 25 lines]
>
> Regards

Reply to this Message

George Nicholson - 26 Feb 2007 19:06 GMT

>Since you can only have one table, named as "Atable", the Function will be
>set to True once in the loop and set to False for other table.

Where within the loop does it get set to False???
There is no logic problem within the OP's Loop. The value is set to False
*before* the Loop starts. That value only changes if the table is found,
which is exactly what he wants.
Your point about exiting when found is valid from a performance standpoint,
but unrelated to the "problem" he was having with understanding the results
of the immediate window..

> If the database have more than one tables, you'll see you have a bad logic
> in your "For Each...Next" loop: it loop through all tables and reset the
[quoted text clipped - 44 lines]
>>
>> Regards

Reply to this Message

steve - 26 Feb 2007 21:57 GMT

If you see this message twice Im sorry. I thought I sent it but then
did not see it in the news group, so I re wrote it and sent it again.

Here is the sub I have been using to test out this function.

I have made some changes based on suggestions here.
I have Compacted and repaired.
I have renamed the db table1 for simplicity.
I can see the db table1 it is not hidden in any way.
I have tried to run the following in a Immediate window
debug.print currentdb.TableDef("Table1").name
but just get the error compile error: Method or data member not found

The below code does not work, or at least gives me an answer Im not
expecting. If I run it with a table that I know exists it tells me
true. But then if I rename the table to something else it still
reports it as true.

Public Function fTableExists1(strTableName As String) As Boolean
Dim db As Database
Dim tdf As TableDef
Set db = DBEngine(0)(0)
fTableExists1 = False

For Each tdf In db.TableDefs
If UCase(tdf.Name) = UCase(strTableName) Then
fTableExists1 = True
Exit For
End If
Next tdf

Set db = Nothing
End Function
'-------------------------------------------------------------------
Sub TableExists2()

If Str(fTableExists1("Table1")) = True Then
MsgBox ("yay there is a table called ")
Else: MsgBox ("oops there is no table")
End If

End Sub

Reply to this Message

Jason Lepack - 26 Feb 2007 22:28 GMT

Add this right before you enter your for loop:
db.TableDefs.Refresh

Just because you rename a table the tabledefs don't rebuild
themselves.

Cheers,
Jason Lepack

> If you see this message twice Im sorry. I thought I sent it but then
> did not see it in the news group, so I re wrote it and sent it again.
[quoted text clipped - 38 lines]
>
> End Sub

Reply to this Message

steve - 27 Feb 2007 18:52 GMT

Thanks that seems to do the trick.

Here is my final code. I dont put it here for you guru's but for those
novices like me that are struggling to find the right. Code. The
following code uses a function to check to see if the table exists.

Public Function fTableExists1(strTableName As String) As Boolean
' This fuction must be run in a sub. see TableExists2()
Dim db As Database
Dim tdf As TableDef
Set db = DBEngine(0)(0)
fTableExists1 = False
db.TableDefs.Refresh 'Refresh to pick up any new changes to
list of tables
For Each tdf In db.TableDefs
If UCase(tdf.Name) = UCase(strTableName) Then 'Make one case
(upper case)
fTableExists1 = True 'Exit when true
Exit For
End If
Next tdf

Set db = Nothing
End Function

'---You must run the fuction in a sub like
below-----------------------

Sub TableExists2()
Dim myTable_1 As String
'This uses the fuction fTableExists1
'Put table in varable
myTable_1 = ("Table1")

If Str(fTableExists1(myTable_1)) = True Then
MsgBox ("yay there is a table called ")
Else: MsgBox ("oops there is no table")
End If

End Sub

Reply to this Message