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

 

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

---

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

.

__,_._,___