Solved

Stored Procedure with Access

Posted on 2001-06-07
6
191 Views
Last Modified: 2006-11-17
I have a stored procedure which works with 2 tables.  However one of the tables is in an access database.  Can I connect to an access database through a stored procedure?

My sp so far is below

USE Planethealth_Development

DECLARE mycursor CURSOR
FOR
  SELECT * FROM tblEmails
OPEN mycursor

DECLARE @EmailID uniqueidentifier
DECLARE @Firstname varchar(40)
DECLARE @Surname varchar(40)
DECLARE @Email varchar(75)
DECLARE @Competition varchar(5)
DECLARE @Newsletter varchar(5)
DECLARE @Enquiry varchar(5)
DECLARE @Nutritional varchar(5)
DECLARE @Format varchar(4)
DECLARE @Comp_Date datetime
DECLARE @Comp_Answer varchar(100)
DECLARE @Nut_Question varchar(1000)

FETCH NEXT FROM mycursor INTO @EmailID, @Firstname, @Surname, @Email, @Competition, @Newsletter, @Enquiry, @Nutritional, @Format, @Comp_Date, @Comp_Answer, @Nut_Question

WHILE (@@FETCH_STATUS <> -1)
BEGIN
  IF (@@FETCH_STATUS <> -2)
    BEGIN    
      If NOT exists(Select * from tblEmails2 WHERE EmailID = @EMailID) Insert INTO tblEmails2 Values(@EmailID, @Firstname, @Surname, @Email, @Competition, @Newsletter, @Enquiry, @Nutritional, @Format, @Comp_Date, @Comp_Answer, @Nut_Question)
      PRINT @EmailID
    END
    FETCH NEXT FROM mycursor INTO @EmailID, @Firstname, @Surname, @Email, @Competition, @Newsletter, @Enquiry, @Nutritional, @Format, @Comp_Date, @Comp_Answer, @Nut_Question
END

CLOSE mycursor

DEALLOCATE mycursor

Many thanks

Dave

0
Comment
Question by:daveamour
6 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 50 total points
ID: 6163550
Check out this:

SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
   'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)
   AS a

The table "a" can now be used to be joined to as if it were a normal table.

Your above cursor might be a little faster like this:

insert into tblemails2
select * from tblEmails
where emailid not in ( select emailid from tblEmails2 )

Cheers


0
 
LVL 2

Expert Comment

by:agriggs
ID: 6164856
You can also use sp_addlinkedserver and link to any ODBC database.  Then you can always have immediate access to the Access database without having to do the openrowset first.

0
 
LVL 19

Author Comment

by:daveamour
ID: 6167040
angellll

What syntax do I use if I am using a n access database with no username and password please?

Cheers

Dave
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 19

Author Comment

by:daveamour
ID: 6167301
I have tried this

SELECT a.* FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\DataTables.mdb', tblEmails) AS a

but I get the following error

Server: Msg 7399, Level 16, State 1, Line 3
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.  
[OLE/DB provider returned message: Could not find installable ISAM

Any ideas??

Cheers

Dave
0
 
LVL 2

Expert Comment

by:niklausj
ID: 6178868
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
  'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'', Orders)
0
 
LVL 19

Author Comment

by:daveamour
ID: 6283119
I have long given up on this project and moved onto other stuff so I am just issuing points as I think your suggestion is good although I couldn't get it working

Thanks anyway

Dave
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now