On May 9, 2:58 am, lyle fairfield <lyle.fairfi...@[EMAIL PROTECTED]
> wrote:
> 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]
> @[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.
>
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 owner****p 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
|
