?
Solved

getting an error "microsoft access cannot find object "oapr_ImportSafer"

Posted on 2010-09-07
14
Medium Priority
?
766 Views
Last Modified: 2012-06-27
We switched the adp to a replication of the original database and now when we try to run an DoComd.openStoredProcedure "oapr_ImportSafer" the error Microsoft access cannot find the object "oapr_ImportSafer" appears. Also all of the queries now have an (dbo) at the end of thier name in the query object box.
0
Comment
Question by:Keking
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 3
  • +1
14 Comments
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 33618496
Keking,

Probably "oapr_ImportSafer" is not a valid procedure name or it is not created.

One thing I noticed, or maybe it is only typographical error in your part, the code you run must be:
DoCmd.OpenStoredProcedure "ProdedureName"

Ed
0
 
LVL 85
ID: 33618533
You'll need to strip off the "dbo." from the query, or change all your code to reference that new name. Here's a small routine that will remove the preceding dbo.:

Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database

Set dbs = Currentdb

For each qdf in dbs.QueryDefs
  If Left(qdf.Name, 4) = "dbo." then
    qdf.Name = Right(qdf.Name, Len(qdf.Name) - 4)
  End If
Next

Test this FIRST on a copy of the db to make sure it works as planned (and make a Backup before doing ANYTHING).
0
 
LVL 35

Expert Comment

by:David Todd
ID: 33620509
Hi,

You said that it is a replicated copy of the database - did the stored procedures replicate? Normally it is tables and data and a few indexes that replicate. I'm picking that maybe a backup and restore for testing would work better in this instance.

HTH
  David
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Keking
ID: 33621282
well it is a pieced together database. I copied all the queries over by hand. it is a long story... As long as I continue to use the adp from my computer I can switch betweent the two sql servers without a problem. but as soon as I copy the adp to another users computer I run into this problem. Is it a permissions problem?
0
 
LVL 35

Expert Comment

by:David Todd
ID: 33631191
Hi,

How is the adp connecting to SQL? SQL Authentication - with a SQL username/password, or Trusted/Windows authentication?

That is, I agree with your suspicion that the other user doesn't have the rights or permissions that you do on the SQL Server. Especially if the database in question is cobbled together by yourself, then permissions may not have been a priority.

Regards
  David
0
 

Author Comment

by:Keking
ID: 33640041
It is using a Trusted / windows connection. We have access to the sql server as administrators..but the the other user does not. We are hoping we don't have to make him an adminstrator.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 33640137
Hi,

You don't need to make the other use an administrator.

Rough Steps
Grant access to database
Grant select to table(s)|view(s)

HTH
  David
0
 

Author Comment

by:Keking
ID: 33645709
We are having trouble locating the Grant select to tables(s)view(s). We are using management studio 2005 to access sql server 2000.  any suggestions?
0
 
LVL 35

Expert Comment

by:David Todd
ID: 33650858
Hi,

On the views or tables in question, execute the query

grant select on dbo.TableName to user

HTH
  David
0
 
LVL 85
ID: 33657890
Note also you can review permissions by rightclicking on the object, select Properties, and then review the Permissions settings (in the left side menu). This machine has Management Studio Express, but the dialog should be the same.
0
 

Author Comment

by:Keking
ID: 33684567
is there a way to grant this to all the views at once? doing this one at a time for each user is a large task.
0
 
LVL 35

Accepted Solution

by:
David Todd earned 2000 total points
ID: 33685477
Hi,

There are several methods for managing users.

The first is to not use the user, but use the windows groups.

The second is to use the roles within the database, combined wiht the schemas. That is, add the Human resources group to the data reader role for the database, and the datawriter role for the HR schema.

If it is a big job, suggest using scripts, which can be corrected and rerrun.

I get the feeling you should get a database consultant to look over your shoulder at what you are doing. It appears that there are some fundementals missing in your setup that would make your job a lot easier.

Regards
  David

0
 
LVL 85
ID: 33687436
0
 

Author Closing Comment

by:Keking
ID: 33719928
thank you for all  you help on this.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question