Error 7357 - Access As Linked Server...

[Elad Altstein]

Hi,

I have an .mdb file on a network drive in my SQL Server 7 box. I set it
up as a Linked Server using "OLE DB Provider For Jet". Problem is this: I
can see the tables in the access file (through entrerprise manager) but
when I run a query against them thus: "SELECT * From H...Table"

I get:

Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'Table'. The OLE DB provider
'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns.

I'm guessing this is a permissions problem since there is a workgroup
file (.mdw) that goes along with this .mdb file.

QUESTION IS THIS! :

I do setup a workgroup file for an Access Database setup as a Linked
Server using "OLE DB Provider For Jet"

Thanks,

Elad Altstein.

[mary chipman]

as long as the mdw file for the jet database is the default, you
should be able to provide a user id and password for a valid account.
if it's not, and you are working with multiple workgroup files, then
the answer is, you can't do it. SQL Server doesn't support multiple
jet linked servers with different workgroup files.

[Elad Altstein]

Hi,

Thanks for the quick reply....

There is another way I found, I use the "OLE DB Provider for ODBC
Drivers" and specify an ODBC Data Source thats running a Microsoft Access

Driver to my .mdb file, there I can specify the system Database. BUT,
again I can see the tables in enterprise manager but when I try a SQL
query against them (SELECT * From H...Table), I get this:

Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'MSDASQL'.

And when I tried :
"SELECT * From HSTUDIO.DB.Admin.Table"

I got:

Server: Msg 7312, Level 16, State 1, Line 1
Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A
four-part name was supplied, but the provider does not expose the
necessary interfaces to use a catalog and/or schema.

Any insight on this issue? is it perhaps possible to workaround SQL
Server's disability this way?

Another thing: If i had the same user names in my default workgroup
file as in my custom workgroup file would the users "impersonate" ?

Thanks,
Elad Altstein.

In article <f1ru3tsc7qa8t1rin...@4ax.com>,
mc...@nospam.please says...

[Elad Altstein]

Mary,

reading through your reply again, another thing occured to me.

On my SQL Server machine there is no other Jet engine using the default
or any other workgroup files. Cant I setup the default workgroup file on
SQL Server machine to point to the correct one, keeping in mind I dont
need to link any other Jet enginges to this machine ?

Thanks,
Elad Altstein.

In article <f1ru3tsc7qa8t1rin...@4ax.com>,
mc...@nospam.please says...

[mary chipman]

as far as i know, the only way to get linked servers to work properly
with access is to de-secure the Access mdb and use either the default
system.mdw or a single, secured mdw.

On Tue, 19 Dec 2000 12:30:18 -0800, Elad Altstein

[Xavier John]

This article demonstrates how to perform a Microsoft SQL Server distributed
query to retrieve data from secured Microsoft Access databases.

MORE INFORMATION
================

Microsoft SQL Server version 7.0 provides the ability to perform queries
against
different databases using OLE DB providers. This can be accomplished using:

- OpenQuery or OpenRowset Transact-SQL functions.

-or-

- A query with four-part names including a linked server name.

To set up a linked server to access a secured Access database:

1. Configure the registry (using the Registry Editor) to use the correct
Workgroup Information file used by Access (.mdw). Use the Registry
Editor to
add the full pathname of the Workgroup Information file used by Access to
this registry entry:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\SystemDB

and then set the value to the path and name of the file, such as,
C:\...\MySystem.mdw.

1. Go to Start/Run and type Regedit then press Ok to open the registry
editor.

2. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\

3. Right click and select New/String Value.

4. Enter SystemDb and press enter.

5. Double click default on SystemDb in the left window pane.

6. Enter the full path to your MDW file in the Value Data section.

7. Close the registry editor.

2. Execute sp_addlinkedserver to create the linked server, specifying
Microsoft.Jet.OLEDB.4.0 as provider_name, and the full pathname of the
Access
.mdb database file as data_source. The data_source is evaluated on the
server, not the client, and the path must be valid on the server.

3. Execute sp_addlinkedsrvlogin to create login mappings from local logins
to
Access logins.

Steps to Reproduce Behavior
---------------------------

1. Modify the registry key shown above to the location of your .mdw file.

2. Start Visual Basic 6.0 and select Standard EXE project. Form1 is created
by
default.

3. On the Project menu, select References, and set a reference to Microsoft
ActiveX Data Objects 2.1 Library or later.

4. Place two Command buttons and a DataGrid control on Form1 (Command1,
Command2
and DataGrid1).

5. Paste the following code into the declaration section of Form1:

Dim adorst As New ADODB.Recordset
Dim adoconn As New ADODB.Connection

Private Sub Command1_Click()
Dim strConn As String

adoconn.Open "Provider=SQLOLEDB;Data Source=MyServer;Initial
Catalog=master;User Id=sa;Password=;"
adoconn.Execute "EXEC sp_addlinkedserver 'SecuredJetLS', 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0', 'c:\.....\MyDatabase.mdb'"
adoconn.Execute "EXEC sp_addlinkedsrvlogin 'SecuredJetLS', FALSE,
NULL, 'UserName', 'Password'"
adoconn.Close
MsgBox "Successful Setup"

End Sub

Private Sub Command2_Click()
Dim SQL As String

' Using OpenQuery syntax
SQL = " Select a.* from OPENQUERY(SecuredJetLS, 'Select * from
MyTable') a"

' Using OpenRowset syntax
' SQL = "SELECT * From
OpenRowset('Microsoft.Jet.OLEDB.4.0','c:\....\MyDatabase.mdb';
'UserName';'Password', MyTable)"

' Using four-part name syntax
' SQL = "Select * from SecuredJetLS...MyTable"

adoconn.CursorLocation = adUseClient
adoconn.Open "Provider=SQLOLEDB;Data Source=MyServer;Initial
Catalog=master;User Id=sa;Password=;"
adorst.Open SQL, adoconn, adOpenStatic, adLockReadOnly

Set DataGrid1.DataSource = adorst

End Sub

Private Sub Form_Load()
Command1.Caption = "Setup Linked Server"
Command2.Caption = "Query Linked Server"
End Sub

Private Sub Form_Unload(Cancel As Integer)
adorst.Close
Set adorst = Nothing
adoconn.Close
Set adoconn = Nothing
End Sub

6. Run the project.

7. Click the Setup Linked Server button. If you modify the connection
string,
sp_addlinkedserver and sp_addlinkedsrvlogin with the correct parameters,
the
linked server is created successfully.

8. Click the Query Linked Server button. If you modify the connection
string and
the query text to the correct parameters, the DataGrid control is
populated
with your data.

REFERENCES
==========

For more details on setting up and using Distributed Queries, see the
sp_addlinkedserver, sp_addlinkedsrvlogin, OpenQuery, OpenRowset, and related
topics in the SQL Server 7.0 Books Online. The Books Online topic "OLE DB
Provider for Jet" describes how to access a secured Access database.

Regards,
Xavier

--------------------------------------
Remove nospam_ to replay