Create table, could not find it, to fast?

[klasse]

Hi,

I have the following code

' create the table USAGE
DoCmd.RunSQL ("CREATE TABLE USAGE (MyDate date, MyUsers INTEGER)&quot

' Put data into new table USAGE.
Dim MyRst As Recordset
Set MyRst = CurrentDb.OpenRecordset("USAGE", dbOpenTable)
With MyRst
While arrayusage(m, 1) <> &quot;&quot;
MyRst.AddNew
Let !MyDate = arrayusage(m, 1)
Let !MYUSERS = arrayusage(m, 2)
MyRst.Update
MyRst.MoveLast
m = m + 1
Wend
End With

The problem comes when it is trying to execute
Set MyRst = CurrentDb.OpenRecordset(&quot;USAGE&quot;, dbOpenTable)

Access does not find the table, I check it and I see that it is there.
If I add a dialog just before this line and wait for 3-4 seconds, then it will find it.

I supposed Access does not refresh the tables collection before running the next line of code, and therefore it is using the old collection?

Any ideas on how to solve it?

Thanks,

Klasse

 

[Craig0201]

Klasse,

You need to pause the code. The SQL is taking sometime to create the SQL and the DoCmd object allows the code to move on. Try creating the table using DAO or ADO and you should avoid this problem.

Craig

 

[klasse]

Hi,

Thanks for the help. However, I tried this:

********************************************
' create table with DAO to prevent delays in code.
Dim tdfNew As TableDef
Dim prpLoop As Property

' Create a new TableDef object.
Set tdfNew =CurrentDb.CreateTableDef(&quot;USAGE&quot
With tdfNew
' Create fields and append them to the new TableDef object.
.Fields.Append .CreateField(&quot;MyDate&quot;,dbDate)
.Fields.Append .CreateField(&quot;MyUsers&quot;, dbInteger)
CurrentDb.TableDefs.Append tdfNew
End With
********************************************

and then as before:

********************************************
' Put data into new table USAGE.
Dim MyRst As Recordset
Set MyRst = CurrentDb.OpenRecordset(&quot;USAGE&quot;, dbOpenTable)
With MyRst
   While arrayusage(m, 1) <> &quot;&quot;
     MyRst.AddNew
     Let !MyDate = arrayusage(m, 1)
     Let !MYUSERS = arrayusage(m, 2)
     MyRst.Update
     MyRst.MoveLast
     m = m + 1
   Wend
End With
********************************************

But I still get the same error!

Thanx,

Ignacio

 

[Guest_imported]

Sorry, I forgot to mention that I get the dialog with the error, click ok and then get the graph properly.

 

[llocklin]

I use these sql definition statements regularly without issue. However, I never use Currentdb without assigning it to a variable of type Database.

Having said that, I tried your code in an acc97 module, and after commenting out the undeclared vars and functions it worked as is (850 Mhz w/256Meg and Win2k). I have, on occassion, had what I believed to be timing problems in access. In every case, a loop while checking for object existance solved the issue. Something like:

while not tableexists(&quot;USAGE&quot
doevents
loop

Luther