Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    BE MDB Table Maintenance (Access2K)

    Wendell, Charlotte & Hans,

    A couple of weeks ago we had a minor dialogue about this topic under a thread entitled "Copy Table Data w/Autonumber ", without much resolution. I indicated that I'd go think on it a while, which I've done. The result is I changed my prospective a bit, and came up with a rather elegant solution to the underlying problem.

    I have created an example FE & BE MDB file to exemplify the technique, as well as a short accompanying "txt" file. All three files are in the attached zip file. If you, or other loungers find it of interest, feel free to propagate it as needed. Otherwise, just ignore it.

    One caveate: The main file, "BEDataUpdate.mdb" was created using Access2K. However, I could not get it to compress sufficiently to meet the 100K attachment limitation. Therefore, that single file, "BEDataUpdate.mdb" had to be converted to Access 97 before being placed into the ZIP file. Since I don't have Access 97 with which to test it, I cannot vouch for it in either this state, or in a correct reconversion back to Access2K. If it does not work, or has been somehow scrambled in the process, let me know, and we can work out some other mechanism to get the 2K version where it needs to go.

    Thanks, guys, for all your assistance in pointing me in the correct directions across the months.
    -- Jim
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: BE MDB Table Maintenance (Access2K)

    Hi Jim

    Thanks for sharing. Your BEDataUpdate97.mdb contains several references to object libraries that are not needed. Microsoft Office Server Extensions 1.0 wasn't available on my PC, so it caused an error message. After removing the reference, the VBA code compiled correctly in Access 2002, with the database converted back to Access 2000 format. It wouldn't work in Access 97, since you use the InStrRev function that was introduced in Office 2000 VBA.

    Why do you use .LookIn = ParsePath(strPath, 3) in the FindFile function? This will try to look for a file in the root of the drive that contains the update db, without searching (sub)folders. Or should it have been .SearchSubFolders = True instead of .SearchSubFolders = False ?

  3. #3
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: BE MDB Table Maintenance (Access2K)

    Hans,

    Thanks for checking out the code. Didn't occur to me go edit out my standard list of References..

    As to the .LookIn=.... stuff, that was/is pure oversight on my part. Didn't seem to matter so much in this little demo, but certainly would matter as it was incorporated into someone else's project.

    -- Jim

  4. #4
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: BE MDB Table Maintenance (Access2K)

    Hans,

    In the spirit of "completeness", I have adjusted the References list, and fixed my obvious oversights in the FindFile function. In the process, it seems to have shrunk the resulting file such that the zip is now less than the 100K filter.

    Thanks again.
    -- Jim
    Attached Files Attached Files

Posting Permissions

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