Solved

Stored Procedure with Access

Posted on 2001-06-07
6
194 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 143

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

861 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