ACCESS FEED: Re: [MS_AccessPros] RE: Create Table Syntax

Rabu, 12 Februari 2014

Re: [MS_AccessPros] RE: Create Table Syntax

Sarah-

Ah, I had one variable named incorrectly. I caught it as soon as I tried to compile it. (I use Option Explicit in all my modules.) Here's the corrected code that runs just fine in Northwind 2007:

Sub TestIt()

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

Set db = CurrentDb

Set fld = db.TableDefs("Orders").Fields("Order Date")

SetFieldProperty fld, "Format", "Short Date"

Set fld = Nothing

Set db = Nothing

End Sub

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

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

fldTemp.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

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 12, 2014, at 11:10 PM, <sarahk@schemesoftware.com> <sarahk@schemesoftware.com> wrote:

John,
Sorry about not including the complete thread. I hope I got it this time. Anyhow I am still getting the error: 'run-time error 13, type mismatch' . I can confirm that strName is "Format" , dbText is 10 and strSetting is "Short Time". I did not capitalize in my previous email.
This is my code (I copy pasted your code, but it is not working):
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
fldTemp.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

Any help you can extend me is greatly appreciated.
Thanks
Sarah
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote:
Sarah-

Well, for starters you didn't include the original thread in your reply, so I had to go hunt it down on the web.

dbText should be 10, not "10".

The property name is Format, not format. And the value should be Short Time, not short time. Other than that, I can't see what might be wrong.
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 12, 2014, at 6:20 PM, <sarahk@...> <sarahk@...> wrote:

John,
I tried your code, but I get error 'run-time error 13, type mismatch' on this statement:
Set prpNew = fldTemp.CreateProperty(strName, dbText, strSetting)
strName ="format"
dbText="10"
strsetting="short time"
What am I doing wrong?
Sarah
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)