0

I'm new to Access 2007, and would like to know what this "Link table manager" does which can be found under database tools?

I'm migrating Access 2003 to Access 2007 and found out last time that if I click on the "Link Table Manager" and select all tables to be updated & then click on "machine data source" and select the database which I'm working with under "data source name" and click ok, then I'm getting a message that all tables are link.

After which the connection of my database is very very fast. But if I don't do this, when I opened any forms in the database it takes a minute or two before it open. So I'm not sure what does this "link table manager" do and as well as the database I created under "data source name". Does it really solve the connection problem? confused

Additional info:

@David: I have a database from access 2003, everything works fine from here, especially if you open the forms, queries, tables, they opened so fast. Now we would like to migrate this ms access 2003 to ms access 2007.

I'm not sure if it's also something to do with working on my local machine. After I converted it to ms access 2007, the tables, the queries, and the forms, opened very very slow. It took like 2-3 minutes until a form can be opened.

Then, I went to the "database tools" and clicked on the "link table manager" and clicked on "select all" and "always prompt for new location" and then a "select data source window" pops-up where I can go to the "machine data source" and select "new" to create the database. But if I clicked on "new" I'm getting an "ODBC System Warning" telling me that I don't have any privileged to logged on so "DSN couldn't not be created". I'm attaching a link to see the screenshots I made, please let me know if I need to add more further details.

After converting doing this: when I opened tables, forms, queries,.. they opened very fast. What does this "linked table manager" and "creating new data source" do to improve the connection of the newly converted database?

enter image description here

Improve this question
3
  • You don't want to create a new data source. You want to connect using an existing one.
    – David W. Fenton
    Dec 1, 2009 at 21:13
  • @David W. Fenton: please see my comment below. Thanks
    – tintincutes
    Dec 2, 2009 at 19:26
  • It's an awful feature that will make you not only have to deal with Spaghetti code, but also Spaghetti Databases!
    – leeand00
    Apr 7, 2015 at 20:57

1 Answer 1

Reset to default
1

The linked table manager allows you to update the connect strings of your linked tables. If your DSN has not changed, there should be no need to do it more than once. You'd have to provide more detail for us to try to figure out what's causing that.

EDIT:

When you uprade to A2007, you're inheriting the metadata from the old A2003 linked tables. "Metadata" the data stored in the table links that describes the indexes, PK, recordcount, probably index cardinality and other things, all of which are things that Access uses to decide how to process data retrieval operations. Access could retrieve it each time from the server, but instead, it caches it in the table links to save time.

The problem is that older metadata can be out of date, or subtly different in ways that cause performance problems. In your case, the metadata was likely causing some kind of bottleneck by being out-of-date for some reason after the upgrade. Running the linked table rewrites the connect string and the metadata with current data, which means you no longer have the bottleneck caused by whatever old metadata was inaccurate.

It's still not clear to me whether you really did create a new DSN or if you used an existing one -- you should have used the existing one that was there for your A2003 version.

Improve this answer
6
  • To add to David's reply - The linked tables are pointing to, usually one, database file. In your case to some kind of database server, quite possibly SQL Server. A common scenario is to have a production database and a test database. So the linked table manager allows you to switch between those. Why the performance different I don't know so I decided to add a comment to David's answer.
    – Tony Toews
    Dec 1, 2009 at 9:10
  • this is really strange...
    – tintincutes
    Dec 1, 2009 at 9:36
  • @David W. Fenton: I did create it. If you can see in the .png file, the databases under the data source name they weren't there before, i created it. And then once, its finished I selected the database i created for the connection. What's strange is: I got this message "ODBC System Warning" but I was still able to create it successfully.
    – tintincutes
    Dec 2, 2009 at 19:25
  • The point is you shouldn't have to create a new DSN. If the A2003 database worked with a DSN on the machine you're using, then the A2007 database should work with it. Now, refreshing the linked tables is a good idea after the conversion, but you shouldn't have to create a new DSN, just point them at the existing DSN.
    – David W. Fenton
    Dec 3, 2009 at 4:33
  • @David W. Fenton: I tried that not creating a new DSN, what happened is, all the forms and tables associated with this databank are all very slow. it took like 5-8 minutes before it opened. but when i created it and then select it again and clicked ok and it says that all the tables are succesfully refreshed.So not really sure what is the connection of the speed here?
    – tintincutes
    Dec 3, 2009 at 12:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.