DAO - which properties apply to each field type?

[The Frog]

Hello everyone,

I am working on an application that can build database objects in MS
Access from text files. I suppose you could call it a backup and
restore type routine.

Accessing the fields, tables, relationships, and indexes is no issue
for me via DAO code. The issue I have is that I am not sure which
properties are actually necessary / available to set from code for each
possible type of field. I have looked fo´r a reference on this, but
cannot find one for specifically this task.

What I am looking for is a simple "grid" even, that would have Jet
Field Properties along one side of the grid, and along the other the
field types (eg dbBoolean, dbInteger, etc...), and marking off which
ones apply to which field type.

Does anyone have this type of information or where I can get it? It
would save me many hours of playing around.

Cheers and Thanks

The Frog.

[Wayne Gillespie]

On 6 Dec 2006 06:46:37 -0800, "The Frog" <an************ **@eu.effem.com wrote:

>Hello everyone,

I am working on an application that can build database objects in MS
Access from text files. I suppose you could call it a backup and
restore type routine.

Accessing the fields, tables, relationships, and indexes is no issue
for me via DAO code. The issue I have is that I am not sure which
properties are actually necessary / available to set from code for each
possible type of field. I have looked fo´r a reference on this, but
cannot find one for specifically this task.

What I am looking for is a simple "grid" even, that would have Jet
Field Properties along one side of the grid, and along the other the
field types (eg dbBoolean, dbInteger, etc...), and marking off which
ones apply to which field type.

Does anyone have this type of information or where I can get it? It
would save me many hours of playing around.

Cheers and Thanks

The Frog.

You can save many more hours of playing around by going to -

http://www.ffdba.com/downloads/Save_...ts_As_Text.htm
Wayne Gillespie
Gosford NSW Australia

[The Frog]

Hi Wayne,

Appreciate the idea, but the link doesnt work. Did you type the link
correctly?

I have several of these types of utilities, but mostly they ignore the
field properties. I need to capture the field properties to do this for
our application. We have a large number of dependencies built on this
application, and I need to be absolutely certain that I have exactly
everything that is needed, no guesswork or possible oversights.

Any chance you might be able to re-post / check the link you provided?

[Lyle Fairfield]

"The Frog" <an************ **@eu.effem.com wrote in
news:11******** **************@ l12g2000cwl.goo glegroups.com:

Hi Wayne,

Appreciate the idea, but the link doesn’t work. Did you type the link
correctly?

The link (http://www.ffdba.com/downloads/Get_Object_Names.htm) works but
this does not do what you want. [Thanks, Wayne for mentioning it.]

You mentioned backup; I think you want documentation.
While the utility mentioned creates text manifestations of all Access and
VBA objects it does not do so with JET objects, which are simply backed-
up.

If I were doing something such as (I think) you want to do I would use
ADO. I realize that those unfamiliar with ADO say that it will do nothing
that DAO won't do and I agree that after the first few hundred advantages
it's difficult to find more; but this is one of the first few hundred.

If you go to
http://www.ffdba.com/columns.asp

and click All and then Submit you will get an idea of the kind of
information that ADO can provide simply and quickly about Tables. (This
is a dynamic page; that is it gets the information anew each time it is
loaded).

If you go to
http://www.ffdba.com/procedures.asp

you will see how ADO can list all queries (sprocs), views etc.

The pages you will see (if you can click twice on the links, that is,)
are created with ASP/Javascript and ADO and reference an MS-SQL database.
But all the procedures are entirely doable with Access, VBA and a JET
Database. Of course, instead of presenting the information as an HTML
page, one is likely to save it to a file.

http://www.ffdba.com/downloads/Get_Object_Names.htm is not a complete
solution for this but it may give you a start.

Just the idea of looping through DAO definitions to do this is painful to
me. ADO will give you the information you need instantly and painlessly.

--
Lyle Fairfield

from http://msdn.microsoft.com/library/de...l=/library/en-
us/dnmdac/html/data_mdacroadma p.asp

Obsolete Data Access Technologies
Obsolete technologies are technologies that have not been enhanced or
updated in several product releases and that will be excluded from future
product releases. Do not use these technologies when you write new
applications. When you modify existing applications that are written
using these technologies, consider migrating those applications to
ADO.NET.
The following components are considered obsolete:
....
Data Access Objects (DAO): DAO provides access to JET (Access) databases.
This API can be used from Microsoft Visual Basic®, Microsoft Visual C++®,
and scripting languages. It was included with Microsoft Office 2000 and
Office XP. DAO 3.6 is the final version of this technology. It will not
be available on the 64-bit Windows operating system.
.....

[The Frog]

Thanks once again Lyle,

I originally would have approached the situation from an ADO / ADOX
point of view, bet there remains one problem with this, or at least not
one I know how to solve:

- There are a number of "user defined" properties (UDP's) that are
ascribed to the fields and tables in the source database. Inside these
UDP's are stored (cryptographica lly) some cryptographic keys, which
allow a user (if they have a key) to access the data in the field or
table as the case may be. This works a bit similarly to how EFS works
for files in Win 2K or newer. There is a UDP based on the users
identifier for the application, and it is appended as a property to the
table or field. This then has a value set which corresponds to the
cryptographical ly stored value of the key which is in turn used to
decrypt the data stored in either a table or field. This is used
becuase of the level of granularity it gives, and is much less
susceptable to attacks on workgroup files etc... It also makes the
database much more portable in the sense of application deployment as
you really need nothing special.

The need for the DAO code is to actually allow the transfer of
structures (meta-data) between the various remote users of the
application and the "server" which holds the definitive copy of the
information. So, for example, if a new table is built, and only
intended for some users, then that table structure and the defined
UDP's need to be sent, via a text method, to the remote users.

The catch is two fold:
1/ We may not know which users are allowed which tables / fields, and
in fact this may change from time to time even for existing structures
- therefore we dont the the property name of the UDP.
2/ The cryptography algorythm uses the other other properties
associated with the table / field as part of its authentication
process, however I am told that it doesnt depend on fields that cannot
be set programatically - due to the need to occasionally change keys
etc.

I am not sure how to approach this from an ADOX perspective and
guarantee that all the properties have been correctly read, copied (to
text), and then are able to be set again in a destination (target)
database. I am aware that this is entirely possible with DAO. If,
however, there is an ADOX method of doing exactly the same thing, then
I am all ears. As of writing this I am of the belief (researched but
willing to change) that this is not actually possible at this level of
granularity with ADOX.

Can it be confirmed that the properties collection of a field / table
etc in ADOX exactly matches the properties collection (in terms of
information content and programability) of field / table in DAO for a
Jet database?

I have been unable to find a confirmation of this, or a similar
"reference" stating each property that can be set / programmed for each
field type. Do you have anything that may reference this issue
definitivley?

The Frog

[Lyle Fairfield]

The Frog wrote:

Thanks once again Lyle,

I originally would have approached the situation from an ADO / ADOX
point of view, bet there remains one problem with this, or at least not
one I know how to solve:

- There are a number of "user defined" properties (UDP's) that are
ascribed to the fields and tables in the source database. Inside these
UDP's are stored (cryptographica lly) some cryptographic keys, which
allow a user (if they have a key) to access the data in the field or
table as the case may be. This works a bit similarly to how EFS works
for files in Win 2K or newer. There is a UDP based on the users
identifier for the application, and it is appended as a property to the
table or field. This then has a value set which corresponds to the
cryptographical ly stored value of the key which is in turn used to
decrypt the data stored in either a table or field. This is used
becuase of the level of granularity it gives, and is much less
susceptable to attacks on workgroup files etc... It also makes the
database much more portable in the sense of application deployment as
you really need nothing special.

The need for the DAO code is to actually allow the transfer of
structures (meta-data) between the various remote users of the
application and the "server" which holds the definitive copy of the
information. So, for example, if a new table is built, and only
intended for some users, then that table structure and the defined
UDP's need to be sent, via a text method, to the remote users.

The catch is two fold:
1/ We may not know which users are allowed which tables / fields, and
in fact this may change from time to time even for existing structures
- therefore we dont the the property name of the UDP.
2/ The cryptography algorythm uses the other other properties
associated with the table / field as part of its authentication
process, however I am told that it doesnt depend on fields that cannot
be set programatically - due to the need to occasionally change keys
etc.

I am not sure how to approach this from an ADOX perspective and
guarantee that all the properties have been correctly read, copied (to
text), and then are able to be set again in a destination (target)
database. I am aware that this is entirely possible with DAO. If,
however, there is an ADOX method of doing exactly the same thing, then
I am all ears. As of writing this I am of the belief (researched but
willing to change) that this is not actually possible at this level of
granularity with ADOX.

Can it be confirmed that the properties collection of a field / table
etc in ADOX exactly matches the properties collection (in terms of
information content and programability) of field / table in DAO for a
Jet database?

I have been unable to find a confirmation of this, or a similar
"reference" stating each property that can be set / programmed for each
field type. Do you have anything that may reference this issue
definitivley?

The Frog

I believe that ADO and ADOX can find all Access properties of tables

DatasheetBackCo lor, DatasheetCellsE ffect, DatasheetFontHe ight,
DatasheetFontIt alic, DatasheetFontNa me, DatasheetFontUn derline,
DatasheetFontWe ight, DatasheetForeCo lor, DatasheetGridli nesBehavior,
DatasheetGridli nesColor, Description, FrozenColumns, RowHeight,
ShowGrid

but I don't know of a way for either to find (DAO) UDPs.

[The Frog]

I have done some further research based on what we have been
discussing, and the ADOX cannot actually reach this data (met data) it
seems, as it is a purely specific JET / DAO thing. It appears that ADOX
is unable to create properties that otherwise do not already exist.
This seems a little odd to me, but then again that JET for you.

There is also a problem working with ADO when using Access 97 (GOD help
me we are still using it as a corporate standard), and that is ADO was
released after the fact so to speak, and as such you are unable to
create a connection easily directly inside the application you are
working from like you can in DAO. (eg in DAO you have the currentdb()
function for database objects, but in Access97 the ADO "currentproject "
type of approach simply doesnt work as its just not there and you end
up with an error, strange database locks for workarounds, etc...)

Unfortunately it looks like this is a pure DAO based job, and I am just
going to have to play with the fields and figure out the properties
that can and cannot be set for each field type, and then try to
reproduce the necessary parts in the target database.

When I eventually finish the code for this ***expletive deleted*** task
I will post it here so that if anyone else needs it they can have the
benefit of my pain and lost weekend......

Thanks for trying to help with this, it is appreciated. Unfortunately
it is a bit more complex than originally anticipated so it may take a
bit to get it right.

Thanks for your help

Cheers

The Frog

[Lyle Fairfield]

The Frog wrote:

I have done some further research based on what we have been
discussing, and the ADOX cannot actually reach this data (met data) it
seems, as it is a purely specific JET / DAO thing. It appears that ADOX
is unable to create properties that otherwise do not already exist.
This seems a little odd to me, but then again that JET for you.

There is also a problem working with ADO when using Access 97 (GOD help
me we are still using it as a corporate standard), and that is ADO was
released after the fact so to speak, and as such you are unable to
create a connection easily directly inside the application you are
working from like you can in DAO. (eg in DAO you have the currentdb()
function for database objects, but in Access97 the ADO "currentproject "
type of approach simply doesnt work as its just not there and you end
up with an error, strange database locks for workarounds, etc...)

Unfortunately it looks like this is a pure DAO based job, and I am just
going to have to play with the fields and figure out the properties
that can and cannot be set for each field type, and then try to
reproduce the necessary parts in the target database.

When I eventually finish the code for this ***expletive deleted*** task
I will post it here so that if anyone else needs it they can have the
benefit of my pain and lost weekend......

Thanks for trying to help with this, it is appreciated. Unfortunately
it is a bit more complex than originally anticipated so it may take a
bit to get it right.

Thanks for your help

Cheers

The Frog

Good Luck! ADO with the old is not so great. When I first tried ADO in
Access many years ago it made a hard disk direct write every time data
was saved, and I mean direct; there was an audible click form the hard
drive and then some whirring and a pause of several seconds.

[The Frog]

Hi Lyle,

Got the bulk of the hard work done. I have managed to figure out all
the DAO required code for handling the table structures, indexes,
fields, and relationships, but now I am stuck on something that doesnt
seem to make any sense. I cant return the data in the function I wrote
due to a Type Mismatch issue.

I used a User defined data type to handle the information inside the
function. This seemed the easiest way to collect all the necessary data
and house it in the right format and structure. The function creates an
array of the user defined data type, populates it as it goes, and then
it is supposed to return that array to the calling procedure. BUT, it
wont return the data. I cant figure out why - I have done this many
times before, but maybe I am just missing something really obvious.

Would you mind having a quick look to see what I have missed. The DAO
code works fine, I just cant seem to return the info!

The code is below: (I placed it in a new standard module in Access 97)

Private Type ObjectPropertie s
TFIR As String
ID As Integer
Name As String
PropertyName As String
PropertyType As Integer
PropertyValue As Variant
End Type

Function TableStructures () As ObjectPropertie s

Dim db As DAO.Database
Dim td As DAO.TableDef
Dim pp As DAO.Property
Dim fd As DAO.Field
Dim ix As DAO.Index
Dim rl As DAO.Relation

Dim Count As Integer
Dim ID As Integer
Dim DataArray() As ObjectPropertie s

Set db = CurrentDb
Count = 0
ID = 0

For Each td In db.TableDefs
ID = ID + 1
Select Case td.Name
Case ("MSysACEs")
Case ("MSysModule s")
Case ("MSysModules2" )
Case ("MSysObject s")
Case ("MSysQuerie s")
Case ("MSysRelations hips")
Case Else
'first step is to grab the relevant table properties and
'discard the rubbish ones. Also need to pick up the user
'and application defined properties and save them too
For Each pp In td.Properties
Select Case pp.Name
Case ("ConflictTable ")
Case ("DateCreate d")
Case ("LastUpdate d")
Case ("RecordCoun t")
Case ("ReplicaFilter ")
Case Else
ReDim Preserve DataArray(Count )
DataArray(Count ).ID = ID
DataArray(Count ).Name = td.Name
DataArray(Count ).PropertyName = pp.Name
DataArray(Count ).PropertyType = pp.Type
DataArray(Count ).PropertyValue = pp.Value
DataArray(Count ).TFIR = "T"
Count = Count + 1
End Select
Next
'now it is necessary to get the field definitions for this
table
'and store the relevant properties, discard the rubbish and
'collect also the application and user defined properties
For Each fd In td.Fields
For Each pp In fd.Properties
Select Case pp.Name
Case ("DataUpdatable ")
Case ("FieldSize" )
Case ("ForeignNam e")
Case ("OriginalValue ")
Case ("SourceFiel d")
Case ("SourceTabl e")
Case ("ValidateOnSet ")
Case ("Value")
Case ("VisibleValue" )
Case Else
ReDim Preserve DataArray(Count )
DataArray(Count ).ID = ID
DataArray(Count ).Name = fd.Name
DataArray(Count ).PropertyName = pp.Name
DataArray(Count ).PropertyType = pp.Type
DataArray(Count ).PropertyValue = pp.Value
DataArray(Count ).TFIR = "F"
Count = Count + 1
End Select 'this is the end select on the field
properties
Next 'this cycles to the next field property
Next 'this cycles to the next field
'grab the indexes for the table here and store them in the
DataArray
'first get the index and its properties
'then get the fields for the index itself
For Each ix In td.Indexes
Select Case ix.Foreign 'need to do foreign indexes via
relations
Case ("True")
Case Else
If Left(ix.Name, 1) <"{" Then 'ignore the
GUID indexes
For Each pp In ix.Properties
Select Case pp.Name
Case ("DistinctCount ")
Case Else
ReDim Preserve DataArray(Count )
DataArray(Count ).ID = ID
DataArray(Count ).Name = ix.Name
DataArray(Count ).PropertyName =
pp.Name
DataArray(Count ).PropertyType =
pp.Type
DataArray(Count ).PropertyValue
= pp.Value
DataArray(Count ).TFIR = "I"
Count = Count + 1
End Select
Next
'now grab the fields in the index itself
For Each fd In ix.Fields
ReDim Preserve DataArray(Count )
DataArray(Count ).ID = ID
DataArray(Count ).Name = ix.Name
DataArray(Count ).PropertyName = fd.Name
'DataArray(Coun t).PropertyType =
fd.Type
'DataArray(Coun t).PropertyValu e =
fd.Value
DataArray(Count ).TFIR = "IF"
Count = Count + 1
Next 'cycles the fields
End If 'end of the if for the index type
selection
End Select 'end of the selection of foreign key index
or not
Next 'this cycles through the indexes themselves
End Select 'this is the end select on the tabledefs themselves
Next 'this cycles to the next tabledef

'all the tables and fields and indexes are now complete, time to grab
the relations between tables
'note that the ID variable and the Count variable are still operating

For Each rl In db.Relations
ID = ID + 1
For Each pp In rl.Properties
ReDim Preserve DataArray(Count )
DataArray(Count ).ID = ID
DataArray(Count ).Name = rl.Name
DataArray(Count ).PropertyName = pp.Name
DataArray(Count ).PropertyType = pp.Type
DataArray(Count ).PropertyValue = pp.Value
DataArray(Count ).TFIR = "R"
Count = Count + 1
Next
For Each fd In rl.Fields
ReDim Preserve DataArray(Count )
DataArray(Count ).ID = ID
DataArray(Count ).Name = fd.Name
DataArray(Count ).TFIR = "F"
Count = Count + 1
For Each pp In fd.Properties
Select Case pp.Name
Case ("ForeignNam e")
ReDim Preserve DataArray(Count )
DataArray(Count ).ID = ID
DataArray(Count ).Name = fd.Name
DataArray(Count ).PropertyName = pp.Name
DataArray(Count ).PropertyType = pp.Type
DataArray(Count ).PropertyValue = pp.Value
DataArray(Count ).TFIR = "RFP"
Count = Count + 1
End Select
Next
Next
Next

TableStructures = DataArray

End Function

[Lyle Fairfield]

The Frog wrote:

Hi Lyle,
Private Type ObjectPropertie s
TFIR As String
ID As Integer
Name As String
PropertyName As String
PropertyType As Integer
PropertyValue As Variant
End Type

Function TableStructures () As ObjectPropertie s

Dim DataArray() As ObjectPropertie s

TableStructures = DataArray

My guess:
DataArray is an Array of ObjectPropertie s;
TablesStructure s is an Instance of (one) ObjectPropertie s

So one cannot be assigned to the other as they are different types.

My suggestion:
Dim DataArray() As Variant