Changing a Table to a Linked Table?

[mark909]

Ive set up a database containing several tables.

Data has been uploaded into these using .txt files and appended to the
relevent table.

We have a GIS system in place and I was wanting to streamline the process of
importing data from the GIS system into Access.

Ive set up the GIS system so that all the data inputted into it is
automatically updated in individual access files as seperate tables.

Im hoping to alter the format of the current tables within my Database to
become linked tables to the seperate files i have created.

All the tables have the same format and structure.

Is it possible to do this within having to build my database from scratch??

Thanks for any help!

 

[Arvin Meyer MVP]

First always make a copy before doing anything like this.

Next, on the copy, delete the local tables, and link the GIS system tables.

Last, if necessary, rename the linked GIS tables to exactly match the
original table names. This only changes the names as they appear to Access,
not as they are in the GIS system.

Archive your original files.

 

[Joan Wild]

You would just deleted the tables, and then use File, Get External Data,
Link and link to the tables you want to.

(backup first, of course)

Having explained that though, I'm not sure you really need separate
access files for the data. Couldn't all the GIS information go into
separate tables in ONE mdb file? Then you'd just link to that one file.

(if I understand you correctly)

Joan Wild
MS Access MVP

 

[mark909]

So when I delete and add the linked tables all the queries, reports, etc
within my database will work okay as long as the tables names and structure
are the same?

I tried something similar a while ago and lost the ability to query the data
within the new tables

The access files are saved as OGC files created by the GIS.

Each file represents a different layer within the GIS and so couldnt all be
saved as one mbd table.

 

[Jeff Boyce]

"All tables have the same format and structure."?

It is rare (to the point of near invisibility) to have a well-normalized
relational database that has "matching tables" (same format/structure).
?"So what?", you ask? So the relationally-oriented features/functions
offered by Access work best when fed properly structured data.

If you have more than one table with the "same format and structure", you
(and Access) will have to work much harder to do the things that are much
simpler to do with normalized data.

.... or maybe I don't have enough information about your specific situation
yet...

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP