advantages of linked files?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a large number of files that I re-create each month. I am debating the
best way to manage and use these files. I create text files from the database
for the information I need. I can either link to these text files or pull
them into the access database. I have been importing them, but I've been
having problems. The database gets very large and I keep getting errors
regarding sort keys not found and another one I can't remember. These both
point to corrupt files.

The answer I've found to these errors has been to rebuild the database and
copy the files to it. I have 30+ files and 70+ queries. I don't want to have
to rebuild this database everytime a file gets corrupted on import. I
finally created one database with my queries and one with tables and then
linked the tables in. I didn't use the split database as I have never used
that and forget that function is there.

I'm open to any and all suggestions. Should I link to the flat files?
Rebuild just my tables database? Put them all together and split the
database? Pros/cons?

Thanks.
 
First, do split the database. That will put all your tables in one mdb and
everything else in the other.
I would try linking to the text files to import them. You can set up an
Import Specification to tell Access how the file should be formatted, the
field names, data typed, etc.

To set up the specification, Do a link manually with Files, Get External
Data, Link. Select Text files. Then navigate to the file you want to link
to. After you click Link, you will get the Link Text Wizard. Click on the
Advanced command button. It will walk you through setting up the
specifications. When you have completed the setup, click on Save As. Give
it a meaningful name.
Use the name you gave the specification in the TransferText method to link
to the file using the Specification Name. It is the second argument of the
method. See VBA Help for more details on TransferText.
 
What I have right now is one db with just tables and one db with queries,
reports, etc. These dbs have different names. I did this before I
remembered a database can be split. Is this set-up basically the same as
taking one db and splitting it? Would there be any reason to put everything
back into one db and splitting it?
 
Your database is split. It does not have to be done with the Database
Splitter tool. That is only for convenience. If you have only tables in one
mdb and all other objects in the other, and the other mdb links to the mdb
with the tables to get it's data, that is a split database. This is the way
it should be. The other issue is that the "front end" database (the one with
the queries, forms, reports, etc) should be on each user's computer and
linked to the "back end" database (the one with the table). The back end
should be on a shared drive where all users have rights.
 
Back
Top