my old DAO project... "the return 2" index on a field
-
- PlatinumLounger
- Posts: 4374
- Joined: 26 Apr 2010, 17:36
my old DAO project... "the return 2" index on a field
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.
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.
Last edited by sal21 on 30 Apr 2013, 11:17, edited 1 time in total.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: my old DAO project... "the return 2"
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
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4374
- Joined: 26 Apr 2010, 17:36
Re: my old DAO project... "the return 2"
WOW!HansV wrote: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
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
-
- PlatinumLounger
- Posts: 4374
- Joined: 26 Apr 2010, 17:36
Re: my old DAO project... "the return 2"
HansV wrote: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
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: my old DAO project... "the return 2" index on a field
strSQL = "ALTER TABLE ALTER COLUMN No1 TEXT(11)"
DB.Execute strSQL, dbFailOnError
You already have the code to create an index.
DB.Execute strSQL, dbFailOnError
You already have the code to create an index.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4374
- Joined: 26 Apr 2010, 17:36
Re: my old DAO project... "the return 2" index on a field
HansV wrote: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...???
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: my old DAO project... "the return 2" index on a field
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.
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.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4374
- Joined: 26 Apr 2010, 17:36
Re: my old DAO project... "the return 2" index on a field
HansV wrote: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!
-
- PlatinumLounger
- Posts: 4374
- Joined: 26 Apr 2010, 17:36
Re: my old DAO project... "the return 2" index on a field
OPS...HansV wrote: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.
instead txt prperty i need to change field property in Short Date, how to?
Sorry if i reply on old post.
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: my old DAO project... "the return 2" index on a field
strSQL = "ALTER TABLE MyTable ALTER COLUMN MyField DATETIME"
where MyTable is the name of the table and MyField is the name of the field.
where MyTable is the name of the table and MyField is the name of the field.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4374
- Joined: 26 Apr 2010, 17:36
Re: my old DAO project... "the return 2" index on a field
tks.HansV wrote:strSQL = "ALTER TABLE MyTable ALTER COLUMN MyField DATETIME"
where MyTable is the name of the table and MyField is the name of the field.
-
- PlatinumLounger
- Posts: 4374
- Joined: 26 Apr 2010, 17:36
Re: my old DAO project... "the return 2" index on a field
Hans sorry if i repost on old 3th.HansV wrote:strSQL = "ALTER TABLE MyTable ALTER COLUMN MyField DATETIME"
where MyTable is the name of the table and MyField is the name of the field.
How to use a where clausole similar:
ALTER TABLE GEm ALTER COLUMN NATO DATETIME WHERE LEFT(NATO,4)='1929'
have sintiax error
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: my old DAO project... "the return 2" index on a field
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.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4374
- Joined: 26 Apr 2010, 17:36
Re: my old DAO project... "the return 2" index on a field
HansV wrote: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
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: my old DAO project... "the return 2" index on a field
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.
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.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4374
- Joined: 26 Apr 2010, 17:36
Re: my old DAO project... "the return 2" index on a field
HansV wrote: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?
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: my old DAO project... "the return 2" index on a field
The description was meant to be done interactively. You only need to change the field once, don't you?
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4374
- Joined: 26 Apr 2010, 17:36
Re: my old DAO project... "the return 2" index on a field
wow!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!HansV wrote:The description was meant to be done interactively. You only need to change the field once, don't you?
perfect! DIRECTLLY IN ACEES IDE , IN ONE SHOT IN FEW SECONDS!!!!1
tKS mY "BROTHER"