Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    still working on it, but i have this:

    [codebox] strSQL = "DELETE FROM <New Table>;"
    DoCmd.RunSQL strSQL

    strSQL = "SELECT <columns from referring table> INTO <New Table>" & _
    " FROM <referring table> " & _
    " WHERE <various conditions>;"

    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

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [quote name='steve_skelton13' post='800171' date='27-Oct-2009 19:20']still working on it, but i have this:

    [codebox] strSQL = "DELETE FROM <New Table>;"
    DoCmd.RunSQL strSQL

    strSQL = "SELECT <columns from referring table> INTO <New Table>" & _
    " FROM <referring table> " & _
    " WHERE <various conditions>;"

    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[/quote]


    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
    Andrew

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Found an even simpler method:

    Dim db As DAO.Database
    Set db = CurrentDb

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

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='steve_skelton13' post='800171' date='28-Oct-2009 06:20']still working on it, but i have this:

    [codebox] strSQL = "DELETE FROM <New Table>;"
    DoCmd.RunSQL strSQL

    strSQL = "SELECT <columns from referring table> INTO <New Table>" & _
    " FROM <referring table> " & _
    " WHERE <various conditions>;"

    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[/quote]
    Do you have to reate this table every time you run this code?

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •