Does the table exist?

[crosmill]

You can use this

if EXISTS (select * from INFORMATION_SCHEMA.tables where table_name = 'Table8')

for SQL server but not for access.

Is there a way in VBA perhaps I could use.

Thanks

 

[RichardJ]

I'm guessing you want to check in Access right?

well if so, you got two ways I can think of off the top of my head....

First up, similar to your method but query Access sys table instead, so...


SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Name)="table8"))

You can then use that SQL and wrap an IF around it either in a query or in VBA.


Or you could, in VBA, attempt to reference the table and trap for the error if it does not exist. So...

dim blnTableFound as boolean

On error goto errorhandler

blnTableFound = true

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


errorhandler:
select case err.number
case 3265
blnTableFound = false

 

[crosmill]

Thanks Richard, I can see where your going with this but I'm having trouble implementing it.

this is what I have so far

tableExists = DoCmd.RunSQL("SELECT MSysObjects.Name FROM MSysObjects WHERE ((MSysObjects.Name) = 'LEAFile')")
If Len(tableExists) > 1 Then
DoCmd.RunSQL ("DROP TABLE LEAFile")
Else
End If

The trouble is that it doesn't like me putting the runsql command in there. do you know how to assgin the table name to a variable in vba

thanks

 

[RichardJ]

Actually an easier way has just sprung to mind....


dlookup("Name", "MSysObjects", "Name = 'LEAFile")


if dlookup finds the table it will return a string containing LEAFile if not it will be null


Cheers

 

[crosmill]

Nice one cheers mate, finally got it, this is what I eneded up with.

tableExists = Nz(DLookup("Name", "MSysObjects", "Name = 'LEAFile'"))