Error 7874 Can't find the object tableName

[Sami Ward]

I have an an MSAccess data project front end linked to an SQL Server Database.
I keep getting Error number 7874: database cannot find the object tblname
when I look however the table is there.
I am calling a procedure which deletes all records in the table and then repopultates it with new records.
so the table is never deleted and recreated.
Once the procedure has run, then I transfertext the table to a local directory.

this works when I run it. However when I try and get the user to do it, they get the error - which doesn't make any sense, because the table is still there, and the procedure call has worked because the table has the selected information in there, it just won't transfer it???

Can anyone help out on this please?

thanks.

[NeoPa]

This should probably be a SQL Server thread rather than Access, but unless I miss my guess, this is a permissions issue. A table may well not be visible to the user unless they have been set up to access SQL Server.
As a developer, it's almost certainly been done for your account.

[Sami Ward]

Hi,
have checked permissions everywhere I can think of, for the user, and all looks fine.
I don't think you can use t transfertext method in access data project. there doesn't seem to be a way of creating a specification/template for export format, where I can tell it to use tab as a delimiter.
triedtput to method, but that does not give the required result in the text file.

any other ideas of how to get a table out into a text file delimited by tabs?

[NeoPa]

Would it surprise you to know I still think it's a permissions issue?
I can move this over to the SQL Server forum if you like. I can't see other ideas as helping at all if the access to the SQL Server table is denied anyway. Sorry.

[Jim Doherty]

I have an an MSAccess data project front end linked to an SQL Server Database.
I keep getting Error number 7874: database cannot find the object tblname
when I look however the table is there.
I am calling a procedure which deletes all records in the table and then repopultates it with new records.
so the table is never deleted and recreated.
Once the procedure has run, then I transfertext the table to a local directory.

this works when I run it. However when I try and get the user to do it, they get the error - which doesn't make any sense, because the table is still there, and the procedure call has worked because the table has the selected information in there, it just won't transfer it???

Can anyone help out on this please?

thanks.

What version SQL Server are you using? what version Access are you using?.. on what basis are you logging into SQL server from the client machines are you logging in using Windows integrated security? or standard SQL server permissions?. Are your users assigned to roles? if so what permissions do those roles have for your users compared with yourself who I would guess... is sysadmin role yes? Is this 'procedure' as you call it a 'stored procedure' on the server

These questions are necessary to try and assess your situation in that objects in SQL server rely on the owner creating them. The default owner is 'dbo' in SQL server. You can see this in enterprise manager the standard SQL Server GUI

You will appreciate without sufficient info any advice is blind here... my first simple question or statement would be this:

"If your user opens an Access data project and when they see the tables to which they have permissions do the tables show themselves in the database window with a 'dbo' suffix or not? because if it does then that is where your error lies basically. ADP files are terribly unforgiving when you do not reference a table with the actual owner ie: dbo.tblName in things like listboxes, comboboxes and other routines within code, this is particularly so in a multi user environment"

Why is this? you may ask well the simple answer is this users in SQL server can 'create tables' and other objects ie views having the same name essentially and SQL server distinguishes between them by reference to the OWNER hence the 'dbo' owner prefix in SQL server (cum 'suffix' in an ADP frontend yes confusing isnt it?).

If your SQL server is version 2000 and you do NOT have service pack 1 installed then your table objects for your non sysadmin users will be suffixed with a figure 1. This was a known issue and was fixed in Service pack 1.

(I am not saying this is your answer by the way I am merely illustrating that there can be more reasons than you might think as to why the Access project frontend file the ADP cannot see an explicitly named table called tblName for any users other than yourself who will have the correct permissions.

My suspicions are that you need to look at your 'roles' if you have any and the permissions assigned to the role again more closely and users assigned to those roles.

In terms of getting your data out to tab delimited in a way other than transfertext then yes that can be done at your frontend ADP file using VBA using open file for output methods where you write out your recordset very quickly indeed to a disk text file. Alternatively you could SQL Server BCP out your data super fast to a text file to a specified location on the server side.

It largely depends on what you need to do what your strategies are and where you need your data to go. if its a local directory then if that local directory is also mapped to the server then the BCP option server side could be scheduled to a job on a timer with no user intervention at all.

I use ADP project files extensively so if you get stuck get back to me, but at the moment your information, for me, is too generic and sketchy to enable me to target any one specific thing for you. What I do know is whatever the case this is resolvable.... its just a matter of tracking the issue of non recognition down and it will most probably be a simple tick or two here or there on the permissions side.


Regards

Jim :)

[Sami Ward]

Hi Jim,

thank you very much for your reply. we SQL Server 2000, and I have created an Access 2003 adp to link to the database in sql Server.
there is a group set up, which all relevant users are added to.
the group has access to read, write and execute queries.
everything works fine. all procedures seem to work ok, and users can create tables and update no problem.
the only issue I had was using the transferText method to get the table out into a text file. I have since found another way, by adding a line at the end of the stored procedure that populates the table,
the line is

exec master..xp_cmds hell 'bcp "SELECT * FROM MyDatabase.dbo. MyTable" queryout FileName.txt -c'

This executes a shell, and gets all the records in the table out to the specified text file. the default delimiter is tab which is just what I wanted. there was no way of specifying this in Access because I couldn't use an Export Specification, it just had no option to save it there.

Now the problem is the stored procedure runs fine the table is populated, but the text file is not created - but there are no errors, when the user runs it.
but works ok when I run it and the text file is created.

[Sami Ward]

Hi,
you can move this to SQL Server forum if you like, I don't mind.

thanks.

Would it surprise you to know I still think it's a permissions issue?
I can move this over to the SQL Server forum if you like. I can't see other ideas as helping at all if the access to the SQL Server table is denied anyway. Sorry.

[NeoPa]

I'll do that Sami. It seems appropriate.

[Jim Doherty]

Hi Jim,

thank you very much for your reply. we SQL Server 2000, and I have created an Access 2003 adp to link to the database in sql Server.
there is a group set up, which all relevant users are added to.
the group has access to read, write and execute queries.
everything works fine. all procedures seem to work ok, and users can create tables and update no problem.
the only issue I had was using the transferText method to get the table out into a text file. I have since found another way, by adding a line at the end of the stored procedure that populates the table,
the line is

exec master..xp_cmds hell 'bcp "SELECT * FROM MyDatabase.dbo. MyTable" queryout FileName.txt -c'

This executes a shell, and gets all the records in the table out to the specified text file. the default delimiter is tab which is just what I wanted. there was no way of specifying this in Access because I couldn't use an Export Specification, it just had no option to save it there.

Now the problem is the stored procedure runs fine the table is populated, but the text file is not created - but there are no errors, when the user runs it.
but works ok when I run it and the text file is created.

Sami,

Your problem is going to be with the running of the xp_cmdshell process which is an extended sproc held in the MASTER database and runnable ONLY by people having sysadmin role permissions of which I can assume your users are not members of.

I take it obviously not all your users have sysadmin permissions therefore in order to actually run this process you will need to setup a PROXY account in SQL Server that impersonates a sysadmin and runs the process on their behalf.

Rather than me reinvent the wheel here, below is a couple of web references that explain the way you need to go directionally so to speak

http://msdn2.microsoft.com/en-us/library/aa260700(SQL.80).aspx

http://www.novicksoftware.com/Articles/SQL-Server-2000-SP3-and-xp_cmdshell-Woes.htm

As an aside... (because you say you have rectified your text file write out by invoking BCP on the server) if you DO need to write out the text file tab delimited locally and wish to control that at the client side enabling users to save it to a drive/folder of their choice then I can advise and provide you with the VBA to do that if necessary if the server side BCP write out ended up being a compromise.

Regards

Jim :)

[Sami Ward]

Hi Jim,

Thank you very much for your reply. I tried looking at the proxy thing, but after much fiddling about couldnot get the thing to work. Set up a DTS instead and called that from the procedure by using the
EXEC msdb.dbo.sp_sta rt_job @job_name = 'dtsPackageName '

and the same happened worked for me but not the user - because this need to use a proxy account too.
in the end I went to the Security section in the SQL Enterprise Manager, went to Logins, and found the relevant group, clicked on Properties, and went to the Server Roles tab, and ticked the box that said System administrator. That got everything working nicely thank you.

The users are not going to be doing anything in the system, they would just be using the front end application - so that has solved the problem.

hooray!
------------------------------------------------------------------

Sami,

Your problem is going to be with the running of the xp_cmdshell process which is an extended sproc held in the MASTER database and runnable ONLY by people having sysadmin role permissions of which I can assume your users are not members of.

I take it obviously not all your users have sysadmin permissions therefore in order to actually run this process you will need to setup a PROXY account in SQL Server that impersonates a sysadmin and runs the process on their behalf.

Rather than me reinvent the wheel here, below is a couple of web references that explain the way you need to go directionally so to speak

http://msdn2.microsoft.com/en-us/library/aa260700(SQL.80).aspx

http://www.novicksoftware.com/Articles/SQL-Server-2000-SP3-and-xp_cmdshell-Woes.htm

As an aside... (because you say you have rectified your text file write out by invoking BCP on the server) if you DO need to write out the text file tab delimited locally and wish to control that at the client side enabling users to save it to a drive/folder of their choice then I can advise and provide you with the VBA to do that if necessary if the server side BCP write out ended up being a compromise.

Regards

Jim :)

[Jim Doherty]

Hi Jim,

Thank you very much for your reply. I tried looking at the proxy thing, but after much fiddling about couldnot get the thing to work. Set up a DTS instead and called that from the procedure by using the
EXEC msdb.dbo.sp_sta rt_job @job_name = 'dtsPackageName '

and the same happened worked for me but not the user - because this need to use a proxy account too.
in the end I went to the Security section in the SQL Enterprise Manager, went to Logins, and found the relevant group, clicked on Properties, and went to the Server Roles tab, and ticked the box that said System administrator. That got everything working nicely thank you.

The users are not going to be doing anything in the system, they would just be using the front end application - so that has solved the problem.

hooray!
------------------------------------------------------------------

Thanks for posting your result Sami. (It benefits others viewing)

By doing that you are giving total server permissions to those users and therefore there is nothing to stop any one of them deleting objects and doing anything they like on the server but I'm sure you realise security risk anyway.

Jim :)