Spreadhseet import using DoCmd.TransferSpreadsheet fails

G

Guest

ACC2003, WIN2K3 Server, SQL2K

Has anyone ever seen applications replace periods with underscores when
referencing a qualified database object?

VB clip:
Dim strStagingTable as string, strPath as String
strStagingTable = "dbo.FileImportStaging"
strPath = "C:\Sample.xls

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
strStagingTable, strPath, True, "Importable"

I get a run-time error 3078 "The Microsoft Jet database engine cannot find
the input table or query 'dbo_FileImportStaging'. Make sure it exists and
that its name is spelled correctly." (NOTE: the period replaced by an
underscore.)

When I run a trace on SQL Server, I find statements being run (these are
server-side processes, but the point is that the qualified object name is
still not correct):

exec sp_special_columns N'dbo_FileImportStaging', NULL, NULL, N'V', N'T', N'U'
exec sp_columns N'dbo[_]FileImportStaging', NULL, NULL, NULL

I am a sysadmin on this box and login as dbo, and all user objects are owned
by dbo.

Any ideas? Thanks in advance!
 
G

giorgio rancati

Hi J.Griffis,

in the TransferSpreadsheet method this Tablename "dbo.FileImportStaging"
work in Access2002, in Access2003 no.

if all user objects are owned by dbo, remove the owner in strStagingTable
----
strStagingTable = "FileImportStaging"
----

sorry for my english :)


Ciao
Giorgio
 
G

Guest

Thanks Giorgio. Unfortunately, not all users accessing those objects use the
dbo user account, so the owner references is still necessary.

Any other thoughts? Many thanks!


giorgio rancati said:
Hi J.Griffis,

in the TransferSpreadsheet method this Tablename "dbo.FileImportStaging"
work in Access2002, in Access2003 no.

if all user objects are owned by dbo, remove the owner in strStagingTable
----
strStagingTable = "FileImportStaging"
----

sorry for my english :)


Ciao
Giorgio


J.Griffis said:
ACC2003, WIN2K3 Server, SQL2K

Has anyone ever seen applications replace periods with underscores when
referencing a qualified database object?

VB clip:
Dim strStagingTable as string, strPath as String
strStagingTable = "dbo.FileImportStaging"
strPath = "C:\Sample.xls

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
strStagingTable, strPath, True, "Importable"

I get a run-time error 3078 "The Microsoft Jet database engine cannot find
the input table or query 'dbo_FileImportStaging'. Make sure it exists and
that its name is spelled correctly." (NOTE: the period replaced by an
underscore.)

When I run a trace on SQL Server, I find statements being run (these are
server-side processes, but the point is that the qualified object name is
still not correct):

exec sp_special_columns N'dbo_FileImportStaging', NULL, NULL, N'V', N'T', N'U'
exec sp_columns N'dbo[_]FileImportStaging', NULL, NULL, NULL

I am a sysadmin on this box and login as dbo, and all user objects are owned
by dbo.

Any ideas? Thanks in advance!
Click to expand...
Click to expand...
 
G

giorgio rancati

a workaround ?
-----
Dim strStagingTable As String, strPath As String
Dim strSql As String
strStagingTable = "FileImportStaging"
strPath = "C:\Sample.xls"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strStagingTable, strPath, True, "Importable"

strSql = _
"If Current_User<>'dbo' Begin " & _
" Insert dbo.FileImportStaging " & _
" Select * " & _
" From FileImportStaging; " & _
" Drop table FileImportStaging " & _
"End"

CurrentProject.Connection.Execute strSql
 
G

giorgio rancati

umm..
for most security
-----
Dim strStagingTable As String, strPath As String
Dim strSql As String
Dim owner As String

strStagingTable = "FileImportStaging"
strPath = "C:\Sample.xls"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strStagingTable, strPath, True, "Importable"

owner = DLookup("Current_User", "sysobjects")

If owner <> "dbo" Then
strSql = _
" Insert dbo.FileImportStaging " & _
" Select * " & _
" From " & owner & ".FileImportStaging; " & _
" Drop table " & owner & ".FileImportStaging "

CurrentProject.Connection.Execute strSql
End If
 
G

Guest

Thanks, but that still doesn't change the fact that Access seems to be
passing "dbo_FileImportStaging" instead of "dbo.FileImportStaging". This is
according to the SQL Trace I had running while attempting the import.

I'm thinking something buggy is going on with Access. I may attempt to
reinstall the app if I can't figure something out; I'm clearly passing one
parameter, but seeing a different parameter passed.
 
Top