Spreadhseet import using DoCmd.TransferSpreadsheet fails

J

J.Griffis

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
 
J

J.Griffis

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
 
J

J.Griffis

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.
 
G

gazzippy

I import spreadsheets by no longer using the TransferSpreadsheet comman
but using ADO instead.

I am now using ADO to create a connection to the Excel spreadsheet
then looping through each row adding it to the table in Access (usin
an ADP so it is an

SQL Server table)

The reason Access 2003 does not import them is that we currentl
specify the destination table for the spreadsheet as dbo.MyTable (wher
dbo is the table

owner) but in Access 2003 you do not specify the dbo bit (even thoug
when using the wizard to import you have to select the table with th
dbo bit, which

then fails, nice and consistent that!!).

If I remove the dbo bit Access 2003 then decides to import the table
but it checks to see if the table exists using the user name for th
connection to the

SQL Server box (in this instance the user name is MyUser) and the tabl
name (MyTable) so the table it looks for is MyUser.MyTable (no
dbo.MyTable), which

doesn't exist so Access 2003 creates it.

So all together now....... Thanks Microsoft for a new and improve
inconsistent product.


Dim rsDest As New ADODB.Recordset
Dim rsSource As New ADODB.Recordset
Dim oConn As New ADODB.Connection
Dim strSQL As String

strFName = "C:\Temp.xls"
strRange = "Sheet1$A1:AM42"

rsDest.Open "dbo.MyTable", CurrentProject.Connection
adOpenKeyset, adLockOptimistic
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Dat
Source=" & strFName & ";" & "Extended Properties=Excel 8.0;"
strSQL = "SELECT * FROM [" & strRange & "]"

rsSource.Open strSQL, oConn, adOpenKeyset
adLockOptimistic

'// Loop through spreadsheet adding record to dbo.MyTable
Do While Not rsSource.EOF

rsDest.AddNew

rsDest![Surname] = rsSource![Surname]
rsDest![Forename] = rsSource![Forename]

etc...



rsDest.Update
rsSource.MoveNext

Loop

'// Close Recordsets
Set rsDest = Nothing
Set rsSource = Nothing
Set oConn = Nothin
 
Top