I'm using MSAccess 2k as a front end to a SQL Server 2k database.
I have a user set up in SQL Server to be database owner for a specific database.
Using NT authentication.
The MSAccess application executes a store procedure on the SQL Server that (1) deletes a tables if it exists, (2) creates and populates a table (the owner shows up in Enterprise Manager as the user name) and then (3) uses the table (read) in a join to do some other processing.
The (1) delete works fine.
The (2) table is created and populated fine.
On (3) the Select (inner join) to do the aditional processing I get an error message that the table name used is an invalid object.
If I create the table as sa separately and in the store procedure, instead of deleting, creating and populating the table I merely truncate the table and then repopulate it, everything works fine.
I would prefer to not have the table sitting around the database all the time.
Anyway, I'd like to figure out why the table is causing a problem.