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

[smiler2505]

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

Sub CreateCwS() 
 
    Dim daotbl As DAO.TableDef 
 
    If Not daotbl.Name = tblCwS Then 
 
        DoCmd.SetWarnings False 
 
        Dim daodb As DAO.Database 
        Dim daotblCwS As DAO.TableDef 
        Dim daofldCNum As DAO.Field 
        Dim daofldSNum As DAO.Field 
        Dim daofldCCanEnt As DAO.Field 
        Dim daoiCNum As DAO.Index 
        Dim daoiSNum As DAO.Index 
        Dim daoiCCanEnt As DAO.Index 
 
        Set daodb = CurrentDb() 
        Set daotblCwS = daodb.CreateTableDef("tblCwS") 
 
        Set daofldCNum = daotblCwS.CreateField("CNum", dbLong) 
        Set daofldSNum = daotblCwS.CreateField("SNum", dbText, 5) 
        Set daofldCCanEnt = daotblCwS.CreateField("CCanEnt", dbLong) 
 
        daotblCwS.Fields.Append daofldCNum 
        daotblCwS.Fields.Append daofldSNum 
        daotblCwS.Fields.Append daofldCCanEnt 
        daodb.TableDefs.Append daotblCwS 
 
        DoCmd.RunSQL "INSERT INTO tblCwS SELECT tblCen.CNum, tblSub.SNum FROM tblCen, tblSub;" 
 
    End If 
 
End Sub 
 

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:

CREATE TABLE
 tblCwS (CNum n (5 , 0)
  NOT NULL 
  CHECK Between 10000 And 79999 And Len([CNum])=5
  ERROR Must be 5 digits Long, lie between 10000 And 79999, And unique
  DEFAULT 10000
 

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

[MMcCarthy]

Once the table has been created close the recordset and then reopen as follows:

Dim idxTbl As Index
Dim idxFld As Field
 
Set daotblCwS = daodb.CreateTableDef("tblCwS") 
 
'set the tables primary key
Set idxTbl = daotblCwS.CreateIndex("PrimaryKey")
idxTbl.Primary = -1
idxTbl.Unique = -1
Set idxFld = idxTbl.CreateField("CNum")
idxTbl.Fields.Append idxFld
daotblCwS.Indexes.Append idxTbl

The following is sample code which will give examples of how to set the properties on the fields.

'Format a single field to have two decimal places
Set fld = tbl.Fields("MyNumberField")
Set fFormat = fld.CreateProperty("Format", dbText, "Fixed")
fld.Properties.Append fFormat
Set fFormat = fld.CreateProperty("DecimalPlaces", dbByte, 2)
fld.Properties.Append fFormat
 
'Format the datetime field to be a medium time
Set fld = tbl.Fields("MyDateField")
Set fFormat = fld.CreateProperty("Format", dbText, "Medium Time")
fld.Properties.Append fFormat
 

[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

[MMcCarthy]

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") .

[MMcCarthy]

Sorry just noticed an error in my previous code, replace

 Set daotblCwS = daodb.CreateTableDef("tblCwS") 

with

 Set daotblCwS = daodb.TableDef("tblCwS") 

[smiler2505]

How do I open and close record sets?
(I thought it was

Dim rstCwS As RecordSet
Set rstCwS  = daodb.daotblCwS
rstCwS.Close
rstCwS.OpenRecordset
 

but it's not)

[MMcCarthy]

Try this ...

Dim rstCwS As RecordSet
Set rstCwS  = daodb.OpenRecordset("daotblCwS")
 
  ' you procedures here
 
rstCwS.OpenRecordset
 

Have a look at this basic tutorial:

Access VBA DAO recordset loop using two recordsets

[ADezii]

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

Sub CreateCwS() 
 
    Dim daotbl As DAO.TableDef 
 
    If Not daotbl.Name = tblCwS Then 
 
        DoCmd.SetWarnings False 
 
        Dim daodb As DAO.Database 
        Dim daotblCwS As DAO.TableDef 
        Dim daofldCNum As DAO.Field 
        Dim daofldSNum As DAO.Field 
        Dim daofldCCanEnt As DAO.Field 
        Dim daoiCNum As DAO.Index 
        Dim daoiSNum As DAO.Index 
        Dim daoiCCanEnt As DAO.Index 
 
        Set daodb = CurrentDb() 
        Set daotblCwS = daodb.CreateTableDef("tblCwS") 
 
        Set daofldCNum = daotblCwS.CreateField("CNum", dbLong) 
        Set daofldSNum = daotblCwS.CreateField("SNum", dbText, 5) 
        Set daofldCCanEnt = daotblCwS.CreateField("CCanEnt", dbLong) 
 
        daotblCwS.Fields.Append daofldCNum 
        daotblCwS.Fields.Append daofldSNum 
        daotblCwS.Fields.Append daofldCCanEnt 
        daodb.TableDefs.Append daotblCwS 
 
        DoCmd.RunSQL "INSERT INTO tblCwS SELECT tblCen.CNum, tblSub.SNum FROM tblCen, tblSub;" 
 
    End If 
 
End Sub 
 

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:

CREATE TABLE
 tblCwS (CNum n (5 , 0)
  NOT NULL 
  CHECK Between 10000 And 79999 And Len([CNum])=5
  ERROR Must be 5 digits Long, lie between 10000 And 79999, And unique
  DEFAULT 10000
 

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 programmaticall y 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.

[MMcCarthy]

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 programmaticall y 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

[smiler2505]

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

 Dim daodb As DAO.Database
 Dim daotdfCwS As DAO.TableDef
 Dim tdfCwS As TableDef
 Dim daofldCNum As DAO.Field
 Dim daofldSNum As DAO.Field
 Dim daofldCCanEnt As DAO.Field
 Dim idxPK As Index
 Dim fldPK As Field
 Dim fldCNum As Field
 Dim fldSNum As Field
 Dim fldCCanEnt As Field
 Dim fmtCNum As Property
 Dim fmtSNum As Property
 Dim fmtCCanEnt As Property
 Dim rstCwS As DAO.Recordset
 
 Set daodb = CurrentDb()
 Set daotdfCwS = daodb.CreateTableDef("tblCwS")
 
 Set daofldCNum = daotdfCwS.CreateField("CNum", dbLong)
 Set daofldSNum = daotdfCwS.CreateField("SNum", dbText, 5)
 Set daofldCCanEnt = daotdfCwS.CreateField("CCanEnt", dbLong)
 
 daotdfCwS.Fields.Append daofldCNum
 daotdfCwS.Fields.Append daofldSNum
 daotdfCwS.Fields.Append daofldCCanEnt
 daodb.TableDefs.Append daotdfCwS
 
 Set rstCwS = daodb.OpenRecordset("tblCwS")
 
 rstCwS.OpenRecordset
 
 Set tdfCwS = daodb.TableDefs("tblCwS")
 Set idxPK = tdfCwS.CreateIndex("PrimaryKey")
 
 idxPK.Primary = -1
 idxPK.Unique = -1
 
 Set fldPK = idxPK.CreateField("CNum")
 
 idxPK.Fields.Append fldPK
 tdfCwS.Indexes.Append idxPK
 

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?