Today I upgraded my work PC from Access 2000 (which my original database was written with) to Access 2003.
Everything seems to work regularly except the following line of code.
DoCmd.TransferSpreadsheet acExport, , "tblLANConnections", strBackupPath & "tblLANConnections.xls", True
When I step through my routine (attached below), access halts and the standard error message pops up saying that 'Microsoft Office Access has encountered a problem and needs to close. We are sorry for the inconvenience.", with options to send an error report, and to repair and restart the database.
Ive done the following things for troubleshooting:
1) Tested the database and backup directory being on a network share, or local, or a combination of both.
2) Emptying out the currently existing tblLANConnections.xls file.
3) Deleting and re-creating the tblLANConnections.xls file. (I cant make transferspreadsheet create a new copy for some reason, but thats another battle)
4) Went through the help file to ensure that my syntax is correct.
5) Changed the syntax of the command to code in optional variants.
Im pretty sure its not related to the path being a variable, as the TransferDatabase commands directly above the culprit lines work without a hitch.
If anyone has any ideas about how to get around this it would be appreciated. Thank you!
Code-----
Function Backup() 'Function that backs up all tables to a backup database and excel files
strBackupPath = DLookup("[BackupPath]", "tblVariables")
DoCmd.TransferDatabase acExport, "Microsoft Access", strBackupPath & "scdbbackup.mdb", acTable, "tblSANConnections", "tblSANConnections"
DoCmd.TransferDatabase acExport, "Microsoft Access", strBackupPath & "scdbbackup.mdb", acTable, "tblSANReclaims", "tblSANReclaims"
DoCmd.TransferDatabase acExport, "Microsoft Access", strBackupPath & "scdbbackup.mdb", acTable, "tblLANConnections", "tblLANConnections"
DoCmd.TransferDatabase acExport, "Microsoft Access", strBackupPath & "scdbbackup.mdb", acTable, "tblLANReclaims", "tblLANReclaims"
DoCmd.TransferSpreadsheet acExport, , "tblLANConnections", strBackupPath & "tblLANConnections.xls", True
DoCmd.TransferSpreadsheet acExport, , "tblLANReclaims", strBackupPath & "tblLANReclaims.xls", True
DoCmd.TransferSpreadsheet acExport, , "tblSANConnections", strBackupPath & "tblSANConnections.xls", True
DoCmd.TransferSpreadsheet acExport, , "tblSANReclaims", strBackupPath & "tblSANReclaims.xls", True
End Function
Everything seems to work regularly except the following line of code.
DoCmd.TransferSpreadsheet acExport, , "tblLANConnections", strBackupPath & "tblLANConnections.xls", True
When I step through my routine (attached below), access halts and the standard error message pops up saying that 'Microsoft Office Access has encountered a problem and needs to close. We are sorry for the inconvenience.", with options to send an error report, and to repair and restart the database.
Ive done the following things for troubleshooting:
1) Tested the database and backup directory being on a network share, or local, or a combination of both.
2) Emptying out the currently existing tblLANConnections.xls file.
3) Deleting and re-creating the tblLANConnections.xls file. (I cant make transferspreadsheet create a new copy for some reason, but thats another battle)
4) Went through the help file to ensure that my syntax is correct.
5) Changed the syntax of the command to code in optional variants.
Im pretty sure its not related to the path being a variable, as the TransferDatabase commands directly above the culprit lines work without a hitch.
If anyone has any ideas about how to get around this it would be appreciated. Thank you!
Code-----
Function Backup() 'Function that backs up all tables to a backup database and excel files
strBackupPath = DLookup("[BackupPath]", "tblVariables")
DoCmd.TransferDatabase acExport, "Microsoft Access", strBackupPath & "scdbbackup.mdb", acTable, "tblSANConnections", "tblSANConnections"
DoCmd.TransferDatabase acExport, "Microsoft Access", strBackupPath & "scdbbackup.mdb", acTable, "tblSANReclaims", "tblSANReclaims"
DoCmd.TransferDatabase acExport, "Microsoft Access", strBackupPath & "scdbbackup.mdb", acTable, "tblLANConnections", "tblLANConnections"
DoCmd.TransferDatabase acExport, "Microsoft Access", strBackupPath & "scdbbackup.mdb", acTable, "tblLANReclaims", "tblLANReclaims"
DoCmd.TransferSpreadsheet acExport, , "tblLANConnections", strBackupPath & "tblLANConnections.xls", True
DoCmd.TransferSpreadsheet acExport, , "tblLANReclaims", strBackupPath & "tblLANReclaims.xls", True
DoCmd.TransferSpreadsheet acExport, , "tblSANConnections", strBackupPath & "tblSANConnections.xls", True
DoCmd.TransferSpreadsheet acExport, , "tblSANReclaims", strBackupPath & "tblSANReclaims.xls", True
End Function
Last edited:
