Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2008
    Posts
    78

    Unanswered: Initializing a table with structure from another

    This is probably a simple task but I'm struggling a bit with it.

    I create a problem log on some active projects. Before examining the project/orders table, I wipe out the log files so I can start with a fresh table. I was just "Delete * ..." and that was working fine.

    I have two problems:

    1. A the end of the process I'd like to examine the "OrderProblemAudit" table for unused columns and drop them before distributing to the engineers. Each column represents a potential problem. If there are no problems in that category, no sense in distributing a worksheet with many unused columns. I don't know the best way to drop unused columns in a table. In Oracle I read there is a command "DROP UNUSED COLUMNS" but can't find that implementation in access.

    2. Since I'll be dropping columns from the audit table I can no longer "Delete * from OrderProblemAudit;" since it will not recreate the structure. I have a separate table called "OrderProblemAuditStru" that just has the structure. I'd like to have it copied over on top of "OrderProblemAudit" at the start of the routine. That operation would be faster than "DELETE *" and also guarantees the structure will be initialized every time. How can I copy the structure back to the "OrderProblemAudit" table ? Im trying to "SELECT INTO" but I get errors that the file already exists. I could create a new table with code I suppose, but copying another file with the structure seems a whole lot easier...




    Code:
    Private Sub StartAudit_Click()
        Dim RSO, RSP, RSPStru, RSPL As Recordset
        Dim errMsg, LogErrMsg As String
        Dim MsgBoxText As String
        Dim AddRecFlag As Integer
        
    
        
    '    CurrentDb.Execute "Delete * from OrderProblemAudit;", dbFailOnError
        CurrentDb.Execute "Delete * from OrderProblemLog;", dbFailOnError
        CurrentDb.Execute "SELECT * INTO OrderProblemAudit FROM OrderProblemAuditStru WHERE 1 = 2;", dbFailOnError
        
        
        Set RSO = CurrentDb.OpenRecordset("SELECT * FROM Orders where Status <> 'In Engineering'")
        Set RSP = CurrentDb.OpenRecordset("SELECT * FROM OrderProblemAudit")
        Set RSPL = CurrentDb.OpenRecordset("SELECT * FROM OrderProblemLog")
    Last edited by PonPending; 09-18-08 at 12:47.

  2. #2
    Join Date
    Aug 2008
    Posts
    78
    Follow up.. this code does it

    Code:
        DoCmd.CopyObject , "OrderProblemAudit", acTable, "OrderProblemAuditStru"
    It does produce a confirmation dialog which I'm going to figure out how to shut off....

  3. #3
    Join Date
    Aug 2008
    Posts
    78
    Code:
    DoCmd.SetWarnings False
        DoCmd.CopyObject , "OrderProblemAudit", acTable, "OrderProblemAuditStru"
        DoCmd.SetWarnings True

    Lol.. maybe my inner dialog might help someone

  4. #4
    Join Date
    Aug 2008
    Posts
    78
    any ideas on elegantly removing unused columns?

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I've never heard of a drop unused columns command in Access.

    BTW

    Dim RSO, RSP, RSPStru, RSPL As Recordset
    This doesn't make 4 recordsets, it makes one recordset object and three variants.

    Dim RSO As Recordset, RSP As Recordset, RSPStru As Recordset, RSPL As Recordset
    That will make four recordset objects.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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