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.

[The Frog]

Hi Lyle,

Found out what the issue is: you cant return an array of user defined
data type, you can only return a single UDT from a function.

Bummer. I think what I will do is simply convert this to an array of
strings, have them comma or tab delimited, and return the array of
strings as a variant from the function. UDT's werent so useful here as
I would have hoped.

New code to come....

Cheers

The Frog

[The Frog]

And the answer is....(drumroll please....) : [and the crowd
goes wild]

Function TableStructures () As Variant

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 output() As String

Set db = CurrentDb
Count = 0
ID = 0

ReDim Preserve output(Count)
output(Count) = "ID,Name,Proper tyName,Property Type,PropertyVa lue,TFIR"
Count = Count + 1

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 output(Count)
output(Count) = ID
output(Count) = output(Count) & "," & td.Name
output(Count) = output(Count) & "," & pp.Name
output(Count) = output(Count) & "," & pp.Type
output(Count) = output(Count) & "," & pp.Value
output(Count) = output(Count) & "," & "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 output(Count)
output(Count) = ID
output(Count) = output(Count) & "," &
fd.Name
output(Count) = output(Count) & "," &
pp.Name
output(Count) = output(Count) & "," &
pp.Type
output(Count) = output(Count) & "," &
pp.Value
output(Count) = output(Count) & "," & "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 output(Count)
output(Count) = ID
output(Count) = output(Count) &
"," & ix.Name
output(Count) = output(Count) &
"," & pp.Name
output(Count) = output(Count) &
"," & pp.Type
output(Count) = output(Count) &
"," & pp.Value
output(Count) = output(Count) &
"," & "I"
Count = Count + 1
End Select
Next
'now grab the fields in the index itself
For Each fd In ix.Fields
ReDim Preserve output(Count)
output(Count) = ID
output(Count) = output(Count) & "," &
ix.Name
output(Count) = output(Count) & "," &
fd.Name
output(Count) = output(Count) & ","
output(Count) = output(Count) & ","
output(Count) = output(Count) & "," &
"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 output(Count)
output(Count) = ID
output(Count) = output(Count) & "," & rl.Name
output(Count) = output(Count) & "," & pp.Name
output(Count) = output(Count) & "," & pp.Type
output(Count) = output(Count) & "," & pp.Value
output(Count) = output(Count) & "," & "R"
Count = Count + 1
Next
For Each fd In rl.Fields
ReDim Preserve output(Count)
output(Count) = ID
output(Count) = output(Count) & "," & fd.Name
output(Count) = output(Count) & ","
output(Count) = output(Count) & ","
output(Count) = output(Count) & ","
output(Count) = output(Count) & "," & "F"
Count = Count + 1
For Each pp In fd.Properties
Select Case pp.Name
Case ("ForeignNam e")
ReDim Preserve output(Count)
output(Count) = ID
output(Count) = output(Count) & "," & fd.Name
output(Count) = output(Count) & "," & pp.Name
output(Count) = output(Count) & "," & pp.Type
output(Count) = output(Count) & "," & pp.Value
output(Count) = output(Count) & "," & "RFP"
Count = Count + 1
End Select
Next
Next
Next

TableStructures = output
End Function

now to do the other half of the problem, and make the code to read all
this and "rebuild" a database and all its bits..... ah well, a brief
moment of victory is better than no victory at all...
I will post the rebuild routine here as well. It may take me a day or
two to write it and make it actually work, but in the end we should end
up with two functions, one to grab a structural definition of the
tables and all associated objects, and the other to build them from the
definition.

Its actually pretty cool when you think that you could just send
someone a simple text email with a complete database structure and know
that they can build it in a matter of seconds at the other end. Cant
wait to show my boss this when it is done. I think I will do it with a
full database definition though, and cheat a little by using the
application.sav eastext and application.loa dfromtext functions for the
rest of the objects. I just cant be stuffed playing with DAO anymore.

Are you aware o´f any issues regarding the application.sav easstext and
application.loa dfromtext functions? I know that strictly speaking they
are not supported, but still, if they work...

Cheers

The Frog

[Lyle Fairfield]

"The Frog" <an************ **@eu.effem.com wrote in
news:11******** **************@ 73g2000cwn.goog legroups.com:

And the answer is....(drumroll please....) : [and the crowd
Its actually pretty cool when you think that you could just send
someone a simple text email with a complete database structure and know
that they can build it in a matter of seconds at the other end. Cant
wait to show my boss this when it is done. I think I will do it with a
full database definition though, and cheat a little by using the
application.sav eastext and application.loa dfromtext functions for the
rest of the objects. I just cant be stuffed playing with DAO anymore.

Are you aware o´f any issues regarding the application.sav easstext and
application.loa dfromtext functions? I know that strictly speaking they
are not supported, but still, if they work...

Cheers

The Frog

They have always worked flawlessy for me. Your work will add the missing
features, viz, the JET parts as text.

I suppose next you will write a utility that creates an Access database
from text and enables the user to create that text? Perhaps you could call
it "In the Beginning was the Word"?

--
Lyle Fairfield

[The Frog]

The thought had crossed my mind! A real genesis script for the Jet
engine. Its kind of what I am working on, but for a slightly different
purpose.

The goal of all of this is actually quite simple. What I am trying to
create is an access application that can run via the RunTime version,
and is able to be updated remotely with new structural information and
data.

By separating the structural from the data, particularly with the
tabledefs (oops...I still need to do the DB properties!), I am able to
add some very granular security and also minimise the data sent between
the host (probably me having to maintain it), and the remote users.
With some simple version control, and some VBA code to get everything
in check when the client application starts up, I can basically deploy
any data structure, to anybody, via simple text emails. Back this up
with incremental data also done via text, tack on some encryption so
that the data is safe from prying eyes, and you have a fully
deployable, offline tool that can handle any design that you wish, andd
can stay updated and under your control, with no increase in
infrastructure costs except the consumed bandwidth for sending and
receiving the emails.

My company runs a real shoestring IT group and infrastructure. To get
them to shell out for an off-the-shelf solution is a no go. It just
wont happen on any large scale, and I need to be able to feed data to
some 200 field personnel. This is the only way I could think of to do
it and not incurr extra costs. We have the developer version of Office
97, so I am legally able to deploy this type of thing, and the run time
files that are needed.

At the start of this I wished that there was an easier way, but now I
am really getting into the project and quite enjoying pushing things to
see how far I can take them. I had certainly never expected to be doing
this with Access 97, but then life has some strange turns...

I have also made the code to extract the rest of the database objects,
pretty easy stuff. Got the code written to send and receive emails
through Lotus Notes now too. So the comms are relatively under control.
Now I am building the prototype rebuild component for the DAO code.
Once I have the proof of concept done and operating, I will clean it
right up and develop the whole thing into a deployable app that can
self maintain and handle the data and structural changes. In the end I
envisage there being two versions of the database, the first is the
master where design changes can be made and "published" with version
control, and then the client version which receives the data, can
request updates and status of / to all objects and data.

I am hoping that the end result is an application that the users can
work with, request changes or new things, and can be given these new
things quickly and easily. At the moment they have nearly 20 different
data tools for different systems. That is in the way too hard basket
for most of them, so I am hoping to give them a simple "consolidat ed"
tool and view of their world. They dont need to know the ins and outs
of Oracle and SQL Server, or OLAP cubes, they just want to do their
jobs, and so I find myself creating this tool. Your average sales guy
or girl really isnt technologically savvy.

I think, in the end, if it really works the way I hope it will, I may
just publish all the code on this thread and make the lives of many
people much much easier. What manager wouldnt want to be able to ask
for a new report on the data, and have it the same day, and distributed
to everyone (if they have permissions to use it) around the world if
need be! Pretty cool for a bit of VBA code, some time, and a now
"outdated" desktop database engine.

I will keep you posted as to the progress.

Any thoughts? Am I completely mad? The line between brilliance and
insanity may be getting more blurred here by the minute..... :-)

Have a great weekend and I will speak to you next week.

Cheers

The Frog :)

[The Frog]

The thought had crossed my mind! A real genesis script for the Jet
engine. Its kind of what I am working on, but for a slightly different
purpose.

The goal of all of this is actually quite simple. What I am trying to
create is an access application that can run via the RunTime version,
and is able to be updated remotely with new structural information and
data.

By separating the structural from the data, particularly with the
tabledefs (oops...I still need to do the DB properties!), I am able to
add some very granular security and also minimise the data sent between
the host (probably me having to maintain it), and the remote users.
With some simple version control, and some VBA code to get everything
in check when the client application starts up, I can basically deploy
any data structure, to anybody, via simple text emails. Back this up
with incremental data also done via text, tack on some encryption so
that the data is safe from prying eyes, and you have a fully
deployable, offline tool that can handle any design that you wish, andd
can stay updated and under your control, with no increase in
infrastructure costs except the consumed bandwidth for sending and
receiving the emails.

My company runs a real shoestring IT group and infrastructure. To get
them to shell out for an off-the-shelf solution is a no go. It just
wont happen on any large scale, and I need to be able to feed data to
some 200 field personnel. This is the only way I could think of to do
it and not incurr extra costs. We have the developer version of Office
97, so I am legally able to deploy this type of thing, and the run time
files that are needed.

At the start of this I wished that there was an easier way, but now I
am really getting into the project and quite enjoying pushing things to
see how far I can take them. I had certainly never expected to be doing
this with Access 97, but then life has some strange turns...

I have also made the code to extract the rest of the database objects,
pretty easy stuff. Got the code written to send and receive emails
through Lotus Notes now too. So the comms are relatively under control.
Now I am building the prototype rebuild component for the DAO code.
Once I have the proof of concept done and operating, I will clean it
right up and develop the whole thing into a deployable app that can
self maintain and handle the data and structural changes. In the end I
envisage there being two versions of the database, the first is the
master where design changes can be made and "published" with version
control, and then the client version which receives the data, can
request updates and status of / to all objects and data.

I am hoping that the end result is an application that the users can
work with, request changes or new things, and can be given these new
things quickly and easily. At the moment they have nearly 20 different
data tools for different systems. That is in the way too hard basket
for most of them, so I am hoping to give them a simple "consolidat ed"
tool and view of their world. They dont need to know the ins and outs
of Oracle and SQL Server, or OLAP cubes, they just want to do their
jobs, and so I find myself creating this tool. Your average sales guy
or girl really isnt technologically savvy.

I think, in the end, if it really works the way I hope it will, I may
just publish all the code on this thread and make the lives of many
people much much easier. What manager wouldnt want to be able to ask
for a new report on the data, and have it the same day, and distributed
to everyone (if they have permissions to use it) around the world if
need be! Pretty cool for a bit of VBA code, some time, and a now
"outdated" desktop database engine.

I will keep you posted as to the progress.

Any thoughts? Am I completely mad? The line between brilliance and
insanity may be getting more blurred here by the minute..... :-)

Have a great weekend and I will speak to you next week.

Cheers

The Frog :)

[Lyle Fairfield]

The Frog wrote:

Am I completely mad?

Sure you are, just like every person who dared to dream and who dared
to try.

Why not just sit back with a beer and watch some television?

But, while you do remember that without some other mad person(s) there
would be no television.

[Terry Kreft]

.... and no beer !

--
--
Terry Kreft
"Lyle Fairfield" <ly***********@ aim.comwrote in message
news:11******** **************@ 16g2000cwy.goog legroups.com...

The Frog wrote:

>Am I completely mad?

Sure you are, just like every person who dared to dream and who dared
to try.

Why not just sit back with a beer and watch some television?

But, while you do remember that without some other mad person(s) there
would be no television.

[The Frog]

Just an update to all on the project here....still underway. Hasnt been
forgotten. Christmas got in the way, and well, I got too fat to reach
my keyboard from all that yummy christmas food.

Anyway, I am back at work and will attempt to finish the data IO and
versioning methodology over the next month. I have another project that
has to take priority for now, but it should only take a week or so to
accomplish.

Hope you had a great Christmas and New Years.

Hopefully posting some (useful) code soon.

Cheers The Frog