refreshing databaseconnection after drop and creation of table

[Guest]

I'm facing troubles refreshing my databaseconnection.
I'm performing an import routine, part of the routine is dropping a table
and next recreate the table with some other fields. This is simply done by
pushing a button executing a procedure.

I have to go back to the database-window and do a manual refresh (the f5) to
fetch the table.
I'm using application.refreshdatabasewindow in my code after the procedure
has been executed but it doesn't refresh automatically. Anybody has a clue?

 

[Guest]

Some notes:
Using Access 2003 ADP / SQL Server 2005

 

[Sylvain Lafontaine]

Maybe a timing problem: try to call refreshdatabasewindow after a few
seconds of waiting. Another possibility would be to use
CurrentProject.CloseConnection followed by .OpenConnection instead of using
refreshdatabasewindow.

 

[Sylvain Lafontaine]

Also, I don't see the need for you to be able to view in the Database window
a table that is probably manipulated only with VBA code.

 

[Guest]

Sylvain when I do that my access project simply pops out of the screen.
When reopening I have to re-enter the connection information.
Doesn't seem to be the solution

I did the following after execution of the proc

currentproject.closeconnection
currentproject.openconnection

 

[Guest]

I'm calling the table from vba to be shown in a pivot view.

Dynamically a table is being generated through the proc which has to be
shown in the pivot view by pressing a knob

 

[Sylvain Lafontaine]

Well, you must provide a connection string to currentproject.openconnection.
I never tried it but you could save the connection string from
CurrentProject.Connection before calling the method .CloseConnection.

 

[JohanK]

Ok sylvain this piece of code worked, thanks for the hint, I included in here
for others people help

Dim objCurrent As Object

Set objCurrent = Application.CurrentProject

MsgBox "Temporary table has been deleted"

CurrentProject.CloseConnection
CurrentProject.OpenConnection objCurrent.BaseConnectionString, "SA",
"DejaN$10"