By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,403 Members | 855 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,403 IT Pros & Developers. It's quick & easy.

Linked table - ODBC connection ... failed

P: n/a
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
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.