TransferSpreadsheet to .ADP dbo table

[D.Stone]

I'm getting a problem with importing an Excel spreadsheet into a table
in a SQL Server 2000 back-end. I'm doing this in VBA in an onClick
event proc in an Access 2003 project.

The target table's owner is 'dbo', but the database connection is via
user 'fred'. password 'bloggs' (say) using SQL Server authentication.
User 'fred' is a member of db_owner role.

What happens is that TransferSpreadsheet ignores the 'dbo' table and
creates a new version, owned by 'fred'. This has undesirable side-
effects downstream (the app crashes).

The TransferSpreadsheet (and spreadsheet file itself) seem to be fine,
as the data is imported OK when the existing target table is actually
owned by 'fred'.

Is there something (permission/grant?) that needs to be set to enable
'fred' to import into the 'dbo' table, or is there a problem with
TransferSpreadsheet?

Any help gratefully received!

Dave

[D.Stone]

On May 8, 3:59 pm, [e-mail] wrote:

I'm getting a problem with importing an Excel spreadsheet into a table
in a SQL Server 2000 back-end. I'm doing this in VBA in an onClick
event proc in an Access 2003 project.

Further info:

I thought it might help if I prefixed the target table name in the
TransferSpreadsheet call with 'dbo.', so the VBA line now reads:

DoCmd.TransferSpreadsheet acImport, , "dbo." & tempTab, strFilename,
True, "MyRange"

In fact, this gives an error (3078) because the table name generated
is 'dbo_mytab' instead of 'dbo.mytab'.

I also find that TransferText behaves the same, i.e. it creates a new
table belonging to 'fred' rather than use an existing one belonging to
'dbo'.

Cheers,

Dave

[Rich P]

Hi Dave,

The TransferSpreadsheet command is fine. The problem is with the ADP.
TransferSpreadsheet would work more reliably with an MDB.

With the mdb you can easily read your excel data into a local table with
Transferspreadsheet and then push that data to the sql server using ADO.

I would recommend using a separate mdb for importing your excel data if
you want to stay with TransferSpreadsheet. Otherwise, I would recommend
stepping up to .Net for interfacing with sql server and excel (much
easier).

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[lyle fairfield]

I connect SQL-Server and Excel this way:

1. I use the excel file as a linked server by running this Stored
Procedure (the parameter is the full path to the file):

CREATE Procedure [dbo].[LinkToOPEExcelFile]
@FileLocation nvarchar(255)
AS
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id !
= 0 AND srv.name = N'OPE_XLS')
EXEC master.dbo.sp_dropserver @server=N'OPE_XLS',
@droplogins='droplogins'

EXEC master.dbo.sp_addlinkedserver
@server = N'OPE_XLS',
@srvproduct=N'Excel',
@provider=N'Microsoft.Jet.OLEDB.4.0',
@datasrc=@FileLocation,
@provstr=N'Excel 8.0'

2. I create VIEW(s) accessing the table(s) resident in the XLS file
(one view):

CREATE VIEW [dbo].[North]
AS
SELECT *
FROM OPE_XLS...North$ AS North$_1

Now I can use the VIEW as the recordsource for forms or reports. An
advantage is that my data is now the data in the Excel file,
reflecting any change immediately.

Of course, I can make my own table with

"SELECT * INTO NorthSchools FROM North"

if I want the data to be static (not reflecting changes in the Execl
file).

TransferSpreadsheet is a powerful command. I would use it but I am not
sure that Access has kept it up=to-date as far as permissions go with
repect to folders and SQL_Server. An SQL-Server only solution seems
simpler and more likely to have no problems to me.
[e-mail] wrote:

I'm getting a problem with importing an Excel spreadsheet into a table
in a SQL Server 2000 back-end. I'm doing this in VBA in an onClick
event proc in an Access 2003 project.

The target table's owner is 'dbo', but the database connection is via
user 'fred'. password 'bloggs' (say) using SQL Server authentication.
User 'fred' is a member of db_owner role.

What happens is that TransferSpreadsheet ignores the 'dbo' table and
creates a new version, owned by 'fred'. This has undesirable side-
effects downstream (the app crashes).

The TransferSpreadsheet (and spreadsheet file itself) seem to be fine,
as the data is imported OK when the existing target table is actually
owned by 'fred'.

Is there something (permission/grant?) that needs to be set to enable
'fred' to import into the 'dbo' table, or is there a problem with
TransferSpreadsheet?

Any help gratefully received!

Dave

[D.Stone]

On May 9, 2:58 am, lyle fairfield <[e-mail]:

I connect SQL-Server and Excel this way:

1. I use the excel file as a linked server by running this Stored
Procedure (the parameter is the full path to the file):

CREATE Procedure [dbo].[LinkToOPEExcelFile]
@FileLocation nvarchar(255)
AS
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id !
= 0 AND srv.name = N'OPE_XLS')
EXEC master.dbo.sp_dropserver @server=N'OPE_XLS',
@droplogins='droplogins'

EXEC master.dbo.sp_addlinkedserver
@server = N'OPE_XLS',
@srvproduct=N'Excel',
@provider=N'Microsoft.Jet.OLEDB.4.0',
@datasrc=@FileLocation,
@provstr=N'Excel 8.0'

2. I create VIEW(s) accessing the table(s) resident in the XLS file
(one view):

CREATE VIEW [dbo].[North]
AS
SELECT *
FROM OPE_XLS...North$ AS North$_1

Now I can use the VIEW as the recordsource for forms or reports. An
advantage is that my data is now the data in the Excel file,
reflecting any change immediately.

Of course, I can make my own table with

"SELECT * INTO NorthSchools FROM North"

if I want the data to be static (not reflecting changes in the Execl
file).

TransferSpreadsheet is a powerful command. I would use it but I am not
sure that Access has kept it up=to-date as far as permissions go with
repect to folders and SQL_Server. An SQL-Server only solution seems
simpler and more likely to have no problems to me.

Thanks for that Lyle - that sounds like a powerful technique I should
check out for the future. Unfortunately time constraints on the
current project mean I'm likely to have to use an ownership boj. Think
you may well be right about the Transfer commands not being squeaky
clean.

BTW, shouldn't the great .NET debate be continued on a different
group ;-)

Cheers,

Dave

[Rich P]

>>Stupid and Lazy?

Well, I confess that I am not the brightest star in the sky - stupid?
hmmm - could be. Lazy? You betcha!!!

That is the beauty of .Net!!!!

It is precisely for the stupid and lazy. It makes us look like we are
the brightest starts in the sky. That is what I mean about easy.
Microsoft has taken the best elements of the Access paradigm and
integrated them into the .Net model. That is the whole point. You
think I can write complex MFC code (which is what Access and .Net are
all based on) ? You gotta be kidding! Well, actually, I have delved a
little into MFC (and Java and a few other technologies).

Access is still fundamentally a great tool. But it was just timne to
add OOP functionality to the model. How to do that? Build a whole new
model based on the old model. Adding OOP to the Access paradigm through
..Net has extended functionality by over 1000%, and .Net holds your hand
every inch of the way (well, at least VB.Net does - even C# to a
degree). It makes stupid guys look like they actually know what they
are doing. Between the .Net Error catching engine (which will slap your
hand every inch of the way and point out suggested fixes on the spot)
and the encapsulation of tons of API code, .Net reduces spaghetti code
significantly. STuff that could take 100 lines of code in VBA may take
only 10 lines in VB.Net (or even C# - well maybe 20 lines in C#).

AS for Transfer spreadsheet, one other trick would be to try automation.
You can use the Excel UsedRAnge property of the range object to read the
contents of the used range. This is kind of what TransferSpreadhsset
does. Then you can loop through the range object (from access) and read
each value of each row into a string and write that row to a table using
an ADO command object - something like this:

(make a reference to the Excel library)
Dim xl as New Excel.Application, wb as Excel.WorkBook, Sht as
Exce.WorkSheet, rng AS Excel.Range
dim cmd As New ADODB.Command, str1 AS String
Dim i As Integer, j As Integer

cmd.ActiveConnection = "..."

Set wb = xl.Workbooks.Open(...)
Set sht = wb.Sheets("Sheet1")
set rng = sht.UsedRange
For i = 1 to rng.rows.count
For j = 1 to rng.Columns.Count
if str1 <"" Then str1 = str1 & ", "
str1 = str1 & rng(i, j)
Next
cmd.CommandTExt = "Insert into your tbl Select " & str1
cmd.Excecute
Next
cmd.ActiveConnection.Close

In .Net you would use an OleDB DataAdapter and a SqlDataAdapter (these
are components of ADO.Net) to read from Excel and write to the Sql
Server DB. There is no looping involved which is similar to
TransferSpreadsheet which does not use looping - just a big data push.
Rich

*** Sent via Developersdex http://www.developersdex.com ***