make table query -- preserving table control properties

[Guest]

i run A2K....when i join a table with a query in order to make a table, i
find that the fields/controls which were used in the table the query used
have lost some of their 'properties' for lack of a better word, i.e. a date
field no longer has the 'input format' i used in the original table which
appears in the join. and so forth....so, seeking to avoid the hassle of hard
coding this all over again my question is, is there a way to preserve those
properties of the controls going into this?

 

[Allen Browne]

Correct. A Make Table query will not copy the existing field properties into
the new table. This is one of the severe limitations on when Make Table
queries are any use.

Alternatives:
a) If you are deleting an existing table so you can create it again, delete
its records and use an Append query instead. This leaves the correct
structure and field properties in place. Deleting the records is just a
matter of:
dbEngine(0)(0).Execute "DELETE FROM Table1;", dbFailOnError

b) If it is not an attached table, you can duplicate it like this:
Docmd.CopyObject ,"MyNewTable", acTable, "MyOldTable"

c) Use DAO to programmatically set the properties of the Fields in the
TableDef. While this is tedious, it is really the only way to set properties
such as Format, Caption, and DisplayControl.

Many of these properties do not exist until created, so you need a routine
that creates them if they do not exist, and sets them if they do. The
example below does that. You could call it like this:
Call SetPropertyDAO(Currentdb.TableDefs("Table1").Fields("Field1"),
"Format", dbText, "Short Date")

Function SetPropertyDAO(obj As Object, _
strPropertyName As String, _
intType As Integer, _
varValue As Variant, _
Optional strErrMsg As String) As Boolean
On Error GoTo ErrHandler
'Purpose: Set a property for an object, creating if necessary.
'Arguments: obj = the object whose property should be set.
' strPropertyName = the name of the property to set.
' intType = the type of property (needed for creating)
' varValue = the value to set this property to.
' strErrMsg = string to append any error message to.

If HasProperty(obj, strPropertyName) Then
obj.Properties(strPropertyName) = varValue
Else
obj.Properties.Append obj.CreateProperty(strPropertyName, intType,
varValue)
End If
SetPropertyDAO = True

ExitHandler:
Exit Function

ErrHandler:
strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & " not set to
" & varValue & ". Error " & Err.Number & " - " & Err.Description & vbCrLf
Resume ExitHandler
End Function

Public Function HasProperty(obj As Object, strPropName As String) As Boolean
'Purpose: Return true if the object has the property.
Dim varDummy As Variant

On Error Resume Next
varDummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function

 

[Guest]

hi allen,

that's probably a bit more than i can chew on but i'll definitely give it a
lengthy study.

thanks, much!

-ted