Check to see if an access table column exists

[ngod]

Hi,

I am having a problem. I first import a spreadsheet into access as a tempTable, then I am trying to filter it into a new table. I thought we could use IF EXISTS in vba sql but I keep getting an error message:

"Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'."

code segment:
-------------------------------------
strBEISql = "IF EXISTS(SELECT [" & sourceTempTable & "].Bundle FROM " & sourceTempTable & ")SELECT [" & sourceTempTable & "].Bundle INTO [" _
& userNameTable & "]" _
& "FROM [" & sourceTempTable & "];"

DoCmd.RunSQL strBEISql
-------------------------------------

does anyone know how to check to see if a column exists in access?

 

[gemma-the-husky]

try to retrieve any value from the column


code....

on error goto nocolumn
variable = dlookup("columnname","mytable")

'if the above test does NOT throw an error then the column/table was found

if the table/column does NOT exist it will trigger an error.

you need the onerror trap, because doing a dlookup on a non-existent column will otherwise crash the app

 

[PatrickJohnson]

also, (and i may be off base here) but i was under the impression you could not perform SELECT statements via vba since it has no way to handle results. am i wrong?

 

[Pat Hartman]

Use the fields of the tabledefs collection to determine if a field exists.