Hi, Our network administrator recently came over and told me that my profile is larger than the rest of my department put together (about 200 users!) The main reason for this is that I write DB's and reporting tools from txt files extracted from our main Oracle DB and I import the same data into lots of different DBs for different purposes. Some of the txt files which I import into tables are pretty big (one is 1m+ records with 30 odd fields) which obviously takes up quite a bit of space.

Rather than having multiple imports, I've been thinking about creating one or two "Central" Databases which house all the txt file imports and linking these tables to the other DB's (they're mostly reporting tools and don't require additional data). I've read quite a bit on here about performance issues with linked tables. Would the general consensus be a yes or no on this one?

Thanks, Tom.

P.S. I can't connect directly to the Oracle tables because Access can't handle the size of the tables and there is no funds available to give me Business Objects or something similar :(

To start with, only put files on the server that other users have to have. I would suspect this would clean up a lot. Next, when you do have these files identified, I would have a dept share set up. Then put the files there for the dept to use...

If you are using the same data in several databases, yes link to them. This would be similar to doing your on little data warehouse. Not only do you save on size, but you would also only have to update the one table/file to get fresh data into all of your dbs.

P.S. I can't connect directly to the Oracle tables because Access can't handle the size of the tables and there is no funds available to give me Business Objects or something similar - wrong -

For linked ODBC tables, Jet does its best to send all queries to the server for processing and only return the requested records. Look for the Microsoft knowledgebase article that talks about client/server applications.

Your forms MUST be bound to a query with selection criteria. If you bind your forms to a table, the form becomes an open pipeline to the table. THAT's what your DBA is afraid of. If your application is properly structured so that your queries always contain criteria and only select the columns you want, Access actually makes a great front end for Oracle, DB2, SQL Server, etc. databases.

Thanks guys thats really helpful. I linked to the tables then just tried opening a few- expecting it to be really slow. Needless to say it was, so I immediately dismissed creating anything with them. I thought that creating forms based on queries with criteria would only make things slower because Access has to look at all the values in a field to determine whether it fits the criteria or not- unless it's indexed (is that correct?)
Anyway i'll have another bash at hooking up to the live tables rather than working off extracts all the time....

Thanks again, Tom.

Jet is a file server and Oracle is a database server. The difference is that Jet runs on the client machine so to run a query, it first needs to bring the data from the server to the local workstation (linked Access tables ONLY) whereas when the linked tables are Oracle, Jet sends the query to the server where it is processed and the server returns only the selected data.

So, if your linked tables were Jet and large, they could be slow but if the linked tables were Oracle, they wouldn't be any slower for accessing from Access than from some VB or C++ client/server application provided the Access app is properly defined.