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...
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")