Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Nov 2002
    Location
    Seattle, Washington, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    copying data to and from several tbls/dbs (2000)

    we have 2 databases and we will regularly need to copy data from the first to the second. the data from each record that we will need to copy is stored in several tables in the 1st (all with the same clientID) and will need to be pasted into multiple different tables (that are differently configured, although the field names match). we would like this process to be as automated as possible (such as simply supplying a clientID once, or ideally, a macro that could be run from one of the forms i've created that would just snag the clientID from the current record), but i haven't been able to figure out how to do this other than with multiple append queries that need to be edited in design view prior to running. does anyone have any ideas?

    thanks,

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: copying data to and from several tbls/dbs (2000)

    Hi Lauren,
    I presume there is a reason why you want to have two databases instead of having one with more tables, but I'm puzzled about why you need to copy the data? Without knowing more about your database design, it's pretty hard to suggest an approach. We would need to know the criteria for copying records, and a bit about the table structure. But basically you will probably need multiple append queries, as you can only append to tables. On the other hand, a parameter query might do the trick so you don't have to change the design. Can you shed a bit more light on structure and what you are trying to accomplish?
    Wendell

  3. #3
    New Lounger
    Join Date
    Nov 2002
    Location
    Seattle, Washington, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copying data to and from several tbls/dbs (2000)

    well, we're a non-profit and our client-screening and actual client stuff is pretty separate, and we've never had a screening database, so they asked me to make one to go along with the client db (to reduce duplication of work and make it possible to analyze that portion of our work), which is why there are now 2 (although i suppose we could include the tables and forms from the screeningdb in the clientdb - i'm just worried it might get too big, since we are tracking >500 fields of data). basically, if a client passes the screening and we actually begin to work with them, we then need to copy some of the info we gathered in the screening to the clientdb so that it is in a format useful to our service providers (who then add a bunch of their own data to the client records). also, some of the information is confidential and can only be seen by some of our staff.

    does this give you an ok idea of the set-up? does it seem like we really ought to combine them into one db somehow so we don't have to copy anything (eg names for clients and those not accepted as clients all in the same table with say the "case number" field >0 only for actual clients)? (it would mean re-doing a lot.)

    thanks so much,

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: copying data to and from several tbls/dbs (2000)

    As far as size is concerned, a given Access 2000 .mdb file can get as large as 2 GigaBytes, and a table can have a maximum of 255 fields. I certainly wouldn't recommend getting anywhere near either of those limits, though I doubt your are very close. What you may need in order to work with a single database is to have Access security active, if you don't already. That way you can control who sees what information, who is allowed to change it, and so on. For the kind of information you are working with, that's probably an important consideration in any event - you don't want casual snoopers to be able to see any of it.

    If it were I doing it, I would take a hard look at using only one database. Access 2000 will do pretty well with as many as 10 simultaneous users (as long as they aren't typing in data all day long as fast as they can - i.e. credit card transactions or some similar activity.) I would create a status field that shows the current status of each client - there could be several such as "in data entry", "waiting for additional info", "waiting for review", "Accepted", "Rejected", "Inactive", etc. Then you might have some tables that contain data just for clients that have been accepted. Hope this makes some sense. If it doesn't or you have additional questions, please post back.
    Wendell

  5. #5
    New Lounger
    Join Date
    Nov 2002
    Location
    Seattle, Washington, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copying data to and from several tbls/dbs (2000)

    thanks for the info. i knew about the table size max because we ran up against it a number of times (we're tracking many humndred fields), but the .mdb max i did not. i think i will try to merge the 2 databases and add a switchboard so that people can get to the portion they need without too much confusion. as far as security, we've actually been having some problems, given our network set-up and the person in charge of the network not wanting to have people join workgroups on their desktops (i'm not totally sure why except that there are other db's already in use in other workgroup files) and other issues. if you have any good suggestions for a direction to head in for table-specific user permissions (in additon to design change differnces) that would be easy to use on a multi-user network, i'd be very appreciative.

    thanks,

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: copying data to and from several tbls/dbs (2000)

    The easiest way to deal with the workgroup issue is to put a shortcut on the desktop that refers to the workgroup file for your application - help under command line willl show you how, but note that you need to specify the path to the msaccess.exe as well to make it work. We usually use a single workgroup file so we don't have to administer users multiple times whenever there is a personnel change, but you can make it pretty manageable if you only have a handful of users, not 60 or 100. Another option is to provide two front-ends and have users with less priviledge use one, and those with more priviledge use the other, but that can lead to maintenance headaches as design changes may need to be made in both. (I'm assuming you already have split the database into a front-end and a back-end; if you haven't you probably should as it makes design changes much easier.) Note that you can't impose field level restrictions at the table level, only at the form level, but queries can be used to restrict the amount of data that users see. You might also want to create a very skinny version of your database with either no data or 1 sample record and post it so people can look at your table structure - that might result in a clever design that would reduce the number of fields in your large tables. Hope this helps.
    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
  •