Here’s something i did not know about VBA!

http://www.puremis.net/excel/cgi-bin/yabb/YaBB.pl?num=1166424720/0#8

Cool, now i just have to get a job at the Natural History Museum.

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]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 [/vba]