Linked Table - I Thought I Knew

[JediYodaNT]

So, over the past week, I have been thrown head first into the world of Access. We have a database at work that is used boy 20+ associates. This database was build and maintained by an associate that has since left. We ahve a handful of new function types that need to be added, and a couple tweaks that the database could use, but my main project consists of developing a more useful report. I've spent the past few days going through the MS Online Training for Access, and I though I understood "Linked Tables", but now I'm not so sure. A linked table is one that has a foreign key from an associated table, correct? If this is correct, any table that Access states is a Linked Table should show the links in the Relationship view, right? I have a table that was designed with one main flaw...the InputDate field was set to data type - Text, when it should have been data type - Date/Time. Because of this, any report that I request within a certain date range gives me records for multiple years. I need to figure out what this table is linked to and determine if I can safely unlink it and edit the data type. Any advice would be greatly appreciated.

 

[DCrake]

A linked table is a table that does not reside in the current mdb but in a seperate mdb. You can tell this becuse it will have a small arrow next to the icon in the tables list.

If you hover over a linked table it should show you the source mdb path and name. You need to open this mdb and design the table in question and change the datatype from Text to Date/Time. Be careful as there may be certain records that will not permit the changes to take place due the contents of the record being unable to be converted to date/time format.

David

 

[namliam]

No... A linked table is a table that is not actually stored in this Database, but in another one...

Probably in a Front end (coding and forms) and backend (data) setup...

Open the table in design view, right click the title bar and select properties to find where the storing database is.

FYI
The relationship screen is nice and all, but there is absolutely NO NEED to define any relationship in there....

P.S.
Welcome to the forum oh old wize Jedi

 

[gemma-the-husky]

just to clarify

a dbs is normally split so that the programs are in one place, and the data in another (on a network). In this way, each user can have a copy of the code, all sharing the same data, so the database becomes true multi-user (similar to any other file based system, actually). The linked shared database is termed the BACKEND

tables IN the database are not shown with any symbol. Linked tables are shown with an appropriate symbol, so ACCESS tables have a black arrow, SQL tables have a world symbol, and FoxPro tables have a little fox. To all intents and purposes any linked table can be used as if it was in the database (there are a few commands that wont work on linked tables) As long as the backend is on a LAN, there will be no speed issues, but it will not work quickly over a WAN. Because the LINKED data tables are ACTUALLY located in the backend, you need to open THE BACKEND directly to modify the tables - but any changes are then automatically included in the frontend. This also enables you to change the functionality in the fornt end, and reissue it, without affecting the data held in the backend (although often you WILL find you need to add extra fields to the backend, or modify it in some way - as you are currently finding)

Note that if the date really is held as text (which seems unusual) then

a) it may not be able to be changed directly to date (since the datetime datatype is actually a number) and
b) if your front end is coded to use text dates, then changing the field type is likely to cause many more issues in the front end.


incidentally, see ms kb article 889588 for information on optimising the connection to a linked backend

http://support.microsoft.com/kb/889588


-------------
now when you design a database you establish relationships between tables [which can be local tables or linked tables] - ie you have a customers table, and an orders table - and the customers table is linked to the orders table in some way - in this example the orders table is LINKED to the customers table by the the customer code, which would be included in the orders table, and this is the foreign key. If this LINK is established in the relationship diagram, then it will be automatically included in a query using the customers table AND the orders table - otherwise you add the link manually in the query diagram.

Different sort of LINK, as you can see.

 

[JediYodaNT]

Wow. Thank you all for your explainations. It's great knowing that there are experienced folks out there that know what they are doing and are willing to help those of us that don't.