Linking between databases (2003)

Topic

New Reply

I would like to create another database from a database I currently use and link to tables from that database. Where in proximity to the table I want to link tables to does the new database have to be? Same drive, network etc… I have been requested to create a scaled down version of a database I currently maintain and this will be put out on a lan drive, Cognos Server . The main database will be on a network drive, different server. I thought if I linked the tables in the two databases, I wouldn’t have to import the data twice. I really don’t understand network stuff….

Reply | Quote

Replies

The user should be able to “see” the folder in which the database with the tables is stored, and have full permissions (read/write/create/delete) in that folder. That is sufficient.

Reply | Quote

Does linking the tables make the database having the linked tables smaller?

Reply | Quote

Yes, a linked table takes up very little space, since the data (i.e. the records) are not stored in the linked table, only information about the design of the table and about the location of the “real” table.

Reply | Quote

The main database will be overwritten monthly. Will this break the links? Sorry for the dumb questions but I don’t want to set everything up only to miss something that won’t make this possible.

Reply | Quote

Do you mean the database with the “real” tables? As long as the name of the database remains the same and the names of the tables as well, you’ll be OK. If there is a change in either, you’ll have to relink the tables.

Reply | Quote

Thanks Hans.

Reply | Quote

I have a similar question: is there anything ODBC-ish we can set up between two access tables? Scenario:
– one database on a web server for which I don’t want to open full permissions
– “linked” database on a shared network drive where folks can create their own forms and additional tables based on the data dumped into the first database

Fingers crossed,
Angela

Reply | Quote

The Jet Engine doesn’t support linking to a database using HTTP (internet).

If the data are relatively static, you could place an Access backend on the shared network drive, and import data periodically, for example every night.
If the data are more dynamic, consider storing them in a SQL Server or MySQL database – you should be able to link to those over HTTP from the Access frontend.

Reply | Quote

Darn! The fields will be changing frequently enough that I’m thinking setting it up in SQL will be too much of a pain; will perhaps try to think of other options. Greatly appreciate the help, though. Does anyone know of an online “forms engine” that would let you create forms on the fly and manage the data behind the scenes in the style of survey monkey?

Reply | Quote