The situation. Â
Back end Access DB, front ended with an Excel Form. Add a new record to a table in the DB and run a Make Table Query to sort the whole table in to alphabetical order. Reload the table into a list box.
I need to make a new table because simply sorting the existing table does not results in a alphabetica import into the list box.
The problem is that when I run the SQL for the update query, if the table name is the same as a table already in the DB then DAO errors. In Access you have to click a message box to allow the deletion of the old table.
From VBA I can use DAO with "TableDefs.Delete" to remove the old table then the solution works fine. But I don't really like this idea, it is clunky and I don't like deleting table danger!
So what's a better way?
The workhorse code looks like this:
   Dim i As Integer
   Dim db As Database, rs As Recordset, r As Long
   Dim sSql As String
   Set db = OpenDatabase(gsDataBase)
   Set rs = db.OpenRecordset("tblProject", dbOpenTable)
   With rs
       .AddNew
       .Fields("Project") = sPro
       .Fields("Description") = sDes
       .Update
   End With
   '''close table we will delete shortly
   rs.Close
   Set rs = Nothing
   '''run updatequery to update project list
   sSql = " SELECT tblProject.Project INTO tblProjectS" _
          & " FROM tblProject" _
          & " ORDER BY tblProject.Project;"
   '''delete old projectsS table
   db.TableDefs.Delete "tblProjectS"
   '''run make new table
   db.Execute sSql
   '''close DB
   db.Close
   Set db = Nothing
   MsgBox "Project has been added to the projects list" _
          & vbNewLine _
          & vbNewLine _
          , vbOKOnly, gcsMsgboxhead
End Sub
Related posts:
