Help !! VBA code to copy

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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.


Ofer said:
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


Chase said:
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
Click to expand...
Click to expand...
 
Back
Top