INSERT INTO doesn"t wait for table creation...

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

Re: INSERT INTO doesn"t wait for table creation...

Giles wrote:
> 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?

Yhis entire issue is a result of the bad design of this application. Instead
of a table for each user, have a single table with a column identifying the
user for which the row is being inserted. Problem solved.


However, you are likely running into the Jet delared-write feature. Here are
a couple articles about it:
single connection:
http://support.microsoft.com/?kbid=240317

two connections:
http://support.microsoft.com/kb/200300


Be awaredelayed-write is intended to improve the performance of applications
using Jet as their backend, and that using the techniques espoused in those
articles will involve sacrificing the performance benefits of the
delayed-write cache.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: INSERT INTO doesn"t wait for table creation...

"Bob Barrows [MVP]" wrote in message
news:O4H51SpLIHA.1164@TK2MSFTNGP02.phx.gbl...
> Giles wrote:
>> 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?
>
> Yhis entire issue is a result of the bad design of this application.
> Instead of a table for each user, have a single table with a column
> identifying the user for which the row is being inserted. Problem solved.
>
>
> However, you are likely running into the Jet delared-write feature. Here
> are a couple articles about it:
> single connection:
> http://support.microsoft.com/?kbid=240317
>
> two connections:
> http://support.microsoft.com/kb/200300
>
>
> Be awaredelayed-write is intended to improve the performance of
> applications using Jet as their backend, and that using the techniques
> espoused in those articles will involve sacrificing the performance
> benefits of the delayed-write cache.

Unfortunately I am limited to Access, and thought separate tables would cut
down the time needed for queries to be done if the db got huge (as there
seems no way to create useful indexes for asp queries???). However, I will
accept your advice, as a single table will make your previous advice
possible (SQL for Retrieving Latest Messages). Thanks.

Re: INSERT INTO doesn"t wait for table creation...

Giles wrote:
> "Bob Barrows [MVP]" wrote in message
> news:O4H51SpLIHA.1164@TK2MSFTNGP02.phx.gbl...
>> Giles wrote:
>>> 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?
>>
>> Yhis entire issue is a result of the bad design of this application.
>> Instead of a table for each user, have a single table with a column
>> identifying the user for which the row is being inserted. Problem
>> solved. However, you are likely running into the Jet delared-write
>> feature.
>> Here are a couple articles about it:
>> single connection:
>> http://support.microsoft.com/?kbid=240317
>>
>> two connections:
>> http://support.microsoft.com/kb/200300
>>
>>
>> Be awaredelayed-write is intended to improve the performance of
>> applications using Jet as their backend, and that using the
>> techniques espoused in those articles will involve sacrificing the
>> performance benefits of the delayed-write cache.
>
> Unfortunately I am limited to Access, and thought separate tables
> would cut down the time needed for queries to be done if the db got
> huge (as there seems no way to create useful indexes for asp
> queries???).

Someone has misinformed you. It is certainly possible to create indexes in
Access. And there is no difference between queries run via the Access Query
Builder and queries run via ADO from external applications (including ASP).
So I am really puzzled by your thinking that there is no way to create
useful indexes ....

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"