DataDesignIT
asked on
Newbie Urgent Question on connecting from SQL Server Express 2005 to MS Access
Basic but urgent question... I've got SQL Server Express locally on my workstation. I also have an MS Acces database locally on my workstation. From SQL Server, I must run a series of SELECT statements against the Access database. I understand from the docs and online that I need to have a linked server. Apparently, I'm not getting the syntax right, however.
The MS Access DB has no security. The workstation user is 'Tom.'
The error I get on the 2nd statement below is:
Msg 15007, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 76
'Tom' is not a valid login or you do not have permission.
EXEC sp_addlinkedserver
@server = 'pcf'
, @Srvproduct = 'Access'
, @Provider = 'Microsoft.Jet.OLEDB.4.0'
, @datasrc = 'C:\mydata\Tom01JUL.mdb'
GO
sp_addlinkedsrvlogin 'pcf', false, 'Tom', 'Admin', NULL;
SELECT code
FROM pcf...shift
GO
Though not difficult, there's a level of urgency on this, so thus the higher point ranking.
The MS Access DB has no security. The workstation user is 'Tom.'
The error I get on the 2nd statement below is:
Msg 15007, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 76
'Tom' is not a valid login or you do not have permission.
EXEC sp_addlinkedserver
@server = 'pcf'
, @Srvproduct = 'Access'
, @Provider = 'Microsoft.Jet.OLEDB.4.0'
, @datasrc = 'C:\mydata\Tom01JUL.mdb'
GO
sp_addlinkedsrvlogin 'pcf', false, 'Tom', 'Admin', NULL;
SELECT code
FROM pcf...shift
GO
Though not difficult, there's a level of urgency on this, so thus the higher point ranking.
Sorry wrong post
i mean the server product to
@srvproduct = 'OLE DB Provider for Jet'
@srvproduct = 'OLE DB Provider for Jet'
ASKER
I changed the server name, since pcf already existed, and ran the following:
EXEC sp_addlinkedserver
@server = 'pcf1'
, @srvproduct = 'OLE DB Provider for Jet'
, @Provider = 'Microsoft.Jet.OLEDB.4.0'
, @datasrc = 'C:\mydata\Tom01JUL.mdb'
GO
I still got the same Msg 15007 on the next line:
sp_addlinkedsrvlogin 'pcf1', false, 'Tom', 'Admin', NULL
EXEC sp_addlinkedserver
@server = 'pcf1'
, @srvproduct = 'OLE DB Provider for Jet'
, @Provider = 'Microsoft.Jet.OLEDB.4.0'
, @datasrc = 'C:\mydata\Tom01JUL.mdb'
GO
I still got the same Msg 15007 on the next line:
sp_addlinkedsrvlogin 'pcf1', false, 'Tom', 'Admin', NULL
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
YIPEE! Thanks. I'm in! I didn't have a 'Tom' user in the SQL Server.
@provider = 'Microsoft.Jet.OLEDB.4.0',