?
Solved

Stored Procedure with Access

Posted on 2001-06-07
6
Medium Priority
?
199 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 150 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

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…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

762 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