Help !! VBA code to copy

[Chase]

I am using Access 2003 running as 2000. My PC OS is Win XP Pro on a Windows
2003 network.

The following is two versions of the VBA code I have on a Form the first is
on the local PC "C:\" drive and works the second is on a net work driv a
generates the following error:-
"Error No: 3734; Description: The database has been plased in a stste by
'Admin' on machine 'WS-XP-018' that prevents it from being opened r locked."

First Version that works

Private Sub cmdExpSum_Click()
On Error GoTo ErrorHandler
Dim StrPath As String
Dim StrRegion As String

StrPath = "C:\VolumeDatabase\Reports\" & txtFileName & ".xls"
StrRegion = ("qctb" & Forms![frmReportOps]![cboRegion])
DoCmd.SetWarnings False
Me.Form.Requery
DoCmd.CopyObject "C:\VolumeDatabase\ProcessReport.mdb", StrRegion,
acQuery, "qctbSummary"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, StrRegion,
StrPath, True
DoCmd.DeleteObject acQuery, StrRegion
DoCmd.SetWarnings True

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & _
Err.Description
Resume ErrorHandlerExit

End Sub

Second Version that does not work

Private Sub cmdExpSum_Click()
On Error GoTo ErrorHandler
Dim StrPath As String
Dim StrRegion As String

StrPath = "\\admiral-srv1\data\VolumeDatabase\Reports\" & txtFileName &
".xls"
StrRegion = ("qctb" & Forms![frmReportOps]![cboRegion])
DoCmd.SetWarnings False
Me.Form.Requery
DoCmd.CopyObject "\\admiral-srv1\data\VolumeDatabase\ProcessReport.mdb",
StrRegion, acQuery, "qctbSummary"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, StrRegion,
StrPath, True
DoCmd.DeleteObject acQuery, StrRegion
DoCmd.SetWarnings True

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & _
Err.Description
Resume ErrorHandlerExit

End Sub

PLEASE HELP

 

[Ofer]

The first thing I would do, is check the permissions on the network drive,
try and create a text file in it, to see if you have permissions.
Not the MDB, the directory you are copying data to.

 

[Chase]

Ofer

Outputing the fexcel spreadsheet from the database to the server works fine
it is the copying of an object within the database that is the problem.

The first thing I would do, is check the permissions on the network drive,
try and create a text file in it, to see if you have permissions.
Not the MDB, the directory you are copying data to.
--
I hope that helped
Good luck

I am using Access 2003 running as 2000. My PC OS is Win XP Pro on a Windows
2003 network.

The following is two versions of the VBA code I have on a Form the first is
on the local PC "C:\" drive and works the second is on a net work driv a
generates the following error:-
"Error No: 3734; Description: The database has been plased in a stste by
'Admin' on machine 'WS-XP-018' that prevents it from being opened r locked."

First Version that works

Private Sub cmdExpSum_Click()
On Error GoTo ErrorHandler
Dim StrPath As String
Dim StrRegion As String

StrPath = "C:\VolumeDatabase\Reports\" & txtFileName & ".xls"
StrRegion = ("qctb" & Forms![frmReportOps]![cboRegion])
DoCmd.SetWarnings False
Me.Form.Requery
DoCmd.CopyObject "C:\VolumeDatabase\ProcessReport.mdb", StrRegion,
acQuery, "qctbSummary"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, StrRegion,
StrPath, True
DoCmd.DeleteObject acQuery, StrRegion
DoCmd.SetWarnings True

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & _
Err.Description
Resume ErrorHandlerExit

End Sub

Second Version that does not work

Private Sub cmdExpSum_Click()
On Error GoTo ErrorHandler
Dim StrPath As String
Dim StrRegion As String

StrPath = "\\admiral-srv1\data\VolumeDatabase\Reports\" & txtFileName &
".xls"
StrRegion = ("qctb" & Forms![frmReportOps]![cboRegion])
DoCmd.SetWarnings False
Me.Form.Requery
DoCmd.CopyObject "\\admiral-srv1\data\VolumeDatabase\ProcessReport.mdb",
StrRegion, acQuery, "qctbSummary"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, StrRegion,
StrPath, True
DoCmd.DeleteObject acQuery, StrRegion
DoCmd.SetWarnings True

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & _
Err.Description
Resume ErrorHandlerExit

End Sub

PLEASE HELP