Connect Query Analyser and to MSAccess

[Dennis Wetherall]

Using SQL Server 7.0, how can I connect Query Analyser to
a local MS Access 2002 database? I started with
Enterprise Manager by attempting to creating a remote
server. Is that the right approach?

I created a system DSN called ptc1.

I have been unsuccessful so far. It tried serveral of the
providers: ODBC for Jet , ODBC Simple provider. I keep
getting ODBC errors.

I have used the data view tool in VB6 to build the
provider string.

Provider=MSDASQL.1;Persist Security Info=False;Extended
Properties="DBQ=C:\Documents and Settings\Dennis\My
Documents\MyCompany\ClientContracts\TrainProdCorp\Mas90Dow
nload\BDP MAS90 010804.mdb;DefaultDir=C:\Documents and
Settings\Dennis\My
Documents\MyCompany\ClientContracts\TrainProdCorp\Mas90Dow
nload;Driver={Microsoft Access Driver
(*.mdb)};DriverId=25;FIL=MS
Access;FILEDSN=C:\Program Files\Common Files\ODBC\Data
Sources\a.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout
=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Y
es;"

 

[Billy Yao [MSFT]]

Hi Dennis,

Thank you for using MSDN Newsgroup! My name is Billy and I will be assisting you on this
issue.

From you description, I understand that you would like to use Query Analyzer to connect a
local MS Access 2002 database in SQL Server 7. However, you failed to create a remote
server with ODBC for Jet provider in Enterprise Manager. Have I fully understood you? If there
is anything I misunderstood, please feel free to let me know.

Based on my experience, if you'd like to perform ad hoc connection information as part of a
four-part object name without using a linked server name, it is recommended that you use
OpenDataSource directly as below:

----------------------------------------------------------------------------------
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="C:\Program Files\Microsoft Office\2002\Office10\Samples
\Northwind.mdb";User ID=Admin;Password=')...Customers
----------------------------------------------------------------------------------

The script returns the recordset with all the columns of the table Customers in the database
Northwind.

On the other hand, if you prefer using Enterprise Manager to create a remote server for
Distributed Queries, you'd better use Microsoft Jet 4.0 OLE DB Provider to add a linked server
and point out the Data Souce (Full path name of Jet database file). After that, you can use
OPENQUERY to execute the specified pass-through query on the given linked server. The
sample script is like:

----------------------------------------------------------------------------------
SELECT *
FROM OPENQUERY([Acc_Northwind],'select * from Customers')
-- [Acc_Northwind] is the name of the OLE DB Linked Server.
----------------------------------------------------------------------------------

Dennis, does that answer your question? Please apply my suggestions above and let me
know if this helps resolve your problem. If there is anything more I can do to assist you, please
feel free to post it in the group. I am standing by to be of assistance.

Best regards,

Billy Yao
Microsoft Online Support

 

[Dennis Wetherall]

These are interesting approaches. I think a successfully
creating a remote server would be more suitable for my
needs however.

To define a linked server form the Enterprise Manager
what do I enter for the Product name, Provider string and
Datasource fields?

Dennis Wetherall

-----Original Message-----
Hi Dennis,

Thank you for using MSDN Newsgroup! My name is Billy and

I will be assisting you on this

issue.

From you description, I understand that you would like

to use Query Analyzer to connect a

local MS Access 2002 database in SQL Server 7. However, you failed to create a remote
server with ODBC for Jet provider in Enterprise Manager.

Have I fully understood you? If there

is anything I misunderstood, please feel free to let me know.

Based on my experience, if you'd like to perform ad hoc

connection information as part of a

four-part object name without using a linked server

name, it is recommended that you use

OpenDataSource directly as below:

--------------------------------------------------------- -------------------------
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="C:\Program Files\Microsoft Office\2002 \Office10\Samples
\Northwind.mdb";User ID=Admin;Password=')...Customers
--------------------------------------------------------- -------------------------

The script returns the recordset with all the columns of

the table Customers in the database

Northwind.

On the other hand, if you prefer using Enterprise

Manager to create a remote server for

Distributed Queries, you'd better use Microsoft Jet 4.0

OLE DB Provider to add a linked server

and point out the Data Souce (Full path name of Jet

database file). After that, you can use

OPENQUERY to execute the specified pass-through query on the given linked server. The
sample script is like:

--------------------------------------------------------- -------------------------
SELECT *
FROM OPENQUERY([Acc_Northwind],'select * from Customers')
-- [Acc_Northwind] is the name of the OLE DB Linked Server.

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

anything more I can do to assist you, please

 

[Billy Yao [MSFT]]

Hi Dennis,

Thank you for letting me know your concern and it's my pleasure to be of assistance.

To define a linked server with MS Jet 4.0 OLE DB provider form the Enterprise Manager,
the only thing you need to point out is the Data Souce. Please enter the Full path name of
your Access database file like C:\Program Files\Microsoft Office\2002\Office10\Samples
\Northwind.mdb

After that, you can use OPENQUERY to execute the distributed queries as I mentioned
before. Additionally, you can use sp_linkedservers to get the detailed informatin of the
linked server you created.

203638 HOWTO: Return Information About SQL Server Linked Servers
http://support.microsoft.com/?id=203638

Thank you for using MSDN Newsgroup! If there is anything more I can do to assist you,
please feel free to post it in the group.

Best regards,

Billy Yao
Microsoft Online Support

 

[Dennis Wetherall]

I think there must be something missing.

My questions were how do I set up a remote server. And
what are the values I should enter for the for following
fields when creating the remote server from enterprise
manager.
1)Provider String
2)Product name
3)Data Source
Your response, if I understand it, was that I need only
enter a value for the the data source field.

I have attempted to folow your guidance and I continue to
get the same generic ODBC error message I have been
receiving since my initial newsgroup posting.

Also, your answer continues to rely on the openquery()
SQL syntax, despite my statement that I want to use a
linked server instead of an Openquery() or
Opendatasource() syntax.

I want to avoid the kind of clutter in my SQL that an
Openquery() or OpenDatasource() approach entails for
reasons that I can proivde to you if necessary.

It would be most helpful to me if you could provide an
example of how you created a remote server in Enterprise
Manager that points to a local database, say
Northwind.mdb. If you could tell me what values you
provided for these fields (as I enumerated above), then I
can figure out how to create such a remote server for my
access database.

Thank you for your willingness to help.

Dennis Wetherall

-----Original Message-----
Hi Dennis,

Thank you for letting me know your concern and it's my pleasure to be of assistance.

To define a linked server with MS Jet 4.0 OLE DB

provider form the Enterprise Manager,

the only thing you need to point out is the Data Souce.

Please enter the Full path name of

your Access database file like C:\Program

Files\Microsoft Office\2002\Office10\Samples

\Northwind.mdb

After that, you can use OPENQUERY to execute the

distributed queries as I mentioned

before. Additionally, you can use sp_linkedservers to

get the detailed informatin of the

linked server you created.

203638 HOWTO: Return Information About SQL Server Linked Servers
http://support.microsoft.com/?id=203638

Thank you for using MSDN Newsgroup! If there is

anything more I can do to assist you,

 

[Billy Yao [MSFT]]

Hi Dennis,

Thank you for your feedback! It's clear to me that you prefer setting up a remote server to
using Query Analyzer to connect a MS Access database.

You've fully understand me that the field you should only fill in is 'Data Source', which needs
a full path and name of your Access database file. To exclude another possible
misunderstanding, I'd like to provide the following detailed steps for your benefits:

1. Expand your server, and then expand the Security folder in Enterprise Manager

2. Right click Linked Servers, and then select New Linked Server in the menu

3. In the General pan of the Linked Server Properties dialog box, enter the Linked Server
Name, select Other data source and choose the Microsoft Jet 4.0 OLE DB Provider

4. Enter the full path and name of your Access database file (such as C:\Program Files
\Microsoft Office\2002\Office10\Samples\Northwind.mdb). Leave the other two fields
Provider String and Product name blank.

5. Navigate to the Security pan, select No security context will be used.

Dennis, if the problem still exists, please specify the detailed error message as I cannot
touch it in the original post and try using Query Analyzer to connect the Access database.
Thanks for your patience and cooperation!

I'm here to be of assistance if you need further help.

Best Regards,

Billy Yao
Microsoft Online Support