CopyObject VBA Code Problem

[Guest]

I am using Access 2003 running as 2000. My PC's OS is WIN XP Pro on a windows
2003 network.
I am the developer with sole ability to view and maintain the database. The
database should sit on the network but will not run the VBA code below when I
do so.
There is 3 elements to the VBA code; First is to copy a query based on a
template and name in a text box; second export the new query to Excel; third
delete the new query.

With the DB sat on the loacl "C:\" drive with the copy code directed to the
same drive the copyobject routine works with no error.

With the DB sat on the network drive with the copy code directed to the
network the copyobject routine fails with the following error: -
"Error No: 3734; Description: The database has been placed in a stste by
'Admin' on machine 'WS-XP-018' that prevents it from being opened or locked."

Below is the code form the 2 locations

First 'C:\' drive
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 "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 Network Dirve
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

 

[Guest]

Hi, Chase.

If your computer is named "WS-XP-018," then your own computer is blocking
you from copying the query to the destination database. If you have the
destination database open while this code is running, then ensure that you
haven't opened it in exclusive mode (you must have "Full Control" Windows
security permissions on the directory where the destination database is
located) and check your VBA code for things that would elevate the
destination database to exclusive mode, such as editing a form, report, or
module, or opening multiple Database Objects in the default Workspace Object,
et cetera.

Whether or not the destination database is open from your computer while
this code is running, if you have anti-virus software running with network
scanning turned on, then this may be interfering.

If there's an exception to your Windows 2003 Server network, and the
destination database is actually located on a Windows 2000 Server with Server
Message Block (SMB) turned on, then this could be the problem. For more
information, please see the following Web page:

http://support.microsoft.com/default.aspx?id=814112

If your computer is not named "WS-XP-018," then find out who has this
computer and ask him to close the database from his computer so that you can
edit the database.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

I am using Access 2003 running as 2000. My PC's OS is WIN XP Pro on a windows
2003 network.
I am the developer with sole ability to view and maintain the database. The
database should sit on the network but will not run the VBA code below when I
do so.
There is 3 elements to the VBA code; First is to copy a query based on a
template and name in a text box; second export the new query to Excel; third
delete the new query.

With the DB sat on the loacl "C:\" drive with the copy code directed to the
same drive the copyobject routine works with no error.

With the DB sat on the network drive with the copy code directed to the
network the copyobject routine fails with the following error: -
"Error No: 3734; Description: The database has been placed in a stste by
'Admin' on machine 'WS-XP-018' that prevents it from being opened or locked."

Below is the code form the 2 locations

First 'C:\' drive
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 "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 Network Dirve
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