Can a source name in table definition is set to be a query ? 1

[maupiti]

Access 2003.
Can a source name in table definition is set to be a query,
such as shown below ?
Set Table_Definition = Dbs.CreateTableDef(Query_Name)

The code below does not have any error, but it also
did enter the loop. The Query_Name is equal to
"Query_CrossTab", and the Field_Name is equal to "LV".

////////////////////////////////////////////////

Private Sub Insert_Records_Into_Table()
If The_Field_Exist("Query_CrossTab", "LV") Then
A = A + B
'CALL INSERT THE RECORD
End If
End Sub

////////////////////////////////////////////////

Function The_Field_Exist(Query_Name, Field_Name) As Boolean
Dim Dbs As DAO.Database
Dim Table_Definition As DAO.TableDef
Dim Field_Loop As DAO.Field

Debug.Print "Query_Name = ", Query_Name
Debug.Print "Field_Name = ", Field_Name
Set Dbs = CurrentDb

Set Table_Definition = Dbs.CreateTableDef(Query_Name)
For Each Field_Loop In Table_Definition.Fields
If Field_Loop.Name = Field_Name Then
Debug.Print Field_Name; " exists in " &
Table_Definition.Name
Public_If_The_Field_Exist = True
Exit For
Else
Debug.Print "The field does not exist"
Public_If_The_Field_Exist = False
End If
Next

End Function

 

[PHV]

You may try this function (needs some error handling):

Function The_Field_Exist(Query_Name As String, Field_Name As String) As Boolean
Dim Dbs As DAO.Database
Dim Query_Definition As DAO.QueryDef
Dim Field_Loop As DAO.Field

Debug.Print "Query_Name = ", Query_Name
Debug.Print "Field_Name = ", Field_Name

Set Dbs = CurrentDb
Set Query_Definition = Dbs.QueryDefs(Query_Name)
For Each Field_Loop In Query_Definition.Fields
  If Field_Loop.Name = Field_Name Then
    Debug.Print Field_Name & " exists in " & Query_Definition.Name
    The_Field_Exist = True
    Exit Function
  End If
Next
Debug.Print "The field does not exist"
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[maupiti]

Thank you PHV (MIS).

 

[JerryKlmns]

maupiti

You could have both in one function

Function FieldExists(tbl_qdf As String, myField As String) As Boolean
On Error Resume Next
Dim RST As ADODB.Recordset
Dim eXists As String
Err.Clear
Set RST = CurrentProject.Connection.Execute("SELECT TOP 1 " & tbl_qdf & ".* FROM " & tbl_qdf)
eXists = RST.Fields(myField) & ""
If Err.Number = 0 Then
    FieldExists = True
Else
    FieldExists = False
End If
RST.Close
Set RST = Nothing
End Function