ACCESS ADP TransferSpreadsheet

[Dave]

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.

[Razvan Socol]

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.

[Dave]

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.

[mcnewsxp]

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.

[Razvan Socol]

Hi, Dave

If you specify dbo.TableName, then Access will dumbly look for the
table username.[dbo.TableName]. Unfortunately, it's a bug in Access,
and I agree with the workaround that mcnewsxp wrote (however, running
sp_changeobjectowner requires at least db_ddladmin and db_securityadmin
priviledges in that database).

Another workaround is to have two tables, one for each user who will be
doing the export. For example, if you want to export the data from
TableName, create a dbo.Temp table and a username.Temp table and copy
the data from TableName in both. Then execute TransferSpreadsheet and
it will get the data from one of the Temp tables, depending on the
current user. I know, it's an ugly workaround, but it works...

Razvan

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.

[Dave]

Sorry I didn't respond to this sooner. We had to get a resolution
quick, so we just used SQL Reporting Services to push the export.

Thanks for everybody's help.
Razvan Socol wrote:

Hi, Dave

If you specify dbo.TableName, then Access will dumbly look for the
table username.[dbo.TableName]. Unfortunately, it's a bug in Access,
and I agree with the workaround that mcnewsxp wrote (however, running
sp_changeobjectowner requires at least db_ddladmin and db_securityadmin
priviledges in that database).

Another workaround is to have two tables, one for each user who will be
doing the export. For example, if you want to export the data from
TableName, create a dbo.Temp table and a username.Temp table and copy
the data from TableName in both. Then execute TransferSpreadsheet and
it will get the data from one of the Temp tables, depending on the
current user. I know, it's an ugly workaround, but it works...

Razvan

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.