473,320 Members | 2,071 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Error 7874 Can't find the object tableName

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.
Nov 1 '07 #1
10 8550
NeoPa
32,556 Expert Mod 16PB
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.
Nov 1 '07 #2
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?
Nov 1 '07 #3
NeoPa
32,556 Expert Mod 16PB
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.
Nov 2 '07 #4
Jim Doherty
897 Expert 512MB
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 :)
Nov 2 '07 #5
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_cmdshell '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.
Nov 2 '07 #6
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.
Nov 2 '07 #7
NeoPa
32,556 Expert Mod 16PB
I'll do that Sami. It seems appropriate.
Nov 2 '07 #8
Jim Doherty
897 Expert 512MB
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_cmdshell '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/lib...0(SQL.80).aspx

http://www.novicksoftware.com/Articl...shell-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 :)
Nov 2 '07 #9
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_start_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/lib...0(SQL.80).aspx

http://www.novicksoftware.com/Articl...shell-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 :)
Nov 7 '07 #10
Jim Doherty
897 Expert 512MB
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_start_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 :)
Nov 7 '07 #11

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Rasta | last post by:
Hi, I've created a new table in a SQL Server db called 'LGBProdDetails' and I keep getting an error from my asp page that queries the table The error is: Category=Microsoft OLE DB Provider for...
0
by: gwaddell | last post by:
I have a stored procedure that is loading data into a global temp table. Here is all the code for that stored procedure. CREATE PROCEDURE AS SET NOCOUNT ON BEGIN
8
by: chippy | last post by:
Hi, I've a VB script that creates a Access object in a word doc. Here is the full script. It works for all but the Export. Which always fails with a 3011 error. If I do the same in Access as a...
1
by: kscdavefl | last post by:
When I try to display the fields associated with a table using the following code: private void fieldsButton_Click(object sender, System.EventArgs e) { string tableName =...
7
by: scorpion53061 | last post by:
This exception occured while running in the development environment. The debugger didnt catch it though it appeared as a message box prompt........does anyone have insight on this? ...
4
by: ink | last post by:
Hi All I am relatively new to doing this and i think that i am making some king of school boy error. The error i am getting is on the following line of code. XmlSerializer xs = new...
0
by: mbenedict | last post by:
I am rather new at this code and am attempting to modify existing code to use clob datatypes, which I have never used before. The database tables have been set up for clob data. When trying to use...
0
by: CoreyReynolds | last post by:
Hey all, I have a piece of code that dumps a bunch of data into a spreadsheet. Also rearranges it into a pivot table and then graphs the pivot table as well so my boss can get a clear view of the...
1
by: chienee | last post by:
here is the code <?php session_cache_limiter('private,must-revalidate'); session_start(); require_once("config.php"); include ("classes/db_functions.php"); include ("classes/display.php");
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.