Newly Created Table no Available 1

[WebCodger]

With all the sophisticated stuff on this site, I'm embarrased to post this. Here's the code in its simple form:

Dim Con As New ADODB.Connection
Con.Open CurrentProject.Connection

Con.Execute "CREATE TABLE MyTable (MyColumn text(10));"

Con.Close
Set Con = Nothing

DoCmd.OpenTable "MyTable"

Resulting error message: "Microsoft Access cannot find the object "MyTable".

I did check for an answer (the Web, books, and help file). Everyone will tell you how to create a table but no mention of how to update the collection within the current subroutine. Refreshing the catalog didn't do it.

Can you help?

 

[psemianonymous]

This is the most fascinating question I've answered today, so believe me--it's not as simple as you say it is.

Try:

[tt]DBEngine.Idle dbRefreshCache
DoEvents[/tt]

before you open the table window. If this doesn't work, you can put in a crude hack where you just wait for some period of time, waiting for the database tables collection to 'catch up'. Hmm, maybe none of this will work. But try the above code.


Pete

 

[WebCodger]

Thanks for responding, Pete.
The code hangs up on "dbRefreshCache".
Error message: "Variable not defined"
Is there another reference I should have active?
You obviously have the right idea. If I put a long "For...Next" loop in the code, the tables collection does catch up.
You're also right about the crude hack.

But again thanks. At least now I know what I'm up against.

 

[psemianonymous]

maybe just do a DoEvents.

I think the dbRefreshCache is part of the DAO library, which you may not have installed.

Maybe the DoEvents will make the database catch up, but there's no telling.