Hi Art,
I agree with John on this, letting your users define table structures is just building trouble for yourself.
I had a requirement similar to this many years ago when I used FoxPro for development. The way I did it was to define a table with a primary key, an Entity-Type key and a number of indexable text fields with generic names (field1, field2, etc.) into which the users could enter any type of data they wished. They couldn't alter the structure of the table, but depending on the 'Entity' type, they could label the fields whatever they wanted, thereby fulfilling their requirement to cater for many different types of data. The 'Entity' types were held in a lookup table which also stored the corresponding labels for the different fields. When the users opened the data entry form, the labels were filled out from the entity lookup table and any fields that hadn't a defined label were not displayed. This worked ok in FoxPro. I have never tried it in Access, but I don't see why it wouldn't work there also.
However, it requires a lot of thought to get the design right and a lot of discipline on the part of the users when setting up each type of entity. All forms and reports have to reference the Entity lookup table to populate their labels with the correct values, and there are also data conversion issues to account for, although Access handles a lot of this automatically and virtually any type of data can be stored in a text field. It also requires a lot of work to maintain if new forms and reports are required. If you're not fussy what the forms look like, you could just design a simple 2 column data entry form with the labels down the left. If you wanted to get a bit more fancy, you could store other field parameters in the Entity lookup table such a field width, colour, etc. and apply them on the fly when opening the form.
Andrew
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: 28 June 2017 15:56
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Create a table dynamically using VBA based on a form
Art-
As I've implied before, letting users build their own tables in your application database is insane.
Why can't the user(s) lay out for you what they need so that you can build it? Why allow them to build whatever they want on the fly?
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On Jun 28, 2017, at 4:47 PM, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
OK and Entity is what I call the user defined table. This entity could be Contacts, Vendors, Books, Projects, Property, etc. It could be just about anything a user can want to track data on. The user defines what they need to track and then adds these records to it. Eg.
Contact
John V 123 MAIN St Somewhere SD 55555 USA 605-555-5555 01/01/01
JohnVResume.DOC
JohnVBudget,XLS
SummerVacationPic.jpeg
Art L 777 Maple Ave Sioux Falls SD 57103 USA 605-555-9999 01/02/01
ArtsResume.DOC
ArtsInterviewNotes.doc
MiaWedding.gif
Just remember the entity is used to track user specified data and then associate specific documents to it.
With Warm Regards,
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265 Ext. 130
Fax (605)964-1070
"Anyone who claimed that old age had brought them patience was either lying or senile."
On Tue Jun 27 2017 16:35:58 GMT-0500 (Central Daylight Time), John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
OK, what is an "entity"?? And why would something new cause you to allow the user to control the database design?
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On Jun 27, 2017, at 9:28 PM, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
And around and around it goes. But this only works with these entities. We don't know what type of entity a user might come up with.
With Warm Regards,
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265 Ext. 130
Fax (605)964-1070
"Anyone who claimed that old age had brought them patience was either lying or senile."
On Tue Jun 27 2017 14:20:08 GMT-0500 (Central Daylight Time), John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Art-
This is Relational Design 101. How are Projects, Documents, Categories, Contacts, and Vendors related? Are Contacts and Vendors related to Projects or Documents? If Projects, and a Project can have many Contacts, then you may need another many to many linking table. If a Contact is related to one and only one Project and a Project can have many Contacts, then put the ProjectID in the Contacts table. Go through the same analysis for Vendors.
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On Jun 27, 2017, at 9:06 PM, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
OK. I understand what you are doing there but where do I track specific Project, contact, vendor, etc information?
With Warm Regards,
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265 Ext. 130
Fax (605)964-1070
"Anyone who claimed that old age had brought them patience was either lying or senile."
On Tue Jun 27 2017 13:55:21 GMT-0500 (Central Daylight Time), John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Art-
If it's documents you're categorizing, and a document can belong to only one category, then add a CategoryID field to the documents table. If a document can be in several categories, build a linking many to many table:
tblDocumentCategories
DocumentID
CategoryID
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On Jun 27, 2017, at 8:44 PM, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Yes it would describe all projects or they can add fields to it, There is a separate table for documents which tracks location and other specific document information. tblDocuments would contain the documents for a project (timelines, budget, etc.). Or if the entity is Contacts it would be first name, last name, address (field names) and the documents related would be like applications, licenses, etc.
This would be the contact table structure:
Table: tblContact Page: 1
Columns
Name Type Size
PKContactID Long Integer 4
CreatedDate Date With Time 8
ModifiedDate Date With Time 8
CreatorID Long Integer 4
ModifiedByUserID Long Integer 4
OwnerID Long Integer 4
OwningGroupID Long Integer 4
flFirstName Short Text 255
flLastName Short Text 255
flDesignation Short Text 255
flComapnyName Short Text 255
flAddress Long Text -
flPhoneNumber Short Text 255
flEmailID Short Text 255
Table Indexes
Name Number of Fields
PrimaryKey 1
Fields:
PKContactID Ascending
Sounds like your Idea for a category but be easier. Could you expand on that in more details. Example would be great. This is my document table.
Table: tblDocument Page: 1
Columns
Name Type Size
DocumentID Long Integer 4
DocTypeCode Long Integer 4
DocumentTitle Short Text 255
ListOrder Long Integer 4
CreatorID Long Integer 4
OwnerID Long Integer 4
OwningGroupID Long Integer 4
DocState Long Integer 4
CheckedOutFileName Short Text 255
LockHolderUserID Long Integer 4
MajorVersion Long Integer 4
MinorVersion Long Integer 4
CreatedDate Date With Time 8
ModifiedDate Date With Time 8
ModifiedByUserID Long Integer 4
VersionsLimit Long Integer 4
ExpiryDate Date With Time 8
MarkForDeletion Yes/No 1
With Warm Regards,
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265 Ext. 130
Fax (605)964-1070
"Anyone who claimed that old age had brought them patience was either lying or senile."
On Tue Jun 27 2017 13:32:04 GMT-0500 (Central Daylight Time), John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Art-
So, is the design of tblProject sufficient to describe all projects? Is a Project synonymous with a set of documents? Is there a separate tblDocuments? Why not simply have a tblCategories to which users can add category types, and then associate those with each document or project? You could even make the association many to many so that a given project or document can be in multiple categories.
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On Jun 27, 2017, at 7:20 PM, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
I am creating a Document Managememt System for my organization. One of the specification is they want to be able different categories of entities (eg. Projects, Contacts, Vendors, Books, Training, etc. And be able to associate documents to these entities. Once the entity is created they will be add records to it, ergo the table on the fly.
Here is a sample of the Project table:
Table: tblProject Page: 1
Columns
Name Type Size
PKProject Long Integer 4
CreatedDate Date With Time 8
ModifiedDate Date With Time 8
CreatorID Long Integer 4
ModifiedByUserID Long Integer 4
RecordSeriesID Long Integer 4
StackStatus Long Integer 4
MetadataXML Long Text -
MarkForDeletion Yes/No 1
flName Short Text 255
flStartDate Date With Time 8
flEndDate Date With Time 8
flProjectManager Short Text 255
flProjectDetails Long Text -
flStatus Short Text 255
PKProject will need to be customized by the entity name. So if the entity name is Contact then it would be PKContact which needs to become the primary key for the table. Remember the user will be enter the structure into a form.
With Warm Regards,
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265 Ext. 130
Fax (605)964-1070
"Anyone who claimed that old age had brought them patience was either lying or senile."
On Tue Jun 27 2017 11:59:17 GMT-0500 (Central Daylight Time), John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Art-
Please describe in more detail what it is you're trying to implement.
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On Jun 27, 2017, at 6:34 PM, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
So how would you handle this specification?
With Warm Regards,
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265 Ext. 130
Fax (605)964-1070
"Anyone who claimed that old age had brought them patience was either lying or senile."
On Tue Jun 27 2017 10:23:02 GMT-0500 (Central Daylight Time), John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Art-
You're letting users define tables in the database? That's a fool's errand!
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
(Paris, France)
On Jun 27, 2017, at 4:47 PM, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Not really. The specification is that the object can be any category, contact, vendor, project, etc. So I need a table to track the data entered for each of these objects. Then the user will be able to link associated documents to these different objects. The key here we will never know the object that a user will come up with.
With Warm Regards,
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265 Ext. 130
Fax (605)964-1070
"Anyone who claimed that old age had brought them patience was either lying or senile."
On Tue Jun 27 2017 00:43:14 GMT-0500 (Central Daylight Time), Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of 'Graham Mandeno' graham@mandeno.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Tuesday, June 27, 2017 3:18:20 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Create a table dynamically using VBA based on a form
How can I create a table dynamically using VBA. I found the code to create the table but the gotcha is that the table name and fields names are coming from a form.I have a form called sfrmObjectTypeFields which contains the field names which I need to transfer into the VBA CREATE TABLE statement. Any ideas?
Posted by: Andrew Wilson <andrew@adwsystems.co.uk>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (19) |
Tidak ada komentar:
Posting Komentar