Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    May 2002
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deleting\Importing Queries (ACC97)

    My company currently uses Access 97 to generate reports and queries of our accounting data. The problem I'm having is a Department Head generates queries and reports for the Accounting office to use. The database is not currently set up to be shared (DONT ASK!), but every week or so I get asked to import the queries and reports from the the Department Heads computer to the Accounting computer for updating. When I import the external data, the queries and reports don't overwrite those with the same name, but appends a 1, 2, 3, etc. to the end. As you can guess, the Department head doesn't want that so I have to physically delete the old ones one at a time before I can update. Is there a way to overwrite the old queries and reports with the updated ones, or at least is there a way to bulk delete the old ones. Deleting 50 to 60 queries and reports one at a time is VERY tedious and error prone. Is there a script I can write to do this for me?

    Thanks,
    GH

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting\Importing Queries (ACC97)

    I am sure this process is as questionable to you as it is to me, so I'll leave my opinion out of the reply.

    Look at the DeleteObject method in Visual Basic Help which carries out the DeleteObject action, as in:

    DoCmd.DeleteObject [objecttype][, objectname]

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting\Importing Queries (ACC97)

    How about splitting the database into a front end and a backend and putting both on the network. Then you could have different frontends for different people.

  4. #4
    New Lounger
    Join Date
    May 2002
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting\Importing Queries (ACC97)

    Thanks, I'm looking into it as we speak.

  5. #5
    New Lounger
    Join Date
    May 2002
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting\Importing Queries (ACC97)

    It's a PeachtreePAWet generated database, that apparently can't be on a network because the company doesn't want the Peachtree data on the network. I'm a database guy, not a Peachtree accounting guy, so I can't validate any of this, but that's seems to be the reason why.

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting\Importing Queries (ACC97)

    I haven't worked with replication yet, but replication may be the way to go. Does anyone have any ideas along this line?

  7. #7
    New Lounger
    Join Date
    May 2002
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting\Importing Queries (ACC97)

    I've read about it, but can it keep the data up to date both ways? For example, can I pull the data over from the Accounting office to the Department Heads computer, have him make his changes, then push it back to the Accouting Office without the Accounting Office updates since the first move being lost? Things that make you go, hmmmmmmmmmmm. Thanks for the feedback! Everything helps!

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Deleting\Importing Queries (ACC97)

    Unless Peachtree has changed a lot in the last few years, the application itself doesn't like its back end being on a network drive. I ran into that a couple of jobs back.
    Charlotte

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Deleting\Importing Queries (ACC97)

    1) Your Department Head should be strung up by his thumbs ... all ten of them!

    2) Is the department head creating reports in Peachtree or does he have a database where he is analyzing data in reports? If the latter, the simplest way to get around the problem is to do an export from his database rather than an import from the Accounting department's database. Export using TransferDatabase and the object will overwrite any existing object with the same name. You can easily do it in code or create a macro that will export each of the queries or reports to the other database.
    Charlotte

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Deleting\Importing Queries (ACC97)

    You do not want to use replication in this instance. Replication is primarily useful for split databases and design changes can only be made to the design master, not to any of the replicas. Data is synced both ways, but don't *ever* replicate an unsplit database if you want to keep your sanity. Trust me on this one.
    Charlotte

  11. #11
    New Lounger
    Join Date
    May 2002
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting\Importing Queries (ACC97)

    Sounds good to me! I'll give it a try today.

    To answer #2, Peachtree is used for billing and data entry. The DH doesn't like how the reports look so we use PAWet to export the Peachtree data to Access, then use Access to generate the reports. Not the best solution I've seen, but whatever floats their boat.

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Deleting\Importing Queries (ACC97)

    I've seen their reports, and I have to agree with him. The export should solve the problem. If you need help, post back.
    Charlotte

  13. #13
    New Lounger
    Join Date
    May 2002
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deleting\Importing Queries (ACC97)

    IT WORKS!!
    Charlotte, you rock! <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

    Thanks a ton. That's about 45 minutes saved!

Posting Permissions

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