Tables don't show immediatly

[LeonAtRC]

The code produces an error because Access doesn't see the table: tmp_Results
If I then open the database windows and press {F5} tmp_Results will show.

Is there another way to get Access to see the new table on the sql server?

    cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandText = "usp_CreateLoad_tmpResults_Table"
    cmd.CommandTimeout = 0
    cmd.CommandType = adCmdStoredProc
    cmd.Execute
    Set cmd = Nothing
    
    CurrentData.AllTables.Application.RefreshDatabaseWindow
    DoEvents
    
    DoCmd.TransferText acExportDelim, , "tmp_Results", ResultsFilePath, True

 

[cmmrfrds]

The code is creating an implicit transaction and it apparently does not get committed prior to your next action of the DoCmd. Try wrapping the code in an explicit transaction and committing after the cmd.execute. Or, if this is truely a temporary table then use the Temp Table area of SQL Server and drop it when you are done with it. This would be more efficient especially if you are leaving the default logging on for the inserts and deletes.

 

[LeonAtRC]

Thanks cmmrfrds, I'll give that a try. It certainly appears as though it should work.