ter79,
No great problem here, except that SQL hates saving references to remote DBs that are not linked servers.
The following code creates a link to an Access database, and will return 'Correct Syntax' when checked, but errors out when saved.
CREATE PROCEDURE [dbo].[usp_OPENROWSET_ACCESS]
AS
SELECT a.* FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\Documents and Settings\Tony\My Documents\Access\test.mdb';;, tblNAMES) AS a
GO
Not too good.
The same will occur if you try to save it as a view, but there is an undocumanted work-around for this.
Create a view (any syntax will work, choose a table on the database), and save it as: vw_OPENROWSET
Locate the view you just saved, and double-click it to open it in Properties view.
Copy and paste the following syntax in the view, replacing all the current syntax:
CREATE VIEW dbo.vw_OPENROWSET
AS
SELECT a.* FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\Documents and Settings\Tony\My Documents\Access\test.mdb';;, tblNAMES) AS a
and click 'OK'. (Change the database path and name to the one you use).
Open a new SProc, and paste the following syntax:
CREATE PROCEDURE [dbo].[usp_OPENROWSET_ACCESS]
AS
select * from vw_OPENROWSET
GO
than click 'OK'.
The SProc will now run as normal, referencing the view that runs the OPENROWSET command to the target Access database.
Hope this assists,
Logicalman
