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 :)