Stored Procedure with Access

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

LVL 19
daveamourAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
agriggsCommented:
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
 
daveamourAuthor Commented:
angellll

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

Cheers

Dave
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
daveamourAuthor Commented:
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
 
niklausjCommented:
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
  'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'', Orders)
0
 
daveamourAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.