Webmaster Forum / ASP / Database Access / November 2007

IIS7, asp + VBScript site: MSAccess mdb. Simple messaging facility for
limited no of users.
Each user has a table uniquely named after them like [giles_1234]. When a
mesage is posted, a script checks if the table exists for the recipient. If
it doesn't, a new table is created. The message is then added to their
table.
However, for a new recipient, I get
Microsoft JET Database Engine error '80040e37'
Could not find output table 'fred_9876'.
If the page is refreshed (to "resend the information") it is INSERTED fine.
It seems that the INSERT INTO is trying to happen before the table has
finished being created.
Is there a way to delay the INSERT until it has happened?

function TableExists(TableName)
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Provider="Microsoft.Jet.OLEDB.4.0"
Conn.Open (Server.MapPath("/ecourse/db2/ecourseCommunicator.mdb"))
'See if table exists
Set cat = CreateObject("ADOX.Catalog")
cat.ActiveConnection = conn
TableExists=false
For Each tbl In cat.Tables
 If tbl.Type = "TABLE" And lcase(tbl.Name) = lcase(TableName) Then
TableExists = True:exit for
Next
Set tbl = Nothing
Set cat = Nothing
Conn.close:set Conn=nothing
end function

Sub CreateTable(TableName)
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Provider="Microsoft.Jet.OLEDB.4.0"
Conn.Open (Server.MapPath("/ecourse/db2/ecourseCommunicator.mdb"))
   strSQL = "CREATE TABLE [" & TableName & "] (" &_
            "ID Counter Primary Key, " &_
            "ThreadID Number, " &_
            "Sender LongText, " &_
            "RecipientList LongText, " &_
            "MessageTitle LongText, " &_
            "MessageBody LongText, " &_
            "TheDateTimeSent LongText, " &_
            "HasBeenRead YESNO, " &_
            "HasReplied YESNO, " &_
            "IsDeleted YESNO " &_
            ");"
  Conn.Execute strSql
  Conn.Close: Set Conn = Nothing
End Sub

if not TableExists(EmailAr(f,1)) then CreateTable(EmailAr(f,1))

SQL = "INSERT INTO [" & EmailAr(f,1) & "] (ThreadID, Sender, RecipientList,
MessageTitle, MessageBody, TheDateTimeSent) VALUES "
SQL=SQL & "(" & ThreadID & ",'" & Sender & "','" & RecipientList & "','" &
MessageTitle & "','" & MessageBody & "','" & TheDateTimeSent & "')"
Conn.Execute SQL