Linked MSDE tables in Access

[Guest]

Ok, I have an mdb with a couple of linked tables from an MSDE db. When I
linked em I saved the password.

I have a query in Access that refers to one of the tables in MSDE. It works
great. However, when I try to run that same query from and Aspx page, I get
an error telling me that ODBC can't connect to the MSDE db.

I am using OleDbCommands and an OledbDataadapter in the aspx page.

I have tried setting up a role with myself, IIS_WPG and ASPNET having perms,
but it doesn't seem to help.
Any ideas?

 

[Norman Yuan]

Are you saying that your ASP.NET pages connect to an Access *mdb, which, in
turn, links to MSDE through ODBC? It is rather odd settings. Why not connect
ASP.NET app directly to MSDE?

 

[Guest]

Hi Yes, I have an aspx page that connects to and Access mdb which has linked
MSDE tables. The aspx page runs an access query that references the linked
MSDE table. I realize this is odd, but I am trying to transition off of
access, but I can't do it all at once.
Thanks ... Ed

 

[Norman Yuan]

IMO, it is not whether you can do it all at once or not, it is just not
right. the reason to use Access to link to a backend SQL Server is to use
Access as front end app (UI). Now you are developing another frontend (UI),
that is, aspx pages. Why put one front end on top of another? If your Access
front end can access the SQL Server/MSDE, I do not see why there is
difficulty to have the ASP.NET app directly talk to SQL Server. I wouldn't
waste time doing an ASP.NET app on top of an Access front end, which in turn
links to DB Server.

 

[Paul Clement]

¤ Ok, I have an mdb with a couple of linked tables from an MSDE db. When I
¤ linked em I saved the password.
¤
¤ I have a query in Access that refers to one of the tables in MSDE. It works
¤ great. However, when I try to run that same query from and Aspx page, I get
¤ an error telling me that ODBC can't connect to the MSDE db.
¤
¤ I am using OleDbCommands and an OledbDataadapter in the aspx page.
¤
¤ I have tried setting up a role with myself, IIS_WPG and ASPNET having perms,
¤ but it doesn't seem to help.
¤ Any ideas?

I haven't seen this before but you can try a couple of alternatives:

1) You can reference SQL server from an Access QueryDef without creating a linked table. The syntax
would look something like the following:

SELECT * FROM Customers In "" "ODBC;dsn=LocalSQLServer;database=Northwind;uid=sa;pwd=";

2) You could also try entering the user ID and password in the DSN.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Guest]

I knew there had to be some magic dust I need to sprinkle on this!
Thanks a million.
Later ... Ed

 

[Guest]

Oh I hear ya Norm, but I have almost 2000 queries that need to be converted
to MSDE. Along with that I have approximately 300 crystal reports that would
have to be modified. So, it'll take some time to do. And, with my app, there
is very little concurrency (it's an accounting app). In fact, the only place
I have concurrency in when users are entering their timesheets on Friday
afternoons. Which is why I am doing them first. Eventually, I'll have
everything converted over, but it's not something I can just "bite off."
Thanks for the input.