asked on

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?

lluthien

SELECT FVIS001F.CONTITF, FVIS001F.CONCTIF, FVIS001F.CONAVAF, FVIS001F.NUMCONF
FROM FVIS001F

actually
SELECT CONTITF, CONCTIF, CONAVAF, NUMCONF
FROM FVIS001F

will do

LMGONCA

ASKER

Just that?.......and where do I specifiy the path for the access database?.............

lluthien

how do you use the query?
what is the client-side?

ASKER CERTIFIED SOLUTION

mcmonap

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LMGONCA

ASKER

VB.NET-> But it could be also Query Analyser. If it works against SQL Server I'm fine with it.

LMGONCA

ASKER

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...

mcmonap

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

lluthien

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

mcmonap

...you do not need a linked server for the openrowset function to work...

LMGONCA

ASKER

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?

ispaleny

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

mcmonap

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

Julianva

try this

SELECT *
FROM OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Driver={Microsoft Access Driver }',
'C:\access database\Direct_Payments.mdb;')

LMGONCA

ASKER

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 ... ;(

ispaleny

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.