Posted by Razvan Socol on 10/30/06 06:06

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]