Post reply

MSAccess db to link to different database based on user

  • February 12, 2007 at 5:15 pm

    #369455

    Hello -

    I have a Microsoft Access 2000 database which links four tables from a SQL Server database using an ODBC.  I was thinking that I'd be able to have the user set up their ODBC to point to the appropriate server and database (same database structures).  For example, based on their location, User A needs the data from database X on server Y and User B needs data from database M on server N.  But when I changed my ODBC to point to a different server and database, I got an error stating:

    Connection failed:

    SQLState: '08004'

    SQL Server Error: 4060 

    Server rejected the connection; Access to selected database has been denied

    I'm now seeing that the database name is stored in the properties of the linked table.    I tried removing the db name from the table properties, but that didn't work. 

    Is there a way that I can reset the links via a subroutine or function when the user opens the MSA db or clicks on a button on the form (they only see a single form when the db opens - nothing else)?

    FYI - I'm more of a SQL person than an Access person; sorry if this is an obviously novice question. 

    Thanks!

    Sam

    Sam

  • February 13, 2007 at 11:47 am

    #689532

    Are you using an Access mdb?  Does each person have their own Access mdb?  Do you know how to use the immediate window?

     

    Russel Loski, MCSE Business Intelligence, Data Platform

  • February 13, 2007 at 11:56 am

    #689534

    Yes, I am using an Access 2000 mdb to link four tables from a SQL Server db.  Yes, each person would have their own copy of the Access mdb.

    No, I don't know about the immediate window.

    Sam

  • February 13, 2007 at 12:28 pm

    #689541

    I think that your best bet is to use the linked table manager.  Change the DSN in the ODBC administrator tool to point to the server and database.  Then go into Access and find the linked table manager (tools menu, database utilities, linked table manager).  In the lower left corner is a check box to have the routine prompt for new location.  Click on that and chech your tables.

    Russel Loski, MCSE Business Intelligence, Data Platform

  • February 13, 2007 at 2:17 pm

    #689578

    Thanks!  I was hoping to do this via VB from behind the scenes after the user creates a specific ODBC because I want to restrict the user totally to a single form in the MDB.  I've found lots of code that will relink tables from other MDBs but the code I found that was supposedly going to relink my ODBC tables and then drop the links when the form closed, turned all my select queries into pass-through queries.    In the meantime, I did find the command that will open the Link Table Manager (LTM):

    DoCmd.RunCommand acCmdLinkedTableManager

    So, since I need to get this done for the end of the week , for now I'll add a button to the form that the user has to click to get LTM to open or add this to the open event or something and revisit doing it automatically later. 

    Thanks for your help!

    Sam

  • February 13, 2007 at 5:48 pm

    #689609

    Apologies for being late to this, and not being too specific - the right arm that is databases has been cut off for me in this present job (no Access or SQL, gahhh!).

    Anyway, there's a few things you might want to try...

    If you're accessing ODBC connections then you can save user & password in plain text in the query properties if I recall correctly. Obvious security issues there but it can make connection control simpler.

    You could also have a look on the Microsoft MVP website for Access:

    http://www.mvps.org/access/

    There's a mine of information on there, including the code to automatically prompt for new MDB link connections if they're not found. I've used this successfully to relink databases in a scenario where drive mappings were chaging. Might be useful?

    Then, in the past where I've been working with users who've not got ODBC set up on the machine they've been using, I've resorted to a registry fragment (export your ODBC settings from the registry), which then gives the various mappings in the control panel's ODBC settings.

    Not sure if any of that helps, and possibly BS given I haven't got access to Access here at the moment (boo!).

     

    Regards,

    Andrew

  • February 14, 2007 at 9:44 am

    #689742

    Thanks, Andrew!  I had been to that web site via a search result link (which didn't take me to what I wanted), but didn't search the site.  Now that I have, I believe I see the code I need.  Something new to play with!!! 

    Thanks again!

    Sam

  • February 15, 2007 at 12:55 am

    #689877

    Hi Andrew,

    How do you export your ODBC settings from the registry?

  • February 15, 2007 at 3:47 pm

    #690092

    Hi,

    Well, to put it into context, what you're doing is exporting your ODBC settings as per control panel. So what's set up in there will carry through.

    So, if you've got user level access set up in control panel, then that'll follow through, but if your PC has system level then that'll export. Of course, you can do both.

    And, to do so...

     

    Start, Run, Regedit...

    Find the key (for system ODBC connections):

    mycomputer\hkey_local_machines\software\microsoft\odbc

    Right mouse click, select export.

    Name it something suitable, put it somewhere suitable...

    And hey presto, you've got a registry fragment that'll give you ODBC settings on the machine that runs it.

    Worth noting that a lot of email systems will block a .reg file, so may find hard to distribute easily.

     

    We used this method for years, after our Network Support team rolled out a duff image of the systems to our division, and started charging extortionate fees to fix, when all it needed was a new PC image to be built. Ah well, we were heros.

  • February 15, 2007 at 3:50 pm

    #690093

    Further to this...

    Not wanting to encourage too much messing about with your registry, but if you edit the fragment using notepad or similar it's pretty clear how the settings are structured, meaning you can remove one or more if you think it inappropriate it to pass on (like local shares to admin databases etc).

    Cheers,

    Andrew

  • February 15, 2007 at 6:22 pm

    #690113

    I dont have immediate access to the snippet, but we used to use an Access module that would relink all the tables based on a provided database name - that way one set of links could be used for any of a number of databases that all had the same structure. Theres a system table that holds the link info, so its just a matter of digging in - a different approach to consider anyway!

    Andy
    SQLAndy - My Blog!
    Connect with me on LinkedIn
    Follow me on Twitter

  • February 16, 2007 at 7:02 am

    #690204

    Hi Andrew, is there a chance you might want to share the code in that Access module?

  • February 17, 2007 at 3:39 pm

    #690363

    Not mine to share as such, but it's here:

    http://www.mvps.org/access/tables/tbl0010.htm

     

    What I did with this code was to then wrap it up into a startup routine in the database which checked on startup, and if it couldn't find a table it ran the re-link. Worked like a charm!

    For those Access types amongst us, this website is an essential resource I can tell you, there's a myriad of useful nuggets in there.

     

    Cheers,

    Andrew

  • February 19, 2007 at 12:44 am

    #690400

    Thank you very much Andrew,

    I believe tblReconnectODBC in code is not a system table though.

  • February 19, 2007 at 3:34 pm

    #690554

    Yes, I believe you're right.

     

    Have fun.

Viewing 15 posts - 1 through 14 (of 14 total)

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