Rabu, 28 Juni 2017

Re: [MS_AccessPros] Create a table dynamically using VBA based on a form

 

Art-


If you let users build their own tables, then you would have to modify the front ends to build the forms and reports on those new tables anyway.  Sit down with your users and get a better handle on what it is they want overall, then build that into your app from the beginning.  Put on your systems analyst hat.

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 5:30 PM, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



OK. I get it. So I will make it a admin function (meaning me). So how would be the best way to handle it. I could just create the table and then have to change 30 front-ends which could be time consuming each time a user wants a different entity added.


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 Wed Jun 28 2017 10:11:41 GMT-0500 (Central Daylight Time), John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:


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 
http://www.viescas.com/ 
(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 
http://www.viescas.com/ 
(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 
http://www.viescas.com/ 
(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 
http://www.viescas.com/ 
(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 
http://www.viescas.com/ 
(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 
http://www.viescas.com/ 
(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 
http://www.viescas.com/ 
(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:



Art, I'm not sure but it looks like you are re-inventing the wheel. 

Duane

Get Outlook for iOS
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
 


Hi Art

Have you heard of DDL (Data Definition Language)?  It is a subset of SQL, and so you can execute a DDL "query" in the same way you execute a normal SQL command to create and modify tables and other database objects.

Here is an article to get you started:
https://support.office.com/en-us/article/Create-or-modify-tables-or-indexes-by-using-a-data-definition-query-d935e129-229b-48d8-9f2d-1d4ee87f418e

I assume, since you say that the table and field names are "coming from a form", that they are actually stored in one or more tables.  Therefore, you can open a Recordset that returns the fields, one at a time, and loop through it creating your SQL/DDL string.  Something like this:

Set db = CurrentDb
Set rs = db.OpenRecordset( "your SELECT statement here" )
strDDL = "CREATE TABLE " & strTableName & "("
Do Until rs.EOF
    If rs.AbsolutePosition > 0 Then strDDL = strDDL & ", "
    strDDL = strDDL & rs!FieldName & " " & rs!FieldType
Next
strDDL = strDDL & ")"
CurrentDb.Execute strDDL, dbFailOnError

You can also add other qualifiers to each line, such as NOT NULL (i.e. Required), UNIQUE (i.e. No Duplicates), and PRIMARY KEY and other CONSTRAINTs.

Best wishes,
Graham

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] 
Sent: Tuesday, 27 June 2017 09:43
To: MS_Access_Professionals@yahoogroups.com
Subject: [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?

 

Thank you,

 

Art Lorenzini    

Sioux Falls, SD




























__._,_.___
Posted by: John Viescas <johnv@msn.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (20)
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

.

__,_._,___
Diposting oleh di

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)