create index column on new table

Topic

New Reply

still working on it, but i have this:

[codebox] strSQL = “DELETE FROM ;”
DoCmd.RunSQL strSQL

strSQL = “SELECT INTO ” & _
” FROM ” & _
” WHERE ;”

DoCmd.RunSQL strSQL <– this works fine, new data appears

Dim dbs As Database
Dim tdfRUGs As TableDef
Dim idxID As Index

Set dbs = CurrentDb
Set tdfRUGs = dbs!RUGs

With tdfRUGs
Set idxID = .CreateIndex("ID")
End With

Indexes.Append idxID <– says 'object required'

[/codebox]

I tried setting up the new table with an ID column (autonumber, index no dupes) and re-did the delete statement to clear all columns except Mr. ID but the new table runs without keeping that column. which is fine, i want to add the id column after the fact. Just not quite sure how to do it…

TIA

Reply | Quote

Replies

still working on it, but i have this:

[codebox] strSQL = “DELETE FROM ;”
DoCmd.RunSQL strSQL

strSQL = “SELECT INTO ” & _
” FROM ” & _
” WHERE ;”

DoCmd.RunSQL strSQL <– this works fine, new data appears

Dim dbs As Database
Dim tdfRUGs As TableDef
Dim idxID As Index

Set dbs = CurrentDb
Set tdfRUGs = dbs!RUGs

With tdfRUGs
Set idxID = .CreateIndex("ID")
End With

Indexes.Append idxID <– says 'object required'

[/codebox]

I tried setting up the new table with an ID column (autonumber, index no dupes) and re-did the delete statement to clear all columns except Mr. ID but the new table runs without keeping that column. which is fine, i want to add the id column after the fact. Just not quite sure how to do it…

TIA

The indexes are not referencing any objects

Try putting it inside the With with a . To apply it to the tabledef

Code:

		With tdfRUGs
			Set idxID = .CreateIndex("ID")
			idxID.Fields.Append  idxID.CreateField("ID")
			.Indexes.Append idxID	  '<-- says 'object required'
		End With

Reply | Quote

Found an even simpler method:

Dim db As DAO.Database
Set db = CurrentDb

strSQL = “ALTER TABLE RUGs ADD [ID] COUNTER;”
db.Execute strSQL

Reply | Quote

still working on it, but i have this:

[codebox] strSQL = “DELETE FROM ;”
DoCmd.RunSQL strSQL

strSQL = “SELECT INTO ” & _
” FROM ” & _
” WHERE ;”

DoCmd.RunSQL strSQL <– this works fine, new data appears

Dim dbs As Database
Dim tdfRUGs As TableDef
Dim idxID As Index

Set dbs = CurrentDb
Set tdfRUGs = dbs!RUGs

With tdfRUGs
Set idxID = .CreateIndex("ID")
End With

Indexes.Append idxID <– says 'object required'

[/codebox]

I tried setting up the new table with an ID column (autonumber, index no dupes) and re-did the delete statement to clear all columns except Mr. ID but the new table runs without keeping that column. which is fine, i want to add the id column after the fact. Just not quite sure how to do it…

TIA

Do you have to reate this table every time you run this code?

If so why not change it to an Append query instead?

Reply | Quote