Spreadhseet import using DoCmd.TransferSpreadsheet fails

  • Thread starter Thread starter Guest
  • Start date Start date
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!
 
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
 
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...
 
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
 
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
 
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.
 
Back
Top