my old DAO project... "the return 2" index on a field

[sal21]

I just have a table with a field named NO1 (text property lenght 9)
I need with DAO and Vba code to indexing the field with duplicates YES....
...is possible?

Note:
I just have a DAO conn named DB.

[HansV]

Let's say that the table is named MyTable and that you want to give the new index the name MyIndex.

Code: Select all

Dim strSQL As String
strSQL = "CREATE INDEX MyIndex ON MyTable (NO1)"
DB.Execute strSQL, dbFailOnError

[sal21]

Let's say that the table is named MyTable and that you want to give the new index the name MyIndex.

Code: Select all

Dim strSQL As String
strSQL = "CREATE INDEX MyIndex ON MyTable (NO1)"
DB.Execute strSQL, dbFailOnError

WOW!
Work fine!

Note:
Googling...

A lot of example use minimun 10 lines of code.
Dim table def, set index, create.. tabledef ...ecc

BUT YOU IN ONE ONLY LINE ARE.... very brillinat

[sal21]

Let's say that the table is named MyTable and that you want to give the new index the name MyIndex.

Code: Select all

Dim strSQL As String
strSQL = "CREATE INDEX MyIndex ON MyTable (NO1)"
DB.Execute strSQL, dbFailOnError

OPS...

... and if i need to chenge property from field Number to Text with lenght 11 and index dupplicate yes?
Sorry me.

NOTE:
Via query, please

[HansV]

strSQL = "ALTER TABLE ALTER COLUMN No1 TEXT(11)"
DB.Execute strSQL, dbFailOnError

You already have the code to create an index.

[sal21]

strSQL = "ALTER TABLE ALTER COLUMN No1 TEXT(11)"
DB.Execute strSQL, dbFailOnError

You already have the code to create an index.

sorry Hans is correct this sintiax?

alter twice...???

[HansV]

Sorry, I forgot the name of the table.

strSQL = "ALTER TABLE MyTable ALTER COLUMN No1 TEXT(11)"

The first ALTER specifies that you are going to modify the table MyTable.
The second ALTER specifies that you are going to modify the field No1 within this table.

[sal21]

Sorry, I forgot the name of the table.

strSQL = "ALTER TABLE MyTable ALTER COLUMN No1 TEXT(11)"

The first ALTER specifies that you are going to modify the table MyTable.
The second ALTER specifies that you are going to modify the field No1 within this table.

now work great!

[sal21]

Sorry, I forgot the name of the table.

strSQL = "ALTER TABLE MyTable ALTER COLUMN No1 TEXT(11)"

The first ALTER specifies that you are going to modify the table MyTable.
The second ALTER specifies that you are going to modify the field No1 within this table.

OPS...
instead txt prperty i need to change field property in Short Date, how to?

Sorry if i reply on old post.

[HansV]

strSQL = "ALTER TABLE MyTable ALTER COLUMN MyField DATETIME"

where MyTable is the name of the table and MyField is the name of the field.

[sal21]

strSQL = "ALTER TABLE MyTable ALTER COLUMN MyField DATETIME"

where MyTable is the name of the table and MyField is the name of the field.

tks.

[sal21]

strSQL = "ALTER TABLE MyTable ALTER COLUMN MyField DATETIME"

where MyTable is the name of the table and MyField is the name of the field.

Hans sorry if i repost on old 3th.

How to use a where clausole similar:

ALTER TABLE GEm ALTER COLUMN NATO DATETIME WHERE LEFT(NATO,4)='1929'

have sintiax error

[HansV]

Since a field in a table can have only one data type, you cannot use a WHERE-clause in an ALTER TABLE statement. You either change the field to date/time or you don't.

[sal21]

Since a field in a table can have only one data type, you cannot use a WHERE-clause in an ALTER TABLE statement. You either change the field to date/time or you don't.

really my prob is... i need to alter field in DATETIME approx 4.500.xxx records!

and have error in access ide "increase maxlock per file..." ecc

in setad with a vb code:

Teporary space on disk is full...

how to resolve, important for me.
tks.

note:
in vb code i just have used the .propeerty max lock per file=5000000, after .open of conn

[HansV]

Does the following work?

1) Create a new date/time field NATO2.
2) Create an update query

UPDATE GEm SET NATO2=CDate(NATO)

3) Check that the NATO2 field has been filled correctly.
4) Delete the NATO field and rename NATO2 to NATO.

[sal21]

Does the following work?

1) Create a new date/time field NATO2.
2) Create an update query

UPDATE GEm SET NATO2=CDate(NATO)

3) Check that the NATO2 field has been filled correctly.
4) Delete the NATO field and rename NATO2 to NATO.

in access ide or via code?

[HansV]

The description was meant to be done interactively. You only need to change the field once, don't you?

[sal21]

The description was meant to be done interactively. You only need to change the field once, don't you?

wow!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

perfect! DIRECTLLY IN ACEES IDE , IN ONE SHOT IN FEW SECONDS!!!!1

tKS mY "BROTHER"