Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ADOX Column processing (Office 2000)

    I have successfully written an Access form VBA program to dynamically extract all the column names and row contents below the column names and write them to an Excel file. Now, I'm trying to reverse the process, and copy the Excel worksheet back into the Access table. Everything works fine until I have a table that is defined with an "AutoIncrement" field. If I attempt to write a value into the AutoIncrement field, I get an error (expected). So, I tried to use ADOX to change the tbl.columns.item(strColName).Properties("AutoIncre ment") to False. I get an error (not expected). Then, I tried to remove the offending column and re-append it without "AutoIncrement". My delete code looks like:

    tbl.Columns.Delete (strColName)

    This also gave an error. My underlying need is to be able to restore the old AutoIncrement values, and also the AutoIncrement capability for subsequent new rows inserted to the table -- and do this programmatically with VBA.
    Any insights on how to do this, would be greatly appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ADOX Column processing (Office 2000)

    I don't understand why you're trying to do this. You cannot write into an autoincrement field, although you can append to it. Why don't you simply use a query?
    Charlotte

  3. #3
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADOX Column processing (Office 2000)

    This is an attempt to "archive" several tables within a database into a multi-worksheet Excel file. The tables are related via several AutoIncrement fields (never more than one per table). To "restore" the archived data, the relationship pointers need to be kept intact. If not, then I will have to go through a "re-mapping" of references from one table to another (where the target row keys have changed). If possible, I'd like to avoid the remapping process.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ADOX Column processing (Office 2000)

    I still don't understand why you're doing this. What is the purpose of "archiving" data to Excel? For that matter, why are you "archiving" data in the first place, since it leads to some of the obvious problems you're facing?
    Charlotte

Posting Permissions

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