Query working in MS-Access to access a diferent database not working in SQL Server.

I've the following query working in ACCESS:

SELECT FVIS001F.CONTITF, FVIS001F.CONCTIF, FVIS001F.CONAVAF, FVIS001F.NUMCONF FROM FVIS001F IN 'C:\Temp\GESREC.MDB'

Now I've moved the database into SQL server but still need to access that other database in Access (GESREC.MDB),....how can I do the same in SQL server?
LMGONCAAsked:
Who is Participating?
 
mcmonapConnect With a Mentor Commented:
Hi LMGONCA,

I think you will have to make the access database a linked server, I think all the information you should need is here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_12_6a44.asp
0
 
lluthienCommented:
SELECT FVIS001F.CONTITF, FVIS001F.CONCTIF, FVIS001F.CONAVAF, FVIS001F.NUMCONF
FROM FVIS001F

actually
SELECT CONTITF, CONCTIF, CONAVAF, NUMCONF
FROM FVIS001F

will do
0
 
LMGONCAAuthor Commented:
Just that?.......and where do I specifiy the path for the access database?.............
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
lluthienCommented:
how do you use the query?
what is the client-side?
0
 
LMGONCAAuthor Commented:
VB.NET-> But it could be also Query Analyser. If it works against SQL Server I'm fine with it.

0
 
LMGONCAAuthor Commented:
Is the linked server realy the only option? Because the file path and name can change and is normally in the local C drive of the workstation running the procedure that uses this query (local drives not shared)......so server doesn't even has direct access to it...
0
 
mcmonapCommented:
Hi LMGONCA,

You could also OPENROWSET, syntax is below (see example c), you can dynamically construct the query string to point to a db database location of your choice each time it is run:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_78z8.asp
0
 
lluthienCommented:
if you want it to work in QA, you need the linked server.
if you just want it to work in VB.NET,
you could keep two separate connections,
one to the SQLSERVER and one to the MDB file
0
 
mcmonapCommented:
...you do not need a linked server for the openrowset function to work...
0
 
LMGONCAAuthor Commented:
I'm trying this:

USE pubs
GO
SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
   c:\temp\GESREC.mdb';'';'', FVIS001F)
   AS a
GO


Database doesn't has a username and password and it gives the following error:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d:  Authentication failed.].

-----------------

What's the right syntax in this situation?
0
 
ispalenyCommented:
Try

SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\temp\GESREC.mdb;admin', FVIS001F)
   AS a

or

SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\temp\GESREC.mdb;admin;yourpwd', FVIS001F)
   AS a
0
 
mcmonapCommented:
Hi LMGONCA,

ispaleny's sample should work, access does have security - when you open a db and no passord is requested it is still using th db admin account, you may need to specify a blank password though:

SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
   'c:\temp\GESREC.mdb;admin';'admin';'', FVIS001F)
   AS a
GO
0
 
JulianvaCommented:
try this

     SELECT *
FROM OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Driver={Microsoft Access Driver }',  
'C:\access database\Direct_Payments.mdb;')
0
 
LMGONCAAuthor Commented:
This seams to oblige me to have Access installed in the server - which is not the case....I'll go trought the establishement of a different connection object ... ;(
0
 
ispalenyCommented:
OpenRowset('Microsoft.Jet.OLEDB.4.0', needs Jet driver installed. It is installed on almost every system, but your system can be an exception or you have an older version.
Current version is Jet 4.0 SP8
http://msdn.microsoft.com/data/downloads/updates/default.aspx

OR

Data file MDB must be accesible by SQL Server engine. Is this what you want to say? It can be also solved by changing SQL Server account to domain user, and sharing the MDB file.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.