469,934 Members | 2,756 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,934 developers. It's quick & easy.

Linked table - ODBC connection ... failed

An error "ODBC connection to [name of SQL Server] failed"
is raised when I am trying to get data from a table in
Microsoft Access (97, or 2000) which is actually a linked
table to another table in SQL Server 7 . The exception is
raised from a Web .Net app while exactly the same code
works fine in Windows .Net app. That makes me thinking the
issue somehow is related to security context. Anyway I do
not know how to solve the problem, and any help is
appreciated.

Windows 2000, XP; IIS 5.0; .Net Framework 1.1.; Visual
Studio .Net 7.1.3088; SQL Server 7; OleDb connection to
Jet 4.0;

Below there are the steps to reproduce the problem.

Step 1.
Run AdministrativeTools->DataSources. Create a data source
(DSN) to the database "pubs" of SQL Server.
Let's call it "LinkedPubs".

Step 2.
Create a database in MSAccess 97. Let's call
it "db1.mdb". Go to File->Get External Data->Linked
Tables.Select "ODBC databases". In the list of data
sources select newly created DSN "LinkedPubs". In the next
list of "Linked Tables" select table "dbo.authors". Close
MSAccess.

Step 3.
Start Visual Studio .Net. Create a new ASP.Net
application. Drag OleDBAdapter to the page, and start
configuring it. Select database "db1.mdb" as the data
connection. Create a query, say "SELECT au_fname FROM
dbo_authors". Finish the wizard.
Put a button on the page and place the following code to
handle button click:

==================
Private Sub btnGetAuthors_Click(ByVal sender As Object,
ByVal e As System.EventArgs) Handles btnGetAuthors.Click

Dim ds As New DataSet
Dim ID As String
Dim err As String

Try
Me.OleDbConnection1.Open()
Me.OleDbDataAdapter1.Fill(ds)
ID = ds.Tables("dbo_authors").Rows(0).Item
(0).ToString()
btnGetAuthors.Text = ID
Catch ex As System.Data.OleDb.OleDbException
err = ex.Message
btnGetAuthors.Text = err
End Try

Me.OleDbConnection1.Close()
End Sub
=======

That's it.

When I run the application, click the button, the line
Me.OleDbDataAdapter1.Fill(ds) raises an exception "ODBC
connection to [name of SQL Server] failed".

I created a Windows .Net application using the same DSN,
the same adapter, and it works fine. For Web .Net app
described above I can use "Preview Data" of the adapter,
and I can see the list of authors.
Nov 17 '05 #1
0 3479

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by David Gray | last post: by
reply views Thread by crypto_solid via AccessMonster.com | last post: by
reply views Thread by Igor | last post: by
reply views Thread by EJO | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.