Solved

Stored Procedure with Access

Posted on 2001-06-07
6
195 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

726 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