A better way with a Make Table Query?

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:

VBA:
  1. Sub SendNewProject(sPro As String, sDes As String)
  2.  
  3.     Dim i As Integer
  4.     Dim db As Database, rs As Recordset, r As Long
  5.     Dim sSql As String
  6.  
  7.     Set db = OpenDatabase(gsDataBase)
  8.     Set rs = db.OpenRecordset("tblProject", dbOpenTable)
  9.  
  10.     With rs
  11.         .AddNew
  12.         .Fields("Project") = sPro
  13.         .Fields("Description") = sDes
  14.         .Update
  15.     End With
  16.  
  17.     '''close table we will delete shortly
  18.     rs.Close
  19.     Set rs = Nothing
  20.  
  21.     '''run updatequery to update project list
  22.     sSql = " SELECT tblProject.Project INTO tblProjectS" _
  23.            & " FROM tblProject" _
  24.            & " ORDER BY tblProject.Project;"
  25.  
  26.     '''delete old projectsS table
  27.     db.TableDefs.Delete "tblProjectS"
  28.     '''run make new table
  29.     db.Execute sSql
  30.  
  31.     '''close DB
  32.     db.Close
  33.     Set db = Nothing
  34.  
  35.     MsgBox "Project has been added to the projects list" _
  36.            & vbNewLine _
  37.            & vbNewLine _
  38.            , vbOKOnly, gcsMsgboxhead
  39.  
  40. End Sub

Related posts:

  1. VBA, SQL, Dates and Strings – the habits we form.
  2. Selection to CSV
  3. Parameters in ADO, getting good with Access – NOT!

Submit a Comment