Posted by mcnewsxp on 11/01/06 17:29

it has something to with Access not being able to see the fully
qualified table name correctly.
i have not found a fix except to run a stored proc that changes the
object owner on the database.

Dave wrote:
> Razvan...Thanks for the reply. I must have left that part out. Sorry.
> I did try including dbo.TableName. That also doesn't work. Onething
> that I've been talking over with coworkers is Office Versions. The
> Test User account that I used, I also used on an Office 2k3 machine.
> Everyone else is either using XP or 2K. I know that that shouldn't be
> an issue, but right now that's the only thing that's different.
>
> I have to try the Test User account on an XP or 2K machine.
>
>
>
>
> Razvan Socol wrote:
> > Hi, Dave
> >
> > I have encountered the same problem in a similar configuration (Access
> > XP Full/Runtime, MSDE 2000 SP3/SP4, TransferDatabase function). The
> > issue seems to be caused by the fact that MS Access looks for a
> > qualified table name, using the prefix given by the username of the
> > current connection. In other words, if the connection is using a login
> > that is member of the sysadmin server role or is db_owner for the
> > database, then it looks for the dbo.tablename. If the login corresponds
> > to a particular user in that database, it looks for the
> > username.tablename table.
> >
> > Razvan
> >
> > Dave wrote:
> > > Ok...here's a good one.
> > >
> > > I wrote an ADP application in ACCESS XP with a SQL 2000 back end.
> > > Works Great! Recently we did a server migration and transfered the
> > > database to another server. Setup all the groups and security without
> > > a hitch. My users change the Connection to point to the new server and
> > >
> > > all of a sudden the TransferSpreadsheet function that I'm using in two
> > > seperate procedures starts to error saying that it can't see the table.
> > >
> > > So I look...and there it is. The function doesn't see it though.
> > >
> > >
> > > I try it on my machine and it works fine, but I have Local Admin rights
> > >
> > > on the server though. So we start looking around. What we found is
> > > that only those with Local Admin Rights and Server Administration
> > > rights seem to be able to execute the procedure so that the
> > > TransferSpreadsheet works correctly.
> > >
> > >
> > > Initially I thought that it maybe the way the Server is configured, so
> > > we moved it to another location and tried it again with no luck. Tried
> > >
> > > putting it back on the original server and again, it works great!
> > > We've used SQL Compair to make sure that something didn't get changed
> > > in the process, and everything looks ok. I will admit that there may
> > > have been something in the way that the first server was configured,
> > > but it doesn't explaine why the function isn't working correctly now.
> > >
> > >
> > > All the machines that I tried it on are using the following:
> > >
> > >
> > > Desktops:
> > > XP Professional w/SP 1 or 2
> > > ACCESS XP
> > > MDAC 2.8
> > >
> > >
> > > Servers:
> > > Windows 2003 Server w/SP1
> > > SQL Server 2000 w/SP4
> > > or
> > > SQL Server 2005 w/SP1 - We've tried both.
> > >
> > >
> > > If anyone has any Idea's, I'm open.
> > >
> > >
> > > Thank for your time.

[Back to original message]