Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    code error on transfer file (acces 97 sr-2)

    Hello,
    I have a database at c:boyscoutsboyscouts.mdb and I also
    have the same database on a floppy. I am using the
    transfer database to send a table-tblawardsreceivedden, to
    the database on the floppy. I need to delete the table on
    the floppy before importing it to the floppy with the new
    data. I am getting an error that the table can't be found
    or the spelling of the table is wrong. I can't figure out
    my problem. I had the transfer working until I realized
    the table wasn't being deleted and the records were just
    being duplicated.
    Here is my code:
    '----------------------------------------------------------
    --
    ' macexpawardsorder
    '
    '----------------------------------------------------------
    --
    Function macexpawardsorder()
    On Error GoTo macexpawardsorder_Err
    Dim db As Database
    Dim ws As Workspace

    Set ws = DBEngine(0)
    Set db = ws.OpenDatabase("A:boyscouts.mdb")

    If "a:boyscouts.mdbtblawardsreceivedden" <> "" then


    DoCmd.DeleteObject
    acTable, "tblawardsreceivedden"

    End If


    'Set ws = DBEngine(0)
    Set db = ws.OpenDatabase("c:boyscoutsboyscouts.mdb")
    'DoCmd.DeleteObject acTable, "tblawardsreceivedden"
    DoCmd.TransferDatabase acExport, "Microsoft
    Access", "a:boyscouts.mdb", acTable, _
    "tblawardsreceived", "tblawardsreceivedden", False


    MsgBox "Transfer Complete, Remove Disk When Light on
    Your Floppy Drive Goes Out", vbOK, "Mike's Message"


    macexpawardsorder_Exit:
    Exit Function

    macexpawardsorder_Err:
    MsgBox Error$
    Resume macexpawardsorder_Exit

    End Function

  2. #2
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: code error on transfer file (acces 97 sr-2)

    Are you using the floppy as a backup for the database? If so, why not just write over the old .mdb?

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: code error on transfer file (acces 97 sr-2)

    The db will be loaded on different computers and they will be giving me the floppy with updated data. I will run the code to import the data. I want to delete the old table so I won't have duplicate records when I run an append query. After I update the data, I will give them the floppy back to import into their program. Again, deleting the table before they import. I need to do all this in code because they will not know how to do this.
    Thanks,
    Mike

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

    Re: code error on transfer file (acces 97 sr-2)

    You are setting the same workspace object twice without destroying it. Then you're trying to set the same database object twice without destroying it in between. At best, this is sloppy. At worst, it will cause you trouble. You need to close and destroy the database objects and workspaces when you finish using them. The only one that needs to be open for TransferDatabase is the one you're exporting the table from. If that's the current database, you don't need the OpenDatabase or the workspace at all.
    Charlotte

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

    Re: code error on transfer file (acces 97 sr-2)

    You're asking for trouble. When you delete a table, Access doesn't release the space it occupied. Copying a table to the database will increase the size of the database. On a hard disk, this is a nuisance - that's why you should compact a database regularly. But on a floppy disk, it can be disastrous - after a few cycles, the copy operation won't be possible any more. Moreover, writing to a database on a floppy disk is excruciatingly slow.

    So I would suggest:
    <UL><LI>Copy database from floppy to hard disk.
    <LI>Do all data handling.
    <LI>Compact database (you can compact a database - not the current database - in code)
    <LI>Copy database back to floppy.[/list]If you still want to use something of your code, it needs to be modified in several places.
    <UL><LI>You can't detect the existence of a table by just referring to "a:databasenametablename".
    <LI>You can't use DoCmd.DeleteObject to delete a table in another database, even if you opened it using OpenDatabase.
    <LI>As Charlotte remarked, you must be very careful about creating and destroying objects.[/list]The code could look like this:

    Function macExpAwardsOrder()
    On Error GoTo macExpAwardsOrder_Err
    Const strDatabase = "C:Boyscouts.mdb"
    Const strSource = "tblAwardsReceived"
    Const strTarget = "tblAwardsReceivedDen"
    Dim ws As Workspace
    Dim db As Database
    Set ws = DBEngine(0)
    Set db = ws.OpenDatabase(strDatabase)

    ' Try to delete table - no error message if table doesn't exist
    On Error Resume Next
    db.TableDefs.Delete strTarget
    On Error GoTo macExpAwardsOrder_Err

    DoCmd.TransferDatabase acExport, "Microsoft Access", strDatabase, _
    acTable, strSource, strTarget
    MsgBox "Transfer Complete, Remove Disk When Light on Your Floppy Drive Goes Out", _
    vbInformation, "Mike's Message"

    macExpAwardsOrder_Exit:
    ' Clean up
    On Error Resume Next
    db.Close
    Set db = Nothing
    Set ws = Nothing
    Exit Function

    macExpAwardsOrder_Err:
    MsgBox Err.Description, vbExclamation
    Resume macExpAwardsOrder_Exit
    End Function

  6. #6
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: code error on transfer file (acces 97 sr-2)

    I may be missing something here but is there a reason you do not have the database on a network? Your sneakernetting time would be cut down if your BE was on a network and I think there would be less chance for an error or corruption to occur.

  7. #7
    Star Lounger
    Join Date
    Jan 2001
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: code error on transfer file (acces 97 sr-2)

    The database will be given to 5 different people to install on their personal computers at home. They will be tracking awards earned by cut scouts. I will consolidate the awards and do the orders. Each has a different den. There is no network I can work with.
    I will try Hans' code above to get this to work. I will be compacting the database on the floppy each time I give it to the users to import the updated info.

    Thanks,
    Mike

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: code error on transfer file (acces 97 sr-2)

    Have you thought about zipping the database and emailing it to them?

  9. #9
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: code error on transfer file (acces 97 sr-2)

    I like Patt's zip idea.

    Here is another. If your database is the only one that will contain all the data from all the dens on one table you could create a primary key for each den to use. Maybe (Den#, Scout#, AwardDate, Award, LstName).

    You could add code to query the awards time period and export the data to an Excel file or any file format of your choice. Easily emailed due to its small size or again you could zip it. If you choose to stay with the floppy, file transfer is faster.

Posting Permissions

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