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?
Best Answer
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.