Selasa, 27 Juni 2017

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


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.


.

__,_._,___

Tidak ada komentar:

Posting Komentar