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

 

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 (11)

---

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.

---

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___