• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 264
  • Last Modified:

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?
0
LMGONCA
Asked:
LMGONCA
  • 5
  • 4
  • 3
  • +2
1 Solution
 
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
 
lluthienCommented:
how do you use the query?
what is the client-side?
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
mcmonapCommented:
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
 
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

Featured Post

Technology Partners: 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!

  • 5
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now