Post reply

SQL tables linked to Access

  • August 3, 2005 at 7:00 am

    #67459

    I usually connect to a SQL Server 2000 db from ArcView 3.3 (GIS).  This is as simple as making a System DSN and supplying the necessary username and password.  I then refer to the view that I need, using an Avenue (ArcView) script.

    But now I'm helping someone who doesn't have SQL Server 2000, but uses MSDE instead.  Because this person has no client connectivity tools (e.g. Enterprise Manager), I cannot make a view of their data for them.  So I thought I would simply make an Access database, and link to the MSDE db tables by using a System DSN to the default database on the server that is running MSDE.  This I did and it seemed fine.  But I still needed a view of some sort to refer to in my Avenue script.  So I made a query in the Access database.  Now the Access database is the place I need to refer to in my Avenue script (Microsoft Access driver).  So I made a System DSN to the Access database (which has no username or password).  But I'm getting a SQL Syntax error when I try to connect, and I strongly suspect that it has to do with the fact that I'm successfully connecting to the Access database from ArcView, but then failing to connect from there to the MSDE server because I haven't supplied the username and password.

    My question is: How do I pass the username and password for the DSN to the server to Access, not to use when the Access database is opened, but only for when the query (which uses linked tables from the server) is opened?

     

    Thanks

    Willem

  • August 3, 2005 at 9:47 pm

    #579427

    If at all possible, I would recommend avoiding this situation but re-creating the view(s) that you need within SQL server.  You can link to a view from Access the same way you link to a table.  This would avoid the complexity of using Access as a "middle-man".  You can connect your Avenue script directly to the MSDE database and still allow the client to connect to the database through Access. 

  • August 4, 2005 at 3:11 am

    #579471

    what about setting the username and password on the dsn.

    go to the dsn setup and click on the advanced button, put in the usrname & password.

    Everything you can imagine is real.

  • August 4, 2005 at 3:26 am

    #579477

    Thanks, I've tried that.  I think the reason that that doesn't work is that it's not the Access file that needs a username and password (which is what it would mean if you type these in the advanced tab of the Access DSN), but the SQL Server database that the Access database refers to (for the linked tables).

  • August 4, 2005 at 3:56 am

    #579481

    When you linked the tables in Access, did you select the "Save password" option on the screen where you pick the objects to link ?

  • August 4, 2005 at 4:36 am

    #579489

    No I didn't.  Thanks very much Joseph - you've solved my problem. It's working now.

    Thanks very much

    Willem

  • August 10, 2005 at 2:59 am

    #580943

    No problem - glad I could help.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply