plz tell me about table property of MS access
Nedan Nedzatra wrote:
Table Properties
09-Jan-10
Hia
Good Morning Friends
the help example shows how to create fields with some specifications fo
example CHAR
However there are more than 25 specifications and properties for a table
How can I control them
Can one of you show me the following two
1.A field with 20 characters maximu
2.Validation Rule - Vowels Not Allowed
Previous Posts In This Thread:
Table Properties
Hia
Good Morning Friends
the help example shows how to create fields with some specifications fo
example CHAR
However there are more than 25 specifications and properties for a table
How can I control them
Can one of you show me the following two
1.A field with 20 characters maximu
2.Validation Rule - Vowels Not Allowed
In the table design panel set the field length for your field to 20.
In the table design panel set the field length for your field to 20
That will limit you to 20 characters
In the validation rule enter [YourFieldName] Not Like "*[aeiou]*
That will not allow vowels in the fiel
Regard
Kevin
Sounds like you are working with a table in SQL Server and not Access.
Sounds like you are working with a table in SQL Server and not Access. Whe
it comes to field properties, in Access its pretty much straight forwar
given the limited number of possible values, however with SQL Server ther
are a variety of issues that come into play when determining which data typ
would be best for a field. I am not one to be able to go into all of th
intricies of that and have to go digging around in the documentation myself.
Hi Nedan,Did that work?
Hi Nedan
Did that work
Kevi
"kc-mass" <connearney_AT_comcast_DOT_net> wrote in message
The simplest way to create fields is to use design view.
The simplest way to create fields is to use design view. If you need to the
examine the fields programmatically, loop through the Fields of the TableDe
as shown here
http://allenbrowne.com/func-06.htm
If you need to create a table use DDL, this may help
http://allenbrowne.com/func-DDL.htm
For many of the attributes, you will need to use DAO
http://allenbrowne.com/func-DAO.htm
Or ADOX
http://allenbrowne.com/func-ADOX.htm
To compare the field type names to use in JET, DDL, DAO and ADOX, see
http://allenbrowne.com/ser-49.htm
-
Allen Browne - Microsoft MVP. Perth, Western Australi
Tips for Access users -
http://allenbrowne.com/tips.htm
Reply to group, rather than allenbrowne at mvps dot org.
Hia!Kevin!How are you?
Hia
Kevin
How are you
I am sorry for replying late. From the gone Saturday to until a few hour
ago I my questions and your answers were not posted on this site. Why
As for my question I may have mislead you
I explain
The below given codes shows the table has around 25 properties. It is not
problem to control fields when you append tables
However if you want to change for example the field size for example from 1
characters to 6 an error occurs
However you can do it through Access User Interface though Access warn
about loosing data
Anything that can be done through a User Interface should be able to be don
programmatically but I am not able to change the field size
Thanks
Sub ChangingFieldProperties(
Dim db As Databas
Dim tbl As TableDe
Dim fld As Fiel
Dim pr As Propert
Set db = CurrentD
Set tbl = db.TableDefs("tblcustomers"
On Error Resume Nex
With tb
For Each pr In .Propertie
MsgBox pr.Name & " = " & p
Nex
End Wit
On Error GoTo
End Su
:
Hia!David!Please have a look at my reply above.Thanks."David C. Holley" wrote:
Hia!
David!
Please have a look at my reply above.
Thanks.
:
Hia!Allen!
Hia!
Allen!
I will have a look at your sites.
Please have a look at my reply above. It is a rather difficult problem at my
level.
:
Read the links.
Read the links.
Understand that some of these properties do not exist until you use them, so
attempting to reading them programmatically yields an error. Your code must
therefore use error handling.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Hi NedanYour looking for the Data Definition Language or DDL.
Hi Nedan
Your looking for the Data Definition Language or DDL. Check out this site
from Allen Brown
http://allenbrowne.com/func-DDL.html#ModifyFieldDDL
Regards
Kevin
It seems you cannot change the size of a text fieldprogrammatically after it
It seems you cannot change the size of a text field
programmatically after it has been created. As you probably
know, the FieldSize property is Read-Only and the Size
property cannot be changed after a field has been created.
If you need to change the size of a field programmatically,
you could use a work-around, but, depending on your
circumstances, it could be difficult to achieve a robust
solution. You could use VBA to create a new table with the
smaller field size and then use an append query to transfer
the data from the old, to the new table. However, it starts
to get messy when you have to think about table
relationships, data-entry forms, queries, reports, etc.,
that relied on the old table. As others have said, it is
probably easier to change the field size using the
user-interface; then Access will take care of everything
else for you. Nevertheless, you can achieve your objective
programmatically if you really need to do it that way.
By the way, your procedure is ChangingFieldProperties().
But your code loops through the table properties, not a
field's properties.
Did you mean to loop through the table properties?
If you want to see a field's properties, you need something
like this:
Sub FieldProperties()
Dim objDB As DAO.Database
Dim objTBL As DAO.TableDef
Dim objFLD As DAO.Field
Dim objPRP As DAO.Property
Dim lngPropCount As Long
Dim lngNo As Long
On Error GoTo Error_ChangeFieldLength
Set objDB = CurrentDb()
Set objTBL = objDB.TableDefs("tblCustomers")
Set objFLD = objTBL.Fields("MyField")
lngPropCount = objFLD.Properties.Count
Debug.Print "FIELD PROPERTY COUNT: " & lngPropCount
For Each objPRP In objFLD.Properties
lngNo = lngNo + 1
Debug.Print lngNo; Tab(7); objPRP.Name; _
Tab(30); objPRP.Type
Next
Debug.Print "END OF PROPERTY LIST"
' View results in Immediate Window (CTRL-G)
Exit_ChangeFieldLength:
Set objPRP = Nothing
Set objFLD = Nothing
Set objTBL = Nothing
Set objDB = Nothing
Exit Sub
Error_ChangeFieldLength:
MsgBox "Error Number: " & Err.Number _
& vbNewLine _
& "Error Description: " & Err.Description, _
vbOKOnly + vbExclamation, _
"Error Information"
Resume Exit_ChangeFieldLength
End Sub
And if you want to create a text field limited to 6
characters, you need something like this:
Sub CreateTextField()
Dim objDB As DAO.Database
Dim objTBL As DAO.TableDef
Dim objFLD As DAO.Field
On Error GoTo Error_ChangeFieldLength
Set objDB = CurrentDb()
Set objTBL = objDB.TableDefs("tblCustomers")
Set objFLD = objTBL.CreateField
objFLD.Name = "MyNewField"
objFLD.Type = dbText
objFLD.Size = 6
You probably found this example of programmatically changing the field
You probably found this example of programmatically changing the field size:
http://allenbrowne.com/func-DDL.html#ModifyFieldDDL
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Not so difficult after all.Nice solution to know.Geoff
Not so difficult after all.
Nice solution to know.
Geoff
Hia!Allen!How are you!
Hia!
Allen!
How are you!
I have just run your first example and I called the function using
Sub CallLeadFunction()
TableInfo (Something)
End Sub
Am I calling it properly? Why is not perfect? I do not know. Please tell me.
Why do you use Functions? Only because you want to print it through the
calling Sub or Function.
Thanks.
:
Hia!Geoff!
Hia!
Geoff!
Thank you for the procedures but - not to cause offense - for something that
can be done -I mean we can change field size ignoring the Access warning -
you should be able to offer a parallel programmatic solution. I mean VBA but
I may be wrong. May be you have to go a level deeper than VBA operating.
:
<snip>Well if you do not want to use DDL then you can do it using DAO, but
<snip>
Well if you do not want to use DDL then you can do it using DAO, but with DAO
you must first delete the field from the TableDef's fields collection, then
add a field with the attributes you need. The fact that the field is now 'on
the end' of the field list has no bearing on subsequent use of the field
(the ordinal position is irrelevant).
For a table called tblMyTable, containing a field called Test1, the
following example will change Test1 to a number type Long. It will set the
new field's DefaultValue property to 1 and its Required property to True.
Dim fld As DAO.Field
With DBEngine(0)(0).TableDefs!tblMyTable
..Fields.Delete "Test1"
Set fld = .CreateField("Test1", dbLong)
fld.DefaultValue = "1"
fld.Required = True
..Fields.Append fld
End With
Set fld = Nothing
HTH
If Somthing represents the name of your table, you need to put it in
If Somthing represents the name of your table, you need to put it in quotes:
Call TableInfo("Something")
Open the Immediate Window (Ctrl+G), and you can call the function by
entering this:
? TableInfo("Something")
All VBA code must be in a sub or function.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Hi Nedan,I am a bit lost with your request for help.
Hi Nedan,
I am a bit lost with your request for help.
Allen has indicated that a programmatic solution exists.
Stuart seems to think you may not want to use that solution.
And you say in your latest post (to me):
At this stage, I am not sure if you need a further
explanation but I hope below helps.
The programmatic solution uses the Data Definition Language
(DDL). DDL allows you to execute queries (or SQL
statements) that will change the size of a field. These
queries or SQL statements can be executed programmatically.
Here is the example from Allen's website:
Sub ModifyFieldDDL()
' Allen Browne's code using DDL.
' See:
'
http://allenbrowne.com/func-DDL.html#ModifyFieldDDL
'Purpose:
'Change the type or size of a field using DDL.
Dim strSql As String
strSql = "ALTER TABLE MyTable ALTER COLUMN MyField
TEXT(100);"
DBEngine(0)(0).Execute strSql, dbFailOnError
End Sub
You need to change:
"MyTable" to "tblCustomers"
"MyField" to the name of the field whose size you are trying
to change.
"TEXT(100)" to "TEXT(6)"
The ALTER TABLE statement is part of DDL.
In my version of Access, when I create a new query in design
view, I can open the Query menu, point to SQL Specific, and
click Data Definition. I can then enter the following SQL
statement:
ALTER TABLE tblCustomers ALTER COLUMN MyField TEXT(6);
I can save and run the query to change the size of the field
to 6 characters.
The VBA code achieves the same result programmatically.
I do not know if you know this already.
Just trying to help.
Geoff
wrote in message
Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF Customized Find Control for FlowDocuments
http://www.eggheadcafe.com/tutorial...3-721a40cf910c/wpf-customized-find-contr.aspx