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