Selasa, 27 Juni 2017

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

 

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: Art Lorenzini <dbalorenzini@yahoo.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (8)
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)