Table Properties

N

Nedan Nedzatra

Hia!

Good Morning Friends.

the help example shows how to create fields with some specifications for
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 maximum
2.Validation Rule - Vowels Not Allowed
 
K

kc-mass

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 field

Regards

Kevin
 
D

David C. Holley

Sounds like you're working with a table in SQL Server and not Access. When
it comes to field properties, in Access its pretty much straight forward
given the limited number of possible values, however with SQL Server there
are a variety of issues that come into play when determining which data type
would be best for a field. I am not one to be able to go into all of the
intricies of that and have to go digging around in the documentation myself.
 
K

kc-mass

Hi Nedan,

Did that work?

Kevin
kc-mass said:
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 field

Regards

Kevin


Nedan Nedzatra said:
Hia!

Good Morning Friends.

the help example shows how to create fields with some specifications for
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 maximum
2.Validation Rule - Vowels Not Allowed
 
A

Allen Browne

The simplest way to create fields is to use design view. If you need to then
examine the fields programmatically, loop through the Fields of the TableDef
as shown here:
http://allenbrowne.com/func-06.html

If you need to create a table use DDL, this may help:
http://allenbrowne.com/func-DDL.html
For many of the attributes, you will need to use DAO:
http://allenbrowne.com/func-DAO.html
Or ADOX:
http://allenbrowne.com/func-ADOX.html

To compare the field type names to use in JET, DDL, DAO and ADOX, see:
http://allenbrowne.com/ser-49.html
 
N

Nedan Nedzatra

Hia!

Kevin!

How are you?

I am sorry for replying late. From the gone Saturday to until a few hours
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 a
problem to control fields when you append tables.

However if you want to change for example the field size for example from 10
characters to 6 an error occurs.

However you can do it through Access User Interface though Access warns
about loosing data.

Anything that can be done through a User Interface should be able to be done
programmatically but I am not able to change the field size!

Thanks.


Sub ChangingFieldProperties()

Dim db As Database
Dim tbl As TableDef
Dim fld As Field
Dim pr As Property

Set db = CurrentDb
Set tbl = db.TableDefs("tblcustomers")

On Error Resume Next
With tbl
For Each pr In .Properties
MsgBox pr.Name & " = " & pr
Next
End With
On Error GoTo 0

End Sub




kc-mass said:
Hi Nedan,

Did that work?

Kevin
kc-mass said:
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 field

Regards

Kevin


Nedan Nedzatra said:
Hia!

Good Morning Friends.

the help example shows how to create fields with some specifications for
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 maximum
2.Validation Rule - Vowels Not Allowed


.
 
N

Nedan Nedzatra

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

Allen Browne

Read the links.

Understand that some of these properties don't exist until you use them, so
attempting to reading them programmatically yields an error. Your code must
therefore use error handling.
 
K

kc-mass

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


Nedan Nedzatra said:
Hia!

Kevin!

How are you?

I am sorry for replying late. From the gone Saturday to until a few hours
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
a
problem to control fields when you append tables.

However if you want to change for example the field size for example from
10
characters to 6 an error occurs.

However you can do it through Access User Interface though Access warns
about loosing data.

Anything that can be done through a User Interface should be able to be
done
programmatically but I am not able to change the field size!

Thanks.


Sub ChangingFieldProperties()

Dim db As Database
Dim tbl As TableDef
Dim fld As Field
Dim pr As Property

Set db = CurrentDb
Set tbl = db.TableDefs("tblcustomers")

On Error Resume Next
With tbl
For Each pr In .Properties
MsgBox pr.Name & " = " & pr
Next
End With
On Error GoTo 0

End Sub




kc-mass said:
Hi Nedan,

Did that work?

Kevin
kc-mass said:
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 field

Regards

Kevin


message Hia!

Good Morning Friends.

the help example shows how to create fields with some specifications
for
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 maximum
2.Validation Rule - Vowels Not Allowed


.
 
G

GeoffG

However if you want to change for example
the field size for example from 10
characters to 6 an error occurs.
Anything that can be done through a
User Interface should be able to be done
programmatically but I am
not able to change the field size!

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
objTBL.Fields.Append objFLD

Debug.Print "Size: " & objFLD.Size

Exit_ChangeFieldLength:

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


Regards
Geoff





"Nedan Nedzatra" <[email protected]>
wrote in message
Hia!

Kevin!

How are you?

I am sorry for replying late. From the gone Saturday to
until a few hours
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 a
problem to control fields when you append tables.

However if you want to change for example the field size
for example from 10
characters to 6 an error occurs.

However you can do it through Access User Interface though
Access warns
about loosing data.

Anything that can be done through a User Interface should
be able to be done
programmatically but I am not able to change the field
size!

Thanks.


Sub ChangingFieldProperties()

Dim db As Database
Dim tbl As TableDef
Dim fld As Field
Dim pr As Property

Set db = CurrentDb
Set tbl = db.TableDefs("tblcustomers")

On Error Resume Next
With tbl
For Each pr In .Properties
MsgBox pr.Name & " = " & pr
Next
End With
On Error GoTo 0

End Sub




kc-mass said:
Hi Nedan,

Did that work?

Kevin
"kc-mass" <connearney_AT_comcast_DOT_net> wrote in
message
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 field

Regards

Kevin


"Nedan Nedzatra"
message
Hia!

Good Morning Friends.

the help example shows how to create fields with some
specifications for
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 maximum
2.Validation Rule - Vowels Not Allowed


.
 
A

Allen Browne

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.


Nedan Nedzatra said:
Hia!

Kevin!

How are you?

I am sorry for replying late. From the gone Saturday to until a few hours
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
a
problem to control fields when you append tables.

However if you want to change for example the field size for example from
10
characters to 6 an error occurs.

However you can do it through Access User Interface though Access warns
about loosing data.

Anything that can be done through a User Interface should be able to be
done
programmatically but I am not able to change the field size!

Thanks.


Sub ChangingFieldProperties()

Dim db As Database
Dim tbl As TableDef
Dim fld As Field
Dim pr As Property

Set db = CurrentDb
Set tbl = db.TableDefs("tblcustomers")

On Error Resume Next
With tbl
For Each pr In .Properties
MsgBox pr.Name & " = " & pr
Next
End With
On Error GoTo 0

End Sub




kc-mass said:
Hi Nedan,

Did that work?

Kevin
kc-mass said:
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 field

Regards

Kevin


message Hia!

Good Morning Friends.

the help example shows how to create fields with some specifications
for
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 maximum
2.Validation Rule - Vowels Not Allowed


.
 
G

GeoffG

Not so difficult after all.
Nice solution to know.

Geoff




Allen Browne said:
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.


"Nedan Nedzatra" <[email protected]>
wrote in message
Hia!

Kevin!

How are you?

I am sorry for replying late. From the gone Saturday to
until a few hours
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 a
problem to control fields when you append tables.

However if you want to change for example the field size
for example from 10
characters to 6 an error occurs.

However you can do it through Access User Interface
though Access warns
about loosing data.

Anything that can be done through a User Interface should
be able to be done
programmatically but I am not able to change the field
size!

Thanks.


Sub ChangingFieldProperties()

Dim db As Database
Dim tbl As TableDef
Dim fld As Field
Dim pr As Property

Set db = CurrentDb
Set tbl = db.TableDefs("tblcustomers")

On Error Resume Next
With tbl
For Each pr In .Properties
MsgBox pr.Name & " = " & pr
Next
End With
On Error GoTo 0

End Sub




kc-mass said:
Hi Nedan,

Did that work?

Kevin
"kc-mass" <connearney_AT_comcast_DOT_net> wrote in
message
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 field

Regards

Kevin


"Nedan Nedzatra"
message
Hia!

Good Morning Friends.

the help example shows how to create fields with some
specifications for
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 maximum
2.Validation Rule - Vowels Not Allowed




.
 
N

Nedan Nedzatra

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

Nedan Nedzatra

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.



kc-mass said:
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


Nedan Nedzatra said:
Hia!

Kevin!

How are you?

I am sorry for replying late. From the gone Saturday to until a few hours
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
a
problem to control fields when you append tables.

However if you want to change for example the field size for example from
10
characters to 6 an error occurs.

However you can do it through Access User Interface though Access warns
about loosing data.

Anything that can be done through a User Interface should be able to be
done
programmatically but I am not able to change the field size!

Thanks.


Sub ChangingFieldProperties()

Dim db As Database
Dim tbl As TableDef
Dim fld As Field
Dim pr As Property

Set db = CurrentDb
Set tbl = db.TableDefs("tblcustomers")

On Error Resume Next
With tbl
For Each pr In .Properties
MsgBox pr.Name & " = " & pr
Next
End With
On Error GoTo 0

End Sub




kc-mass said:
Hi Nedan,

Did that work?

Kevin
"kc-mass" <connearney_AT_comcast_DOT_net> wrote in message
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 field

Regards

Kevin


message Hia!

Good Morning Friends.

the help example shows how to create fields with some specifications
for
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 maximum
2.Validation Rule - Vowels Not Allowed




.


.
 
S

Stuart McCall

Nedan Nedzatra said:
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 don't 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
 
A

Allen Browne

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

GeoffG

Hi Nedan,

I'm 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):
I mean we can change field size ignoring
the Access warning - you should be able
to offer a parallel programmatic solution.

At this stage, I'm 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's 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're 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 don't know if you know this already.
Just trying to help.

Geoff



"Nedan Nedzatra" <[email protected]>
wrote in message
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.



kc-mass said:
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


"Nedan Nedzatra"
message
Hia!

Kevin!

How are you?

I am sorry for replying late. From the gone Saturday to
until a few hours
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
a
problem to control fields when you append tables.

However if you want to change for example the field
size for example from
10
characters to 6 an error occurs.

However you can do it through Access User Interface
though Access warns
about loosing data.

Anything that can be done through a User Interface
should be able to be
done
programmatically but I am not able to change the field
size!

Thanks.


Sub ChangingFieldProperties()

Dim db As Database
Dim tbl As TableDef
Dim fld As Field
Dim pr As Property

Set db = CurrentDb
Set tbl = db.TableDefs("tblcustomers")

On Error Resume Next
With tbl
For Each pr In .Properties
MsgBox pr.Name & " = " & pr
Next
End With
On Error GoTo 0

End Sub




:

Hi Nedan,

Did that work?

Kevin
"kc-mass" <connearney_AT_comcast_DOT_net> wrote in
message
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 field

Regards

Kevin


"Nedan Nedzatra"
message
Hia!

Good Morning Friends.

the help example shows how to create fields with
some specifications
for
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 maximum
2.Validation Rule - Vowels Not Allowed




.


.
 
H

huma ambreen

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top