daveamour
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
angellll
What syntax do I use if I am using a n access database with no username and password please?
Cheers
Dave
What syntax do I use if I am using a n access database with no username and password please?
Cheers
Dave
ASKER
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
SELECT a.* FROM OPENROWSET('Microsoft.Jet.
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
OPENROWSET('Microsoft.Jet. OLEDB.4.0' ,
'c:\MSOffice\Access\Sample s\northwin d.mdb';'ad min';'', Orders)
'c:\MSOffice\Access\Sample
ASKER
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
Thanks anyway
Dave