Newly Created tables

[Barbara Musante]

I have a situation where I need to create a new table in the current sql
database in my access project and then use the contents of the table in other
parts of the project. It seems that the project cannot see the new table
unless I disconnect and re-connect the ADO connection. Is there another way
to do this?

thanks

 

[Tom Ellison]

Dear Barbara:

Your experience does not match mine. I am able to use a table immediately
after creating it, provided it is in a database to which I am connected.

Could you please share the detaila of how you are creating this table, how
you are subsequently addressing the table, and what specific error it is
generating? Perhaps someone will then see what is wrong.

Tom Ellison

 

[Tom Ellison]

for "detaila" please read "details"

Tom

Dear Barbara:

Your experience does not match mine. I am able to use a table immediately
after creating it, provided it is in a database to which I am connected.

Could you please share the detaila of how you are creating this table, how
you are subsequently addressing the table, and what specific error it is
generating? Perhaps someone will then see what is wrong.

Tom Ellison

 

[Barbara Musante]

Hi Tom,

Thanks for the response. The way I create and use the table is this. I
first execute a stored procedure that creates the table using a select
......into tblstorestatus. After that I attempted to do a docmd.transfertext
on this table to create a text file of the information. Unless I do a
application.currentproject.closeconnection followed by a
application.currentproject.openconnection, the transfertext operation gets an
error that it cannot find the object.

I hope that this explains the situation and also hope that you can help.
--
Barbara Musante
Partner

Dear Barbara:

Your experience does not match mine. I am able to use a table immediately
after creating it, provided it is in a database to which I am connected.

Could you please share the detaila of how you are creating this table, how
you are subsequently addressing the table, and what specific error it is
generating? Perhaps someone will then see what is wrong.

Tom Ellison

 

[Sylvain Lafontaine]

Maybe making a call to Application.RefreshDatabaseWindow will solve this.
There is also the possibility of a timing problem: if you make the call from
ADP too fast after the creation of the new table, you won't still see it
because SQL-Server didn't have the time to finalize the operation; so you
may have to introduce a short delay between these two operations.

See the post from Ron Hinds on
http://groups.google.ca/group/micro...RefreshDatabaseWindow&rnum=3#938eac123effb43c
to see how to call the Sleep() function from ADP (however, I don't know if
this will work for you).

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

Hi Tom,

Thanks for the response. The way I create and use the table is this. I
first execute a stored procedure that creates the table using a select
.....into tblstorestatus. After that I attempted to do a
docmd.transfertext
on this table to create a text file of the information. Unless I do a
application.currentproject.closeconnection followed by a
application.currentproject.openconnection, the transfertext operation gets
an
error that it cannot find the object.

I hope that this explains the situation and also hope that you can help.

 

[Barbara Musante]

Thank you for the suggestion. I think that this will work. I am assuming
that you can use the refresh command even in an .ade.

Thanks again.