ACCESS FEED: Re: [MS_AccessPros] Create Table Syntax

Selasa, 11 Februari 2014

Re: [MS_AccessPros] Create Table Syntax

Sarah-

You cannot do it with SQL because the Format property is what you need to set, and that's not defined in SQL.

First, you need a little routine to create a new field string property:

Sub SetFieldProperty(fldTemp As DAO.Field, strName As String, _     strSetting As String)       Dim prpNew As Property     Dim errLoop As Error       ' Attempt to set the specified property.     On Error GoTo Err_Property     fld.Properties(strName) = strSetting     On Error GoTo 0       Exit Sub    Err_Property:       ' Error 3270 means that the property was not found.     If DBEngine.Errors(0).Number = 3270 Then        ' Create property, set its value, and append it to the         ' Properties collection.        Set prpNew = fldTemp.CreateProperty(strName, _           dbText, strSetting)        dbsTemp.Properties.Append prpNew        Resume Next     Else        ' If different error has occurred, display message.        For Each errLoop In DBEngine.Errors           MsgBox "Error number: " & errLoop.Number & vbCr & _              errLoop.Description        Next errLoop        End     End If    End Sub

Change your code like this:

Dim db As DAO.Database, tdf As DAO.TableDef, fld As DAO.Field

' Point to this database

Set db = CurrentDB

' Create the new table using SQL

db.Execute "CREATE TABLE testss3(ID COUNTER PRIMARY KEY, " & _

"[stu id] LONG, " & _
"description TEXT(50), " & _
"document TEXT(255), " & _
"timexx DATETIME, " & _
"documentdate DATETIME ," & _
"entrydate DATETIME)", dbFailOnError

' Make sure this copy of the database knows about the new table

db.TableDefs.Refresh

' Get the new table

Set tdf = db.Tabledefs("testss3")

' Get the first field

Set fld = tdf.Fields("timexx")

' Call the Set Property code

SetFieldProperty fld, "Format", "Short Time"

' Get the second field

Set fld = tdf.Fields("documentdate")

' Set its Format property

SetFieldProperty fld, "Format", "Short Date"

' Clean up

Set fld = Nothing

Set tdf = Nothing

Set db = Nothing

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Feb 11, 2014, at 2:13 AM, <sarahk@schemesoftware.com> <sarahk@schemesoftware.com> wrote:

I am using the following code to create a new table in my database:

CurrentDb.Execute "CREATE TABLE testss3(ID COUNTER PRIMARY KEY, " & _
                    "[stu id] LONG, " & _
                    "description TEXT(50), " & _
                    "document TEXT(255), " & _
                    "timexx DATETIME, " & _
                    "documentdate DATETIME ," & _
                    "entrydate DATETIME)", dbFailOnError

It works fine, however I would like to format the field 'timexx' as short time and 'documentdate' as short date. Is there anyway to-do this in the above command or in an ALTER TABLE command?.
As always all help is greatly appreciated.