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]
@[EMAIL PROTECTED]
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 @[EMAIL PROTECTED]
'OPE_XLS',
@[EMAIL PROTECTED]
'droplogins'
EXEC master.dbo.sp_addlinkedserver
@[EMAIL PROTECTED]
= N'OPE_XLS',
@[EMAIL PROTECTED]
'Excel',
@[EMAIL PROTECTED]
'Microsoft.Jet.OLEDB.4.0',
@[EMAIL PROTECTED]
'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 re****ts. 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.
D.St...@[EMAIL PROTECTED]
wrote:
> I'm getting a problem with im****ting 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 im****ted 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 im****t into the 'dbo' table, or is there a problem with
> TransferSpreadsheet?
>
> Any help gratefully received!
>
> Dave
|
