8

I have an Access 2010 database A.mdb with a list of tables, one of which is a linked table, linked from another Access database B.mdb on the same server. These databases are on a development machine xxx.xxx.xxx.xxx, which is mapped on my computer as R://, and they are afterwards published online on a yyy.yyy.yyy.yyy server.

If I want to work on the database locally, I need to change the link to the table. But if I change it via filesystem (using the "Linked Table Manager"), the link becomes R://.... and when I look at the ASP page that requests those data, it is broken because the path is wrong. Also, if I change the link locally, it won't work on the online server.

Is there a way to change the link "programmatically"? That is, without using the Linked Table Manager?

I searched for an answer, but I am not that expert, I just understood that I have to write a "Module"? "Macro"?

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Laura Silvani
  • 747
  • 3
  • 9
  • 18

3 Answers3

14

Table links can be UNC paths. For example, say I have a linked table pointing to a database on \\192.168.1.2\Public\ which is mapped to drive P:. If I launch the VBA editor (Alt+F11), open the Immediate Window (Ctrl+G) and type...

?CurrentDB.TableDefs("remoteTable").Connect

...it will return...

;DATABASE=P:\B.accdb

...because I pointed to drive P: when I created the link.

Now if I create and run the VBA function...

Function linkToUnc()
Dim cdb As DAO.Database
Set cdb = CurrentDb
cdb.TableDefs("remoteTable").Connect = ";DATABASE=\\192.168.1.2\Public\B.accdb"
cdb.TableDefs("remoteTable").RefreshLink
Set cdb = Nothing
End Function

...the link is now a UNC path.

By the way, you can create UNC links in the Linked Table Manager if you browse to "Network", "machine name", "share name", but that will give you the machine name (in my case \\PICO\Public\B.accdb).

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Thank you so much!! Just one question (newbie here): how do I create the VBA function? Macro? Module? Macro linked to a Module? Boh! Regarding the "Browse to Network, Machine Name, ..." actually I can't do it, because for some reason that I give up to understand I cannot see the machine from the Newtwork (I am in a big and huge organization, so accesses are restricted and weird!) – Laura Silvani Mar 18 '13 at 10:59
  • 1
    Create a new Module, then just paste in the code and edit to suit. You can run the function by clicking on the first line and hitting `[F5]` (or choose "Run" from the menu bar), and you can also create a Macro to run it (via the `RunCode` action). – Gord Thompson Mar 18 '13 at 11:09
  • Under `Tools > References...` make sure you have "Microsoft Office 14.0 Access database engine Object Library" selected. – Gord Thompson Mar 18 '13 at 11:51
  • Thank you very very much, much appreciated :) Just one last question: is it possible to have links like "C:\MyFolder\mydatabase.mdb"? Now the macro says it doesn't find the MDB file..Just wondering if it is possible. Thanks. – Laura Silvani Mar 18 '13 at 13:38
  • Mmm,not valid...it says "No database found"...anyway, solved it the other way and I am happy like this. Thank you so much for your support! – Laura Silvani Mar 18 '13 at 14:20
  • Super handy! I'd like to note that the connection string can be modified in the current fashion to link to .xls files: `cdb.TableDefs("remotefile").Connect = "Excel 8.0;DATABASE=\\192.168.1.2\Public\File.xls"` – Shrout1 Aug 14 '13 at 18:11
0

you can right click a linked table and select Linked table Manager. Select the linked table you want to work on and tic the checkbox bellow the window which says Always prompt for new location and click the OK button.

You will be asked for the new location. Browse and select the file and click OK.

Tera
  • 113
  • 3
  • 12
  • Hi Tera, thanks for the answer, but this is exactly what I don't want to do. I edited my question because maybe it's not so clear. – Laura Silvani Mar 18 '13 at 09:57
0

I like to set up a File DSN on a shared folder accessible with read-access to our entire network and then use a linked table manager to the UNC path. To do this, I use the linked table manager and set up a file DSN on my desktop. I then copy and move the DSN file to the shared UNC directory. Then, I go into Access and delete the linked Table and recreate the linked table. To do this, after deleting the linked table, I do the following:

  1. I click on ODBC Database-->Link to the data source by creating a linked table-->OK.
  2. Click the File Data Source Tab in the Select Data Source Window. At the bottom of the dialogue box, you will see "DSN Name:" followed by a text box and a New Button. You will enter the entire UNC path AND filename into that text box (so this is pointing to the file you copied out to the UNC path). For example, I enter, \ATD-SERVER1\AccessShare\ContactsApp.accdb.dsn. Then click OK.

Assuming you have access permissions to server you are linking to, the Link Tables dialogue box will be displayed and you can then select your linked tables from there! Microsoft couldn't make this any less intuitive if they tried.

StatsStudent
  • 1,384
  • 2
  • 10
  • 28