Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > access > answers

Creating A Table With More Properties Than Fieldname, Length, Type

Member
  
Join Date: Apr 2007
Posts: 72
#1: Apr 25 '07
I have a database, where I need to create a table if it doesn't exist. This is my code at the moment:

Expand|Select|Wrap|Line Numbers
  1. Sub CreateCwS() 
  2.  
  3.     Dim daotbl As DAO.TableDef 
  4.  
  5.     If Not daotbl.Name = tblCwS Then 
  6.  
  7.         DoCmd.SetWarnings False 
  8.  
  9.         Dim daodb As DAO.Database 
  10.         Dim daotblCwS As DAO.TableDef 
  11.         Dim daofldCNum As DAO.Field 
  12.         Dim daofldSNum As DAO.Field 
  13.         Dim daofldCCanEnt As DAO.Field 
  14.         Dim daoiCNum As DAO.Index 
  15.         Dim daoiSNum As DAO.Index 
  16.         Dim daoiCCanEnt As DAO.Index 
  17.  
  18.         Set daodb = CurrentDb() 
  19.         Set daotblCwS = daodb.CreateTableDef("tblCwS") 
  20.  
  21.         Set daofldCNum = daotblCwS.CreateField("CNum", dbLong) 
  22.         Set daofldSNum = daotblCwS.CreateField("SNum", dbText, 5) 
  23.         Set daofldCCanEnt = daotblCwS.CreateField("CCanEnt", dbLong) 
  24.  
  25.         daotblCwS.Fields.Append daofldCNum 
  26.         daotblCwS.Fields.Append daofldSNum 
  27.         daotblCwS.Fields.Append daofldCCanEnt 
  28.         daodb.TableDefs.Append daotblCwS 
  29.  
  30.         DoCmd.RunSQL "INSERT INTO tblCwS SELECT tblCen.CNum, tblSub.SNum FROM tblCen, tblSub;" 
  31.  
  32.     End If 
  33.  
  34. End Sub 
  35.  
That code does create a table, but it only creates a table with field names, lengths, and types. How do I create a table with more field properties set using VBA? In Visual FoxPro, there is the possibility of using the following code, made using with the help of this site:

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE
  2.  tblCwS (CNum n (5 , 0)
  3.   NOT NULL 
  4.   CHECK Between 10000 And 79999 And Len([CNum])=5
  5.   ERROR Must be 5 digits Long, lie between 10000 And 79999, And unique
  6.   DEFAULT 10000
  7.  
How can I get the same result using VBA? Or, how can I use foxpro in access? And how do I create a caption for a table using code?

Thanks in advance,
George

msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,886
#2: Apr 25 '07

re: Creating A Table With More Properties Than Fieldname, Length, Type

Once the table has been created close the recordset and then reopen as follows:
Expand|Select|Wrap|Line Numbers
  1. Dim idxTbl As Index
  2. Dim idxFld As Field
  3.  
  4. Set daotblCwS = daodb.CreateTableDef("tblCwS") 
  5.  
  6. 'set the tables primary key
  7. Set idxTbl = daotblCwS.CreateIndex("PrimaryKey")
  8. idxTbl.Primary = -1
  9. idxTbl.Unique = -1
  10. Set idxFld = idxTbl.CreateField("CNum")
  11. idxTbl.Fields.Append idxFld
  12. daotblCwS.Indexes.Append idxTbl
The following is sample code which will give examples of how to set the properties on the fields.

Expand|Select|Wrap|Line Numbers
  1. 'Format a single field to have two decimal places
  2. Set fld = tbl.Fields("MyNumberField")
  3. Set fFormat = fld.CreateProperty("Format", dbText, "Fixed")
  4. fld.Properties.Append fFormat
  5. Set fFormat = fld.CreateProperty("DecimalPlaces", dbByte, 2)
  6. fld.Properties.Append fFormat
  7.  
  8. 'Format the datetime field to be a medium time
  9. Set fld = tbl.Fields("MyDateField")
  10. Set fFormat = fld.CreateProperty("Format", dbText, "Medium Time")
  11. fld.Properties.Append fFormat
  12.  
Member
  
Join Date: Apr 2007
Posts: 72
#3: Apr 25 '07

re: Creating A Table With More Properties Than Fieldname, Length, Type

That is exactly what I want! I found an alternative method in SQL and was just about to post it but your way is equally good...do you know of any websites that give a list of the property values accepted by access? are they all in the help documentation provided?

Using Access '03 on xp
msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,886
#4: Apr 25 '07

re: Creating A Table With More Properties Than Fieldname, Length, Type

Quote:

Originally Posted by smiler2505

That is exactly what I want! I found an alternative method in SQL and was just about to post it but your way is equally good...do you know of any websites that give a list of the property values accepted by access? are they all in the help documentation provided?

Using Access '03 on xp

Don't know one off-hand but have a look at the properties list in table design. This would be a good guideline. Leave the spaces between the words out (e.g. "DefaultValue").
msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,886
#5: Apr 25 '07

re: Creating A Table With More Properties Than Fieldname, Length, Type

Sorry just noticed an error in my previous code, replace
Expand|Select|Wrap|Line Numbers
  1.  Set daotblCwS = daodb.CreateTableDef("tblCwS") 
with
Expand|Select|Wrap|Line Numbers
  1.  Set daotblCwS = daodb.TableDef("tblCwS") 
Member
  
Join Date: Apr 2007
Posts: 72
#6: Apr 26 '07

re: Creating A Table With More Properties Than Fieldname, Length, Type

How do I open and close record sets?
(I thought it was
Expand|Select|Wrap|Line Numbers
  1. Dim rstCwS As RecordSet
  2. Set rstCwS  = daodb.daotblCwS
  3. rstCwS.Close
  4. rstCwS.OpenRecordset
  5.  
but it's not)
msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,886
#7: Apr 26 '07

re: Creating A Table With More Properties Than Fieldname, Length, Type

Try this ...
Expand|Select|Wrap|Line Numbers
  1. Dim rstCwS As RecordSet
  2. Set rstCwS  = daodb.OpenRecordset("daotblCwS")
  3.  
  4.   ' you procedures here
  5.  
  6. rstCwS.OpenRecordset
  7.  
Have a look at this basic tutorial:

Access VBA DAO recordset loop using two recordsets
ADezii's Avatar
Expert
  
Join Date: Apr 2006
Location: Philadelphia
Posts: 5,219
#8: Apr 26 '07

re: Creating A Table With More Properties Than Fieldname, Length, Type

Quote:

Originally Posted by smiler2505

I have a database, where I need to create a table if it doesn't exist. This is my code at the moment:

Expand|Select|Wrap|Line Numbers
  1. Sub CreateCwS() 
  2.  
  3.     Dim daotbl As DAO.TableDef 
  4.  
  5.     If Not daotbl.Name = tblCwS Then 
  6.  
  7.         DoCmd.SetWarnings False 
  8.  
  9.         Dim daodb As DAO.Database 
  10.         Dim daotblCwS As DAO.TableDef 
  11.         Dim daofldCNum As DAO.Field 
  12.         Dim daofldSNum As DAO.Field 
  13.         Dim daofldCCanEnt As DAO.Field 
  14.         Dim daoiCNum As DAO.Index 
  15.         Dim daoiSNum As DAO.Index 
  16.         Dim daoiCCanEnt As DAO.Index 
  17.  
  18.         Set daodb = CurrentDb() 
  19.         Set daotblCwS = daodb.CreateTableDef("tblCwS") 
  20.  
  21.         Set daofldCNum = daotblCwS.CreateField("CNum", dbLong) 
  22.         Set daofldSNum = daotblCwS.CreateField("SNum", dbText, 5) 
  23.         Set daofldCCanEnt = daotblCwS.CreateField("CCanEnt", dbLong) 
  24.  
  25.         daotblCwS.Fields.Append daofldCNum 
  26.         daotblCwS.Fields.Append daofldSNum 
  27.         daotblCwS.Fields.Append daofldCCanEnt 
  28.         daodb.TableDefs.Append daotblCwS 
  29.  
  30.         DoCmd.RunSQL "INSERT INTO tblCwS SELECT tblCen.CNum, tblSub.SNum FROM tblCen, tblSub;" 
  31.  
  32.     End If 
  33.  
  34. End Sub 
  35.  
That code does create a table, but it only creates a table with field names, lengths, and types. How do I create a table with more field properties set using VBA? In Visual FoxPro, there is the possibility of using the following code, made using with the help of this site:

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE
  2.  tblCwS (CNum n (5 , 0)
  3.   NOT NULL 
  4.   CHECK Between 10000 And 79999 And Len([CNum])=5
  5.   ERROR Must be 5 digits Long, lie between 10000 And 79999, And unique
  6.   DEFAULT 10000
  7.  
How can I get the same result using VBA? Or, how can I use foxpro in access? And how do I create a caption for a table using code?

Thanks in advance,
George

If you are creating Tables, Fields, Indexes, Primary Keys, and other Database Schema, you should definately consider using ADOX (Microsoft ADO Extensions For DDL and Security). This technology was designed specifically for programmatically creating and manipulating Objects, especially when it comes to Objects relating to your Database's Security and Structure. It contains additional Objects that work with the core ADO Objects. Just thought that this would be a good point to mention here.
msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,886
#9: Apr 26 '07

re: Creating A Table With More Properties Than Fieldname, Length, Type

Quote:

Originally Posted by ADezii

If you are creating Tables, Fields, Indexes, Primary Keys, and other Database Schema, you should definately consider using ADOX (Microsoft ADO Extensions For DDL and Security). This technology was designed specifically for programmatically creating and manipulating Objects, especially when it comes to Objects relating to your Database's Security and Structure. It contains additional Objects that work with the core ADO Objects. Just thought that this would be a good point to mention here.

Not a bad suggestion.

You will find the syntax for ADOX code on Allen Brownes website.

http://allenbrowne.com/func-ADOX.html#CreateTableAdox
Member
  
Join Date: Apr 2007
Posts: 72
#10: Apr 26 '07

re: Creating A Table With More Properties Than Fieldname, Length, Type

Thanks I'll have a look at that


The property method given earlier works great, but I'm getting an error when setting the primary key

Expand|Select|Wrap|Line Numbers
  1.  Dim daodb As DAO.Database
  2.  Dim daotdfCwS As DAO.TableDef
  3.  Dim tdfCwS As TableDef
  4.  Dim daofldCNum As DAO.Field
  5.  Dim daofldSNum As DAO.Field
  6.  Dim daofldCCanEnt As DAO.Field
  7.  Dim idxPK As Index
  8.  Dim fldPK As Field
  9.  Dim fldCNum As Field
  10.  Dim fldSNum As Field
  11.  Dim fldCCanEnt As Field
  12.  Dim fmtCNum As Property
  13.  Dim fmtSNum As Property
  14.  Dim fmtCCanEnt As Property
  15.  Dim rstCwS As DAO.Recordset
  16.  
  17.  Set daodb = CurrentDb()
  18.  Set daotdfCwS = daodb.CreateTableDef("tblCwS")
  19.  
  20.  Set daofldCNum = daotdfCwS.CreateField("CNum", dbLong)
  21.  Set daofldSNum = daotdfCwS.CreateField("SNum", dbText, 5)
  22.  Set daofldCCanEnt = daotdfCwS.CreateField("CCanEnt", dbLong)
  23.  
  24.  daotdfCwS.Fields.Append daofldCNum
  25.  daotdfCwS.Fields.Append daofldSNum
  26.  daotdfCwS.Fields.Append daofldCCanEnt
  27.  daodb.TableDefs.Append daotdfCwS
  28.  
  29.  Set rstCwS = daodb.OpenRecordset("tblCwS")
  30.  
  31.  rstCwS.OpenRecordset
  32.  
  33.  Set tdfCwS = daodb.TableDefs("tblCwS")
  34.  Set idxPK = tdfCwS.CreateIndex("PrimaryKey")
  35.  
  36.  idxPK.Primary = -1
  37.  idxPK.Unique = -1
  38.  
  39.  Set fldPK = idxPK.CreateField("CNum")
  40.  
  41.  idxPK.Fields.Append fldPK
  42.  tdfCwS.Indexes.Append idxPK
  43.  
In the last line given here, it stops with the Run-time error 3211 (The database engine could not lock 'tblCwS' because it is already in use by another person or process.)

How can I fix that?
msquared's Avatar
Administrator
  
Join Date: Aug 2006
Location: Dublin, Ireland
Posts: 10,886
#11: Apr 26 '07

re: Creating A Table With More Properties Than Fieldname, Length, Type

Thanks I'll have a look at that

You need to close the previous tabledef and don't open the recordset. See the following:

Expand|Select|Wrap|Line Numbers
  1.  Dim daodb As DAO.Database
  2.  Dim daotdfCwS As DAO.TableDef
  3.  Dim tdfCwS As TableDef
  4.  Dim daofldCNum As DAO.Field
  5.  Dim daofldSNum As DAO.Field
  6.  Dim daofldCCanEnt As DAO.Field
  7.  Dim idxPK As Index
  8.  Dim fldPK As Field
  9.  Dim fldCNum As Field
  10.  Dim fldSNum As Field
  11.  Dim fldCCanEnt As Field
  12.  Dim fmtCNum As Property
  13.  Dim fmtSNum As Property
  14.  Dim fmtCCanEnt As Property
  15.  Dim rstCwS As DAO.Recordset
  16.  
  17.  Set daodb = CurrentDb()
  18.  Set daotdfCwS = daodb.CreateTableDef("tblCwS")
  19.  
  20.  Set daofldCNum = daotdfCwS.CreateField("CNum", dbLong)
  21.  Set daofldSNum = daotdfCwS.CreateField("SNum", dbText, 5)
  22.  Set daofldCCanEnt = daotdfCwS.CreateField("CCanEnt", dbLong)
  23.  
  24.  daotdfCwS.Fields.Append daofldCNum
  25.  daotdfCwS.Fields.Append daofldSNum
  26.  daotdfCwS.Fields.Append daofldCCanEnt
  27.  daodb.TableDefs.Append daotdfCwS
  28.  
  29. daotdfCwS.Close
  30.  
  31. Set tdfCwS = daodb.TableDefs("tblCwS")
  32.  Set idxPK = tdfCwS.CreateIndex("PrimaryKey")
  33.  idxPK.Primary = -1
  34.  idxPK.Unique = -1
  35.  Set fldPK = idxPK.CreateField("CNum")
  36.  idxPK.Fields.Append fldPK
  37.  tdfCwS.Indexes.Append idxPK
  38.  
  39. tdfCwS.Close
  40.  
Member
  
Join Date: Apr 2007
Posts: 72
#12: Apr 26 '07

re: Creating A Table With More Properties Than Fieldname, Length, Type

there was no close property for tabledef's, but I deleted the record set and it works great!
Member
  
Join Date: Apr 2007
Posts: 72
#13: Apr 26 '07

re: Creating A Table With More Properties Than Fieldname, Length, Type

Thanks for the help. I've made the code to make the table (shown below), but I'm not sure how to check if a table exists. I'm going to start a new thread about that.

Expand|Select|Wrap|Line Numbers
  1. Sub CreateCwS()
  2.  
  3.  DoCmd.SetWarnings False
  4.  
  5.   Dim daodb As DAO.Database
  6.   Dim daotdfCwS As DAO.TableDef
  7.   Dim daofldCNum As DAO.Field
  8.   Dim daofldSNum As DAO.Field
  9.   Dim daofldCCanEnt As DAO.Field
  10.   Dim daoidxCNum As DAO.Index
  11.   Dim daoidxSNum As DAO.Index
  12.   Dim daoidxCCanEnt As DAO.Index
  13.   Dim daofldiCNum As DAO.Field
  14.   Dim daofldiSNum As DAO.Field
  15.   Dim daofldiCCanEnt As DAO.Field
  16.   Dim fldCNum As Field
  17.   Dim fldSNum As Field
  18.   Dim fldCCanEnt As Field
  19.   Dim fmtCNum As Property
  20.   Dim fmtSNum As Property
  21.   Dim fmtCCanEnt As Property
  22.  
  23.   Set daodb = CurrentDb()
  24.   Set daotdfCwS = daodb.CreateTableDef("tblCwS")
  25.  
  26.  
  27.  
  28.   Set daofldCNum = daotdfCwS.CreateField("CNum", dbLong)
  29.  
  30.    daofldCNum.DefaultValue = "10000"
  31.    daofldCNum.ValidationRule = "Between 10000 And 79999 And Len([CNum])=5"
  32.    daofldCNum.ValidationText = "Must be 5 digits long, lie between 10000 and 79999, and unique"
  33.    daofldCNum.Required = True
  34.  
  35.  
  36.   Set daofldSNum = daotdfCwS.CreateField("SNum", dbText, 5)
  37.  
  38.    daofldSNum.DefaultValue = "111111"
  39.    daofldSNum.ValidationRule = "Len([SNum])=5"
  40.    daofldSNum.ValidationText = "Must be 5 digits long"
  41.    daofldSNum.Required = True
  42.    daofldSNum.AllowZeroLength = False
  43.  
  44.  
  45.   Set daofldCCanEnt = daotdfCwS.CreateField("CCanEnt", dbLong)
  46.  
  47.    daofldCCanEnt.DefaultValue = "0"
  48.    daofldCCanEnt.ValidationText = "Please enter number of candidates"
  49.    daofldCCanEnt.Required = True
  50.  
  51.  
  52.  
  53.   daotdfCwS.Fields.Append daofldCNum
  54.   daotdfCwS.Fields.Append daofldSNum
  55.   daotdfCwS.Fields.Append daofldCCanEnt
  56.   daodb.TableDefs.Append daotdfCwS
  57.  
  58.  
  59.  
  60.   Set fldCNum = daotdfCwS.Fields("CNum")
  61.  
  62.    Set fmtCNum = fldCNum.CreateProperty("Format", dbText, "General Number")
  63.    fldCNum.Properties.Append fmtCNum
  64.    Set fmtCNum = fldCNum.CreateProperty("DecimalPlaces", dbByte, "0")
  65.    fldCNum.Properties.Append fmtCNum
  66.    Set fmtCNum = fldCNum.CreateProperty("InputMask", dbText, "00000")
  67.    fldCNum.Properties.Append fmtCNum
  68.    Set fmtCNum = fldCNum.CreateProperty("Caption", dbText, "Centre number")
  69.    fldCNum.Properties.Append fmtCNum
  70.  
  71.   Set daoidxCNum = daotdfCwS.CreateIndex("CNum")
  72.  
  73.    daoidxCNum.Required = True
  74.  
  75.   Set daofldiCNum = daoidxCNum.CreateField("CNum")
  76.  
  77.    daoidxCNum.Fields.Append daofldiCNum
  78.    daotdfCwS.Indexes.Append daoidxCNum
  79.  
  80.  
  81.   Set fldSNum = daotdfCwS.Fields("SNum")
  82.  
  83.    Set fmtSNum = fldSNum.CreateProperty("InputMask", dbText, "00000")
  84.    fldSNum.Properties.Append fmtSNum
  85.    Set fmtSNum = fldSNum.CreateProperty("Caption", dbText, "Subject Reference Code")
  86.    fldSNum.Properties.Append fmtSNum
  87.    Set fmtSNum = fldSNum.CreateProperty("UnicodeCompression", dbBoolean, True)
  88.    fldSNum.Properties.Append fmtSNum
  89.  
  90.   Set daoidxSNum = daotdfCwS.CreateIndex("SNum")
  91.  
  92.    daoidxSNum.Required = True
  93.  
  94.   Set daofldiSNum = daoidxSNum.CreateField("SNum")
  95.  
  96.    daoidxSNum.Fields.Append daofldiSNum
  97.    daotdfCwS.Indexes.Append daoidxSNum
  98.  
  99.  
  100.   Set fldCCanEnt = daotdfCwS.Fields("CCanEnt")
  101.  
  102.    Set fmtCCanEnt = fldCCanEnt.CreateProperty("Format", dbText, "General Number")
  103.    fldCCanEnt.Properties.Append fmtCCanEnt
  104.    Set fmtCCanEnt = fldCCanEnt.CreateProperty("DecimalPlaces", dbByte, "0")
  105.    fldCCanEnt.Properties.Append fmtCCanEnt
  106.    Set fmtCCanEnt = fldCCanEnt.CreateProperty("Caption", dbText, "N° of candidates entered")
  107.    fldCCanEnt.Properties.Append fmtCCanEnt
  108.  
  109.   Set daoidxCCanEnt = daotdfCwS.CreateIndex("CCanEnt")
  110.  
  111.    daoidxCCanEnt.Required = True
  112.  
  113.   Set daofldiCCanEnt = daoidxCCanEnt.CreateField("CCanEnt")
  114.  
  115.    daoidxCCanEnt.Fields.Append daofldiCCanEnt
  116.    daotdfCwS.Indexes.Append daoidxCCanEnt
  117.  
  118.  
  119.  
  120.   DoCmd.RunSQL "INSERT INTO tblCwS SELECT tblCen.CNum, tblSub.SNum FROM tblCen, tblSub;"
  121.  
  122.  
  123. End Sub
  124.  
Reply

« previous question | next question »

/bytes/development

/bytes/it

Forums
Visit our community forums for general discussions and latest on Bytes

Our staff and community areas have moved to bytes.com/forums/

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,510 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved