SQL Server 2000 with Access 2000 VBA Can't find object 'st.....' stored procedure

Question

SQL Server 2000 with Access 2000 VBA Can't find object 'st.....' stored procedure

SQLServerVBA

SSC Journeyman

Points: 96
More actions
July 20, 2009 at 9:06 am

#208486

Hi,
We are using SQL Server 2000 with Access 2000 VBA . One of our power users, gets random errors
'Can't find object... 'stfrmInsurance'. Then it throws him out of the system. When he logs back again it is fine and he cannot reproduce that error for some. The errors are random and not necessarily on the same stored procedure / or object.
Any help will be highly appreciated.
Martha S

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323522 More actions · Answer 1

ive seen this before...make sure that user has a specific default database for his login, instead of master. that database should be whatever database he's using for the application.

i think the issue is sometimes your connection switches to master(no proof, but maybe when you run a procedure from the master database, sp_help or whatever)

sometimes when his database context is master, the procedures or tables cannot be found, logging in again, this time with a connection string for the production db,then fixes it for a while\, until that wierd step, like calling something from master switches the context again.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLServerVBA SSC Journeyman Points: 96 More actions · Answer 2

The User connects to the production database. There is only one database that the user connects and all tables & stored procedures are on that database ONLY. So there is no remote access. Master does not have any Sp or tables. How do I make sure that the database context does not switch.

Lowell SSC Guru Points: 323522 More actions · Answer 3

SQLServerVBA (7/20/2009)
The User connects to the production database. There is only one database that the user connects and all tables & stored procedures are on that database ONLY. So there is no remote access. Master does not have any Sp or tables. How do I make sure that the database context does not switch.

yes, that is exactly my point. the user is expected to connect to one and only one database, but there is an occasional disconnect between the "default" database of a login, and the connection he was using...maybe it's due to connection pooling and timeouts, I'm not sure.

here is exactly what I'm talking about:

here, i created a user "bob", and he has permissions to only one database...but his default is "master"

that is where your issue exists, I think.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLServerVBA SSC Journeyman Points: 96 More actions · Answer 4

Thanks for your reply. Should I change the User's default database as the Production database. Will this solve the problem.

Your time and suggestions are highly appreciated.

Martha

Lowell SSC Guru Points: 323522 More actions · Answer 5

yes; like i had said before, I'd seen the issue in the past. I could never track down exactly what was the cause, but I found that changing that default to the production database resolved the issue for us;

The actual cause was always a lot of speculation, that ranged from the items i mentioned to people cooking fish in the microwave at work(yeah, we were that desperate to pin it on someone!)

you can search forro some other threads here on SSC on hte same issue, and I'm sure you'll see the smae solution suggested.

Good Luck!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLServerVBA SSC Journeyman Points: 96 More actions · Answer 6

Thanks for your suggestion. I am going to make this change and hopefully it will solve our problem.

SQLServerVBA SSC Journeyman Points: 96 More actions · Answer 7

Hi,

I made that change to the default database to USer Login from Master to production as you suggested and it solved the problem with the stored procedure not found errro but a different problem has surfaced.

DBnetlib connection write (Wrapper Write) General Network error.

Have you seen this error ? Any solution ?

Mary

SQL Server 2000 with Access 2000 VBA Can't find object 'st.....' stored procedure

Cookies on SQLServerCentral