By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,397 Members | 1,425 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,397 IT Pros & Developers. It's quick & easy.

DAO - which properties apply to each field type?

P: n/a
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 for 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.

Dec 6 '06 #1
Share this Question
Share on Google+
17 Replies


P: n/a
On 6 Dec 2006 06:46:37 -0800, "The Frog" <an**************@eu.effem.comwrote:
>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 for 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
Dec 6 '06 #2

P: n/a
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?

Dec 6 '06 #3

P: n/a
"The Frog" <an**************@eu.effem.comwrote in
news:11**********************@l12g2000cwl.googlegr oups.com:
Hi Wayne,

Appreciate the idea, but the link doesnt 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_mdacroadmap.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.
.....
Dec 6 '06 #4

P: n/a
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 (cryptographically) 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
cryptographically 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

Dec 8 '06 #5

P: n/a
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 (cryptographically) 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
cryptographically 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

DatasheetBackColor, DatasheetCellsEffect, DatasheetFontHeight,
DatasheetFontItalic, DatasheetFontName, DatasheetFontUnderline,
DatasheetFontWeight, DatasheetForeColor, DatasheetGridlinesBehavior,
DatasheetGridlinesColor, Description, FrozenColumns, RowHeight,
ShowGrid

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

Dec 8 '06 #6

P: n/a
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

Dec 8 '06 #7

P: n/a
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.

Dec 8 '06 #8

P: n/a
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 ObjectProperties
TFIR As String
ID As Integer
Name As String
PropertyName As String
PropertyType As Integer
PropertyValue As Variant
End Type

Function TableStructures() As ObjectProperties

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 ObjectProperties

Set db = CurrentDb
Count = 0
ID = 0

For Each td In db.TableDefs
ID = ID + 1
Select Case td.Name
Case ("MSysACEs")
Case ("MSysModules")
Case ("MSysModules2")
Case ("MSysObjects")
Case ("MSysQueries")
Case ("MSysRelationships")
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 ("DateCreated")
Case ("LastUpdated")
Case ("RecordCount")
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 ("ForeignName")
Case ("OriginalValue")
Case ("SourceField")
Case ("SourceTable")
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(Count).PropertyType =
fd.Type
'DataArray(Count).PropertyValue =
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 ("ForeignName")
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

Dec 14 '06 #9

P: n/a
The Frog wrote:
Hi Lyle,
Private Type ObjectProperties
TFIR As String
ID As Integer
Name As String
PropertyName As String
PropertyType As Integer
PropertyValue As Variant
End Type

Function TableStructures() As ObjectProperties
Dim DataArray() As ObjectProperties
TableStructures = DataArray
My guess:
DataArray is an Array of ObjectProperties;
TablesStructures is an Instance of (one) ObjectProperties

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

My suggestion:
Dim DataArray() As Variant

Dec 14 '06 #10

P: n/a
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

Dec 15 '06 #11

P: n/a
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,PropertyName,PropertyType,PropertyValue,T FIR"
Count = Count + 1

For Each td In db.TableDefs
ID = ID + 1
Select Case td.Name
Case ("MSysACEs")
Case ("MSysModules")
Case ("MSysModules2")
Case ("MSysObjects")
Case ("MSysQueries")
Case ("MSysRelationships")
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 ("DateCreated")
Case ("LastUpdated")
Case ("RecordCount")
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 ("ForeignName")
Case ("OriginalValue")
Case ("SourceField")
Case ("SourceTable")
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 ("ForeignName")
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.saveastext and application.loadfromtext functions for the
rest of the objects. I just cant be stuffed playing with DAO anymore.

Are you aware of any issues regarding the application.saveasstext and
application.loadfromtext functions? I know that strictly speaking they
are not supported, but still, if they work...

Cheers

The Frog

Dec 15 '06 #12

P: n/a
"The Frog" <an**************@eu.effem.comwrote in
news:11**********************@73g2000cwn.googlegro ups.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.saveastext and application.loadfromtext functions for the
rest of the objects. I just cant be stuffed playing with DAO anymore.

Are you aware of any issues regarding the application.saveasstext and
application.loadfromtext 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
Dec 15 '06 #13

P: n/a
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 "consolidated"
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 :)

Dec 15 '06 #14

P: n/a
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 "consolidated"
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 :)

Dec 15 '06 #15

P: n/a
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.

Dec 15 '06 #16

P: n/a
.... and no beer !

--
--
Terry Kreft
"Lyle Fairfield" <ly***********@aim.comwrote in message
news:11**********************@16g2000cwy.googlegro ups.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.

Dec 20 '06 #17

P: n/a
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

Jan 12 '07 #18

This discussion thread is closed

Replies have been disabled for this discussion.