Answers
May 29, 2007 - 09:09 PM
Try
CurrentDB.TableDefs("Table").Fields("Field").Name
this will return the field name if the column exists. Otherwise it will throw an error.
Cheers
Peter
CurrentDB.TableDefs("Table").Fields("Field").Name
this will return the field name if the column exists. Otherwise it will throw an error.
Cheers
Peter
May 30, 2007 - 02:31 AM
There is nothing u can do in SQL, it has to be done via VBA.
quick way is as Peter suggests. But if u trap for errors then u need to make sure u get the right error number.
eg
On Error Resume Next
Debug.Print CurrentDb.TableDefs("MyTbl").Fields("MyField").Name
If Err.Number = 0 Then
Debug.Print "Field Exists"
ElseIf Err.Number = 3265 Then
Debug.Print "Field Does Not Exist"
Else
Debug.Print Err.Number, Err.Description
End If
The other way if u dont want to catch certain errors is to loop thru the field collection in the table then add it that way. U can add fields in vba as well, just in case u didnt want to bother with sql
eg
'This bit loops thru field collection and sets a flag
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim i As Integer
Dim bFound As Boolean
bFound = False
Set db = CurrentDb
Set tbl = db.TableDefs("MyTbl")
For i = 0 To tbl.Fields.Count - 1
'I do a lowercase comparison just in case
If LCase$(tbl.Fields(i).Name) = LCase$("MyField") Then bFound = True
Next i
Debug.Print "Found", bFound
'This bit adds that field. In this case, I am adding it as text size 20
Dim fld As DAO.Field
If bFound = False Then
Set fld = tbl.CreateField("MyField", DB_TEXT, 20)
tbl.Fields.Append fld
End If
quick way is as Peter suggests. But if u trap for errors then u need to make sure u get the right error number.
eg
On Error Resume Next
Debug.Print CurrentDb.TableDefs("MyTbl").Fields("MyField").Name
If Err.Number = 0 Then
Debug.Print "Field Exists"
ElseIf Err.Number = 3265 Then
Debug.Print "Field Does Not Exist"
Else
Debug.Print Err.Number, Err.Description
End If
The other way if u dont want to catch certain errors is to loop thru the field collection in the table then add it that way. U can add fields in vba as well, just in case u didnt want to bother with sql
eg
'This bit loops thru field collection and sets a flag
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim i As Integer
Dim bFound As Boolean
bFound = False
Set db = CurrentDb
Set tbl = db.TableDefs("MyTbl")
For i = 0 To tbl.Fields.Count - 1
'I do a lowercase comparison just in case
If LCase$(tbl.Fields(i).Name) = LCase$("MyField") Then bFound = True
Next i
Debug.Print "Found", bFound
'This bit adds that field. In this case, I am adding it as text size 20
Dim fld As DAO.Field
If bFound = False Then
Set fld = tbl.CreateField("MyField", DB_TEXT, 20)
tbl.Fields.Append fld
End If
Jun 03, 2007 - 10:29 PM
thanks guys.
I read that in the case of SQL Server it is actually possible, if you query the sys-tables. Anyway that option doesn't work in the access-case, so I guess I'll have to do it by code as you suggested.
I read that in the case of SQL Server it is actually possible, if you query the sys-tables. Anyway that option doesn't work in the access-case, so I guess I'll have to do it by code as you suggested.
Nov 27, 2007 - 11:19 PM
what is CurrentDb ?
i dont know how to declear this.
i dont know how to declear this.
Add New Comment