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:
-
Sub SendNewProject(sPro As String, sDes As String)
-
-
   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:
