Connecting to MS Access Database via Stored Procedure 2

[ter79]

I have a stored procedure that I need to create, I have to connect to an Access Database that resides on a network share.

I want to basically pull data, as if I was running
"Select * OpenROM OPENROWSET ('SQLOLEDB', 'ServerA'; 'sa';'', 'SELECT * From tableA')

How would I create the above for Microsoft Access DB

Thanks in advance!!!

 

[SQLBill]

Post this in the Microsoft Access forum. This is the Microsoft SQL Server forum.

-SQLBill

 

[ter79]

But I'm trying to create a SQL Stored procedure

 

[LogicalmanUS]

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

 

[SQLBill]

ter79,

Are you trying to create an Access or a SQL Server stored procedure?

Microsoft Access and Microsoft SQL Server use different forms of SQL. SQL Server uses Transact-SQL and not all TSQL commands work in Access.

-SQLBill

 

[LogicalmanUS]

SQLBill,

I think ter79 needs clarification of terms here.

SQL Server is (predominantly) a server-side application.
Access is (predominantly) a Client-side Application.

Stored Procedures (SProcs) and Views reside solely in SQL Server.
Queries reside solely in Access MDB files.

An Access Data Project (ADP file) is the hybrid application that is created on the client side, without local tables per se, and is linked directly to an SQL Server database. It has no queries, but can directly access and create Views and SProcs on the SQL Server.

Projects do, however, retain Forms, Reports, Web Pages, Macros and Modules (both Global and Class) and these can be used to interact with the server side.

Unfortunately, with the advent of the ADP, Microsoft managed to smear the line between Access and SQL whereby some Access questions are in the SQL Domain, if they refer to tables, views or SProcs.

As you say, some TSQL statements differ from their Access SQL equivalents, but usually when used in the Class or Global Module scenarios.

Hope this helps clear up any confusion.

Logicalman

 

[ter79]

Thanks

 

[ter79]

I'm trying to connect to Access Tables via SQL Stored Procedure. Logicalman answered my question.. Also sorry for the confusion

 

[GS2]

Thanks Logicalman for your work around!! I have been attempting to use linked server and the opendatasource and I kept getting an error "Error 7405:Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection....." When I tried your work around about putting it into a view and then selecting the view it worked like a charm!!

By the way, would anybody have a clue as to why I would have received that error message?

Thanks! GS2

 

[sunaj]

Old thread - but exactly what I was looking for. thx. LogicalmanUS


Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'