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

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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:

• Copy database from floppy to hard disk.
• Do all data handling.
• Compact database (you can compact a database – not the current database – in code)
• Copy database back to floppy.
  [/list]If you still want to use something of your code, it needs to be modified in several places.
  • You can’t detect the existence of a table by just referring to “a:databasenametablename”.
  • You can’t use DoCmd.DeleteObject to delete a table in another database, even if you opened it using OpenDatabase.
  • 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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote