Error in docmd.transferspreadsheet?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have SQL Server 2000 with access 2003 frontend. The following line of vba
code in access gives an error:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3,
"dbo.TM_ProjectMembers", strInputFileName, True, "sheet1!"

The code worked perfectly in Access 2000. After conversion of my front end
to Access 2003 I get an error.

I get runtime error 7874, error message stating that object
"dbo_TM_Projectmembers" cannot be found. Please note that tablename in error
message is changed. Seems that dbo. is changed to dbo_. TM_ProjectMembers is
the target import table.

When I remove dbo. from the vba code I get the same error 7874 stating that
table TM_ProjectMembers cannot be found. The table does exist and the user
has all kinds of access rights on the sql server. TM_ProjectMembers is a
unique tablename within the database. I am using windows integrated security.

Is there a workaround for this problem?

Regards

Tore G.
 
Tore said:
I have SQL Server 2000 with access 2003 frontend. The following line of vba
code in access gives an error:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3,
"dbo.TM_ProjectMembers", strInputFileName, True, "sheet1!"

The code worked perfectly in Access 2000. After conversion of my front end
to Access 2003 I get an error.

I get runtime error 7874, error message stating that object
"dbo_TM_Projectmembers" cannot be found. Please note that tablename in error
message is changed. Seems that dbo. is changed to dbo_. TM_ProjectMembers is
the target import table.

When I remove dbo. from the vba code I get the same error 7874 stating that
table TM_ProjectMembers cannot be found. The table does exist and the user
has all kinds of access rights on the sql server. TM_ProjectMembers is a
unique tablename within the database. I am using windows integrated security.

Is there a workaround for this problem?
Click to expand...

i could not find any way to import into an existing table. it seems
ADPs have trouble with the fully qualified name.
i had to use DoCmd.RunCommand acCmdImport and then run a stored proc to
Change Object Owner.
 
Hello Tore,

I understand that you received runtime error 7874 when trying to use
TransferSpreadsheet to import/append Excel sheet into a existing table. If
I'm off-base, please let me know.

Based on my test, I was not able to reproduce the issue. If I use a table
name excel1 and I have dbo.excel1 in sample database, it works find and
rows are appended correctly from excel.xls file.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, "excel1",
"c:\doc\excel1.xls", True, "sheet1!"

However, if I changed the table name to dbo.excel1, I found the table name
it tries to create is changed to "dbo.dbo_excel1" when it ran first time.
It created table dbo.dbo_excel11 when it ran second time and
dbo.dbo_excel12 in third time.

Note: I have to remove table dbo.excel1 and restart smaple ADP database to
get above results.

Currently I suggest that you tempoarily drop the existing
dbo.TM_ProjectMembers table in the database, and use the code directly to
create the table

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3,
"TM_ProjectMembers", strInputFileName, True, "sheet1!"

Note: You may want to restart the database to refresh connection before you
try to run the code to create dbo.TM_ProjectMembers. I once saw runtime
error 7874 when I deleted table directly in SQL Server other than from ADP.
I had to delete tables on both side and restart Access program to work
around it.

After that, you could run above code again to append the same data in Excel
into the table.

If the issue persists, please run profiler on SQL Server and use the
default events to catch the trace, you shall see insert tabel
dbo.TM_ProjectMembers ... statement in the profiler trace.

More related information:


208380 ACC2000: TransferSpreadsheet May Not Allow the Table Append Operation
http://support.microsoft.com/default.aspx?scid=kb;EN-US;208380

If you have any update, please feel free to let's know. I look forward to
your reply.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
I need to import from excel and append data to an existing table
TM_ProjectMembers on SQL Server. This table contains data and cannot be
deleted.

I will try the workaround suggested in one of the links you provided. That
is to import into a new temporary table. Then i will run a stored procedure
on SQL Server that appends data to TM_ProjectMembers from the temporary
table and finally deletes the temporary table.

This is a bit more complicated than my original solution. If it works I
guess my problem is solved.

Regards

Tore
 
Hello Tore,

Based on my test, if Excel has field name on the first line of sheet, I was
able to append the data in Excel to the existing table without problem. I
also test table name tm_excel1 without problems.

It seems there are issues on your system since the original
"dbo_TM_Projectmembers cannot be found" message is not supposed to appear
in the first place. Since you cannot drop original table, you could import
the data to a new table name such as tmpProjectMembers. You could also
create the table with the proper fields before you try to import the data.

I agree use a SP to append shall be a workaround. However, as you indicated
this has caused more overheads. I'd like to know the test result and please
let's know if you have any update. I look forward to your reply. Thank you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================


This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
 
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3,
"TM_ProjectMembersxx", strInputFileName, True, "sheet1!"

I get the runtime error 7874 no matter whet I try to do. I have tried to
import to a non existing table as well as an existing one. I have tried to
create a fresh table and run into the same problem.

I have tried to import via the file-menu in Access 2003 (File -> Get
external data->Import). It is not possible to do import. I get a message
indicating that an error occurred when I try to import to an existing table
and when I try to create a new table for the imported data.

I have logged on to SQL Server using Windows security. The user is currently
database owner.

What do you mean by "restarting the database"? Do I find this in the menus
of SQL server 2000 Enterprise manager?

Regards

Tore
 
do you have spaces in your spreadsheet name?

in your worksheet name?

give more detail here guys


-Aaron
 
Hello Tore,

I understand there are issues even when you try to import the table
directly via File->Get External data->Import. It seems there are enviorment
issue on this machine.

In order to narrow down the issue, I suggest that you try the following
steps:

1. Try to create a new Excel file, and add some simple fields/data to test
this new file

col1 col2
1 test
2 test1

2. Try to create a new ADP file with new a database on the SQL Server, and
try to import from the newly create Excel file

3. If possible, try on a different client machine with Access 2003
installed, and try to import the newly created Excel file.

4. If the issue still occurs, please try a different SQL Server as backend
and create a new database/adp to test.

Per your question, "restarting the database" I mean close and open the adp
file again.

If the issue occurs with new sql database/new simple Excel file, and it
does not occur on a different machine, it should be a problem on the client
machine. Based on my experience, I once saw similar issues with some
Anitviurs program running. You may troubleshoot the issue with a selective
startup. To do so:

1. Run the System Configuration Utility (msconfig.exe): Click Start->Run,
type "msconfig"(without quotation mark) in the open box, and click OK.

2. On the General tab, select "Selective Startup" and then deselect Load
Startup items, select the checks on other options.

3. On the Services tab, click Hide All Microsoft Services. Deselect all the
items left.

4. Click the Apply button.

5. Reboot your client computer to check if the issue occurs at this time.

If the issue occurs only with the specific database/sql server, it might be
some problem on this server/database, please use sql profiler to capture
the trace when you try to import the data, and send the result to me at
(e-mail address removed). Also, plesae grant the Windows user you log on the
client system admin role on SQL Server to isolate permission issues.

I look forward to your reply. Thank you!

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
 
I have tried all of your suggestions without any success at all. I have the
same problem on 2 different SQL Server 2000 databases, with the customer and
at the home office. I have never tried SQL Server 2005 because the customer
does not want to upgrade at the moment. I am not able to import to an
existing table or a new table on SQL Server 2000 from Access 2003 project,
only from Access 2000 project.

I have an Access 2000 project on my old laptop. I connect it to the SQL
Server 2000 database on another PC and it works fine. Import is possible via
menu File->Get External data->Import. I get a number of trace-lines on the
SQL Profiler.

I try to do the same thing from Access 2003 project on the same machine as
the SQL Server 2000 database. I get an error from the import spreadsheet
wizard stating "An error occurred trying to import file ........ The file was
not imported" I get only one single line on the sql profiler. EventClass =
SQL:BatchCompleted , TextData =
select object_name(id), user_name(uid), type, ObjectProperty(id,
N'IsMSShipped') from sysobjects where type in (N'U', N'S') and
permissions(id) & 4096 <> 0

When I run the sql from profiler in the sql query analyzer it gives an output:

sysobjects dbo S 1
sysindexes dbo S 1
syscolumns dbo S 1
systypes dbo S 1
syscommentsdbo S 1
sysfiles1 dbo S 1
syspermissions dbo S 1
sysusers dbo S 1
sysproperties dbo S 1
sysdepends dbo S 1
sysreferences dbo S 1
sysfulltextcatalogs dbo S 1
sysindexkeys dbo S 1
sysforeignkeys dbo S 1
sysmembers dbo S 1
sysprotects dbo S 1
sysfulltextnotify dbo S 1
sysfiles dbo S 1
sysfilegroups dbo S 1
Table1 dbo U 0
trace dbo U 0
Table2 dbo U 0
dtproperties dbo U 1


Tore
 
give us the exact code; the exact name of the spreadsheet.

are you pushing it into a single range?

you just need to give us more details; buddy

-Aaron
 
Hello Tore,

Thank you for your update. It seems that you did not add all events under
"Errors and warnings" and "Security audit" when using profiler to trace.
Please send the trace to me at (e-mail address removed) for reviewing.

I understand the issue only occurs on Access 2003 on your side and it even
persists on the local server of SQL 2000 when you tried to import Excel
data. From the trace log on my side, it seems Access 2003 tries to get some
information from master database when auditting object permission. To
isolate permission issue, I suggest that you grant the domain user account
you logged on to SQL Server "system admin" role to test. Also, please try
to use "sa" account instead of Windows authentication to test.

I understand your have experienced a lot on this issue, and I appreciate
your time. Thank you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
 
Hello Tore,

Thank you for the additional information. From the trace apparently access
did not do the necessary operations to SQL side and this might be caused by
some internal errors when running TransferSpreadsheet function.

Based on my further research, I found there was known issue regarding
TransferSpreadsheet function in RTM version of msaccess. Also, on my test
machine, Access 2003 was upgraded to sp2(11.6566.8036), and version of
msaccess.exe is (6566). Please install Office 2003 sp2 via the following
article

Description of Office 2003 Service Pack 2
http://support.microsoft.com/default.aspx?scid=kb;EN-US;887616

Or you could check Office update from the following link directly:

http://office.microsoft.com/en-us/officeupdate/default.aspx

If the issue is not resolved at this time, I suggest that you use the
method below to collect the DLL list of msaccess process to me:

1. Go to http://www.sysinternals.com/ntw2k/freeware/procexp.shtml click
Download Process Explorer (x86 - 77KB) - you plan on using Process Explorer
on WinNT/2K. Extract it and run the procexp.exe program.
2. Open Access, and use wizard to reproduce the issue until you see the
error message.
2. In the Processlist, select and highlight msaccess.exe.
3. In the toolbar, click "View DLLs" toolbar
4. Click File | Save As to export the DLLllst.
5. Send me a copy of the .txt file.

I look forward to your reply. Thanks.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Thanks for your responses to my request in usergroup access.adp.sqlserver,
"Error in docmd.transferspreadsheet?"
Please find enclosed tracefiles from SQL Profiler.

Errors and Warnings, Security Audit are switched on for all trace in
addition to the standard profile.

There is one tracefile showing the successful import to SQL Server 2000 of
excel sheet1ddddd from Access 2000. The adp client is on a remote laptop.

There are two tracefiles showing the unsucessful import to SQL Server 2000
from Access 2003. The adp client is on the same machine as the SQL Server.

I have installed reporting services on the machine. It generates some
additional trace traffic.

Regards

Tore Gylver
 
Back
Top