Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Houma, Louisiana, USA
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts
    The last time I designed a database was in 1996 using Access 2.0, so please be gentle. A LOT has changed since then.

    I need to build an Access database that pulls in some information from a linked file on a remote system. I can build it using Access 2007 or 2003 and my first attempt is using the 2003 version. I'm running into a few issues.

    Issue #1- I notice that the size of my Access database is relatively small, UNTIL I link the to the linked file. After that, the database size becomes enormous because it's probably sucking in the entire data file. I really only need some description information and some numbers. I don't want the user to be able to edit the linked file in any way, shape or form. Is there a way to limit the amount of data that comes into my Access database. I only need a miniscule amount of data from that file.

    Issue #2 - I set up some queries that pull data from my access database as well as a couple of fields from the linked database. When I open the query it doesn't allow me to add records. I'm sure this has something to do with me not allowing the linked database to be modified. Is there a way I can make a query/form that will pull in the descriptions from that linked file while allowing the user to add records?

    Issue #3 - The remote site may not have connectivity all the time. What will happen to the data/database when the remote user is unable to get a connection to the linked data?

    I enjoyed the challenge of Access when I did that database years ago, but this database is making me feel like an old dog who's really struggling to learn new tricks.

    Thanks in advance to anyone who can offer a few kind words and helpful tips.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Yes - things have changed a fair bit since Access 2.0 - although Access 2003 has pretty much the same look and feel. I've been working with Access since 1992 - it's been quite a ride.

    Issue#1 - what kind of linked file are you connecting to? If it is text, Excel or some other facility then your database will bloat. On the other hand, are you sure you are linking, and not importing the file?

    Issue #2 - when you join on a table that doesn't have a primary key, the query will become non-updateable. Access 2.0 was more forgiving (or reckless depending on your perspective) in that regard. But if you are allowing record additions, that would suggest you are adding a matching record in the linked table. That you probably cannot do. Also note that if the data is a text file, Excel file, etc, then by definition it cannot have a primary key, although you may be able to trick Access into thinking it has one.

    Issue #3 - if the linked data is not available you will get error messages if you try to open the linked table, or run a query that joins to the linked data. If that is likely to happen very often, you may want to pull in a local copy of the data.

    Hope this gives you some ideas to pursue - and if you are less than comfortable with the 2007 User Interface, I would suggest developing in 2003 - I prefer to - and then move it 2007 if you need to.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Houma, Louisiana, USA
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks so much for your help and advice, Wendell.

    Quote Originally Posted by WendellB View Post
    Yes - things have changed a fair bit since Access 2.0 - although Access 2003 has pretty much the same look and feel. I've been working with Access since 1992 - it's been quite a ride.

    Issue#1 - what kind of linked file are you connecting to? If it is text, Excel or some other facility then your database will bloat. On the other hand, are you sure you are linking, and not importing the file?
    I'm linking to a DB2 file on an iSeries. How can I tell whether it's linking or importing? I think it's linking.

    Issue #2 - when you join on a table that doesn't have a primary key, the query will become non-updateable. Access 2.0 was more forgiving (or reckless depending on your perspective) in that regard. But if you are allowing record additions, that would suggest you are adding a matching record in the linked table. That you probably cannot do. Also note that if the data is a text file, Excel file, etc, then by definition it cannot have a primary key, although you may be able to trick Access into thinking it has one.
    Does the primary key have to be a single field or can it be a combination of fields? One query I made uses a table from the Access database that has a primary key and two linked tables which appear to use a combination of fields for the primary key.

    Issue #3 - if the linked data is not available you will get error messages if you try to open the linked table, or run a query that joins to the linked data. If that is likely to happen very often, you may want to pull in a local copy of the data.
    I think that pulling a local copy of the fields I'm trying is also a good idea. Is it possible to bring in a very limited amount of data from the linked/imported file?

    Hope this gives you some ideas to pursue - and if you are less than comfortable with the 2007 User Interface, I would suggest developing in 2003 - I prefer to - and then move it 2007 if you need to.
    I will probably do as you suggest and develop in 2003.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Sorry about the delay in responding - had to be out of the office most of yesterday. I've not had a great deal of experience with iSeries data, and what I had was a rather old system that was actually upgraded from a System36 - but what I found was that with linking the data was never updateable. I think that was a result of the ODBC driver we were trying to use. We ended up importing all of the tables so we could actually work with the data. And it was possible that way to import selected fields if you use make table queries to create just the key fields. On your question about primary keys, you can have multi-field primary keys in Access, and queries using that table will be updateable if there aren't too many tables involved and outer joins aren't being used. Hope this helps.
    Wendell

  5. #5
    2 Star Lounger
    Join Date
    Dec 2001
    Location
    Houma, Louisiana, USA
    Posts
    100
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks for the reply Wendell.

    I'm not very impressed with linked tables in access. For whatever reason the linked tables really bloat the database. I had 3 tables linked to the database and noticed that the size of my database is over 50MB. Yesterday I removed all the linked tables. The database was still 50MB. I tried runing Compact and Repair database and the size of the database is still over 25MB.

    This database now only has 5 tables in it. There are NO linked tables any more. Between the 5 of tables there's a grand total of 23 records. TWENTY THREE. That's all. I can't fathom how it's possible that the size of the database is so enormous.

    I'm starting to think that this project will require something more than Access. It's fine to store the offline data in this database, but I don't think it will serve us well to try and pull in data from a linked file.

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    The bloat of Access has more to do with the ODBC driver and how it handles linking tables than it does with Access. I routinely work with Access databases linked to tables in SQL Server where the size of the tables is several GBytes of data, and the Access front-end is 200K to 300K. Consider this approach: Import the iSeries tables into an Access database, which you will subsequently link to. Then create a new empty Access database as the front-end, and import the 5 tables (plus any queries, forms, reports, etc.) into that new database. Then link to the Access database you created that contains the iSeries data. (Splitting a database in that fashion is nearly always recommended.) You should find that your front-end database is much smaller in that arrangement. We have complex front-end databases that are typically in the 20MB range, but they contain several hundred forms, reports and queries. If you have your database in 2002/3 format, I have seen situations where the Compact and Repair function doesn't reclaim all the space it should, especially when you are doing development work on forms and reports. For some reason the 2000 format seems to work better in that regard. But importing the objects into a new database seems to solve the issue.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •