Refreshing Table Lists Etc,

[jottiwell]

Hello,

I'm running an Access .adp front end with an sql7 back end, I'm running stored procedures from code in Access, these procedures are creating tables on the SQL server. These tables are then used for further operations. The problem is that when the StoredProc executes and creates the table X, access cannot see or make reference to table X until I manually hit F5 over the database window to refresh the list of tables. Does anyone know a way to do this automatically?, application.refreshdatabasewindow doesn't work, I can call that nothing appears, then press F5 and they all appear. The same thing is happening when I delete a table, it remains visible and accessable until I press F5.

Please help,

John

[paulw]

Try looking at TableDefs.Refresh, or the RefreshLink topics. If push comes to shove use SendKeys{"F5"}

Cheers,

Paul.

[jottiwell]

Paul,

Thanks for the response, I'm using an SQL backend though, in an .adp, as far as I know the tabledefs and refreshlink actions only exist for JET ( DAO ) environments, the sendkeys idea may no be a bad one, I'm just trying o work out how to aply the refresh to the 'database window' rather than whatever's got the focis in Access at the time you call the sendkeys, any ideas how to do this, ( bear in mind that the database window is hidden ).

Thanks

John

[paulw]

OK, moron time, What is an .adp file?

Tabledefs etc are Jet but you are using ADO/OLEDB right? There must be some analagous property. Are the tables from SQL Server linked or am I way off track?

I may be getting in too deep here <glug, glug, glug>

Paul.

[jottiwell]

Hello,

Thanks for the reply again. An .adp file is an Access project ( the type created when you select a new database, then choose ( project existing database ) ). You then establish a connection property to a suitable database ( such as SQL ), you then have a set of tables, views, stored procs etc from the SQL server ( these are stored on the SQL server not in the .adp file ), then forms, reports, modules etc. that ARE stored in the .adp. There's no longer any 'linked tables' extensions or anything like that, tabledefs not longer exist because the definitions are on the server. You haveto design and alter the tables in SQL enterprise manager or by useing TRANSACT-SQL commands.

You probably knew that already, the basic problem I seem to be having is that Access does not update and refresh it's links to SQL server as I would expect it to, if you create a table from a query in an Access .mdb you immediately see the resulting table in the Access database window and can get at the table immediately in vba code, however if you call a storedproc in SQL, which creates the table, Access can't 'see' the table until you SOMEHOW refresh the table list!..... arrrgh which is what I can't do......

Ta for the reply, please get back again if you've any more ideas.

Thanks

John