My employer's network is experiencing an unusual problem. An 8 mB write to
the network drive takes about 8 seconds but reading the same file takes
around 5 minutes. I'm wondering if the ADODB connection that I'm using (so
that our parts room can log part issues to an access db) could be causing
the problem? The db and the spreadsheet were created in Office 03 but the
db is used nearly continuously in Office 07. The Spreadsheet is used nearly
24/6. I have included my code.

Many many thanks in advance and Happy Thanksgiving
Ted
BTW There are 4 PC's (2-2007s & 2-2003s). One of the 2007's was installed
about 2 weeks ago.


Private Sub CommandButton1_Click()


Dim ttime As Variant
Dim cnn As ADODB.Connection

Dim cmdCommand As ADODB.Command
Dim vtSql
ActiveCell.Offset(0, -2).Range("A1").Value = ComboBox3.Value

'' Open the connection.
Set cnn = New ADODB.Connection
cnn.Open DbConnection

'' Set the command text.
Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = cnn

'' - - - - - - - - - - - - - - - - - - - - - - - - - - - -
vtSql = ""
vtSql = vtSql & " UPDATE tblItems"
vtSql = vtSql & " SET Onhand= " & Amt - TextBox1
vtSql = vtSql & " WHERE Part='" & ComboBox2.Value & "'"

With cmdCommand
.CommandText = vtSql
.CommandType = adCmdText
.Execute
End With




If TimeValue(TextBox3) > 0 And TimeValue(TextBox3) < 1 Then
ttime = Date + TimeValue(TextBox3)
Else
ttime = Now()
End If

TextBox2 = Replace(TextBox2, "+", "")


vtSql = "INSERT INTO tblTrans ([Part], [AftBal], [BefBal],
[Description], [Qty], [IssuedTo], [Clerk], [TransDate], [ToMachine]) SELECT
'" & ComboBox2 & "' AS Expr1, " & (Amt - TextBox1 * 1) & " as AftBal, " &
Amt * 1 & " as BefBal, '" & txtDescription & "' as Description, " & TextBox1
* 1 & " as Qty, '" & Trim(ComboBox3) & "' as IssuedTo, '" & TP & "' as
Clerk, '" & ttime & "' as TransDate, '" & ComboBox4 & "' as ToMachine;" ' "
& 1 & " as RptBal

With cmdCommand
.CommandText = vtSql
.CommandType = adCmdText
.Execute
End With

'' Close the connections and clean up.
cnn.Close
Set cmdCommand = Nothing
Set cnn = Nothing


Sheets("PartsList").Select
Range("A2").Select
Selection.QueryTable.Refresh BackgroundQuery:=False '2003
' Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False '2007
Sheets("Form").Select

' Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
' Sheets("Form").Select

Unload DATA_ENTRY
ActiveSheet.Protect Password:="S"

End Sub


__________ Information from ESET Smart Security, version of virus signature database 4634 (20091124) __________

The message was checked by ESET Smart Security.

http://www.eset.com

I may be the file is badly fragmented which means the the datq in the file
isn't continous of the recording medium (the hard drive or network drive).
tTe file is probably very large.

The other possiblilty which we had problems with at work with is the
INDEXING service wasn't working correctly and was accidently turned on on
some of our network drives. Indexing creates what is called a hash table for
accessing data from a drive. For some reason on microsoft PC's the hashing
which is suppose to speed up accessing files actually slow things down a lot.
I was having problems with microsoft office products when indexing was turn
on. Instead of taking a few seconds to open a small workbook it was taking
almost a minute to open the file.

"Ted" wrote:

> My employer's network is experiencing an unusual problem. An 8 mB write to
> the network drive takes about 8 seconds but reading the same file takes
> around 5 minutes. I'm wondering if the ADODB connection that I'm using (so
> that our parts room can log part issues to an access db) could be causing
> the problem? The db and the spreadsheet were created in Office 03 but the
> db is used nearly continuously in Office 07. The Spreadsheet is used nearly
> 24/6. I have included my code.
>
> Many many thanks in advance and Happy Thanksgiving
> Ted
> BTW There are 4 PC's (2-2007s & 2-2003s). One of the 2007's was installed
> about 2 weeks ago.
>
>
> Private Sub CommandButton1_Click()
>
>
> Dim ttime As Variant
> Dim cnn As ADODB.Connection
>
> Dim cmdCommand As ADODB.Command
> Dim vtSql
> ActiveCell.Offset(0, -2).Range("A1").Value = ComboBox3.Value
>
> '' Open the connection.
> Set cnn = New ADODB.Connection
> cnn.Open DbConnection
>
> '' Set the command text.
> Set cmdCommand = New ADODB.Command
> Set cmdCommand.ActiveConnection = cnn
>
> '' - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> vtSql = ""
> vtSql = vtSql & " UPDATE tblItems"
> vtSql = vtSql & " SET Onhand= " & Amt - TextBox1
> vtSql = vtSql & " WHERE Part='" & ComboBox2.Value & "'"
>
> With cmdCommand
> .CommandText = vtSql
> .CommandType = adCmdText
> .Execute
> End With
>
>
>
>
> If TimeValue(TextBox3) > 0 And TimeValue(TextBox3) < 1 Then
> ttime = Date + TimeValue(TextBox3)
> Else
> ttime = Now()
> End If
>
> TextBox2 = Replace(TextBox2, "+", "")
>
>
> vtSql = "INSERT INTO tblTrans ([Part], [AftBal], [BefBal],
> [Description], [Qty], [IssuedTo], [Clerk], [TransDate], [ToMachine]) SELECT
> '" & ComboBox2 & "' AS Expr1, " & (Amt - TextBox1 * 1) & " as AftBal, " &
> Amt * 1 & " as BefBal, '" & txtDescription & "' as Description, " & TextBox1
> * 1 & " as Qty, '" & Trim(ComboBox3) & "' as IssuedTo, '" & TP & "' as
> Clerk, '" & ttime & "' as TransDate, '" & ComboBox4 & "' as ToMachine;" ' "
> & 1 & " as RptBal
>
> With cmdCommand
> .CommandText = vtSql
> .CommandType = adCmdText
> .Execute
> End With
>
> '' Close the connections and clean up.
> cnn.Close
> Set cmdCommand = Nothing
> Set cnn = Nothing
>
>
> Sheets("PartsList").Select
> Range("A2").Select
> Selection.QueryTable.Refresh BackgroundQuery:=False '2003
> ' Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False '2007
> Sheets("Form").Select
>
> ' Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
> ' Sheets("Form").Select
>
> Unload DATA_ENTRY
> ActiveSheet.Protect Password:="S"
>
> End Sub
>
>
> __________ Information from ESET Smart Security, version of virus signature database 4634 (20091124) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
>
> .
>

Thank you for your advice. I'll discuss it with our IT guy.


"Joel" <(E-Mail Removed)> wrote in message
news:5F8BE38A-94EC-40A4-B67F-(E-Mail Removed)...
>I may be the file is badly fragmented which means the the datq in the file
> isn't continous of the recording medium (the hard drive or network drive).
> tTe file is probably very large.
>
> The other possiblilty which we had problems with at work with is the
> INDEXING service wasn't working correctly and was accidently turned on on
> some of our network drives. Indexing creates what is called a hash table
> for
> accessing data from a drive. For some reason on microsoft PC's the
> hashing
> which is suppose to speed up accessing files actually slow things down a
> lot.
> I was having problems with microsoft office products when indexing was
> turn
> on. Instead of taking a few seconds to open a small workbook it was
> taking
> almost a minute to open the file.
>
> "Ted" wrote:
>
>> My employer's network is experiencing an unusual problem. An 8 mB write
>> to
>> the network drive takes about 8 seconds but reading the same file takes
>> around 5 minutes. I'm wondering if the ADODB connection that I'm using
>> (so
>> that our parts room can log part issues to an access db) could be causing
>> the problem? The db and the spreadsheet were created in Office 03 but
>> the
>> db is used nearly continuously in Office 07. The Spreadsheet is used
>> nearly
>> 24/6. I have included my code.
>>
>> Many many thanks in advance and Happy Thanksgiving
>> Ted
>> BTW There are 4 PC's (2-2007s & 2-2003s). One of the 2007's was
>> installed
>> about 2 weeks ago.
>>
>>
>> Private Sub CommandButton1_Click()
>>
>>
>> Dim ttime As Variant
>> Dim cnn As ADODB.Connection
>>
>> Dim cmdCommand As ADODB.Command
>> Dim vtSql
>> ActiveCell.Offset(0, -2).Range("A1").Value = ComboBox3.Value
>>
>> '' Open the connection.
>> Set cnn = New ADODB.Connection
>> cnn.Open DbConnection
>>
>> '' Set the command text.
>> Set cmdCommand = New ADODB.Command
>> Set cmdCommand.ActiveConnection = cnn
>>
>> '' - - - - - - - - - - - - - - - - - - - - - - - - - - - -
>> vtSql = ""
>> vtSql = vtSql & " UPDATE tblItems"
>> vtSql = vtSql & " SET Onhand= " & Amt - TextBox1
>> vtSql = vtSql & " WHERE Part='" & ComboBox2.Value & "'"
>>
>> With cmdCommand
>> .CommandText = vtSql
>> .CommandType = adCmdText
>> .Execute
>> End With
>>
>>
>>
>>
>> If TimeValue(TextBox3) > 0 And TimeValue(TextBox3) < 1 Then
>> ttime = Date + TimeValue(TextBox3)
>> Else
>> ttime = Now()
>> End If
>>
>> TextBox2 = Replace(TextBox2, "+", "")
>>
>>
>> vtSql = "INSERT INTO tblTrans ([Part], [AftBal], [BefBal],
>> [Description], [Qty], [IssuedTo], [Clerk], [TransDate], [ToMachine])
>> SELECT
>> '" & ComboBox2 & "' AS Expr1, " & (Amt - TextBox1 * 1) & " as AftBal, "
>> &
>> Amt * 1 & " as BefBal, '" & txtDescription & "' as Description, " &
>> TextBox1
>> * 1 & " as Qty, '" & Trim(ComboBox3) & "' as IssuedTo, '" & TP & "' as
>> Clerk, '" & ttime & "' as TransDate, '" & ComboBox4 & "' as ToMachine;" '
>> "
>> & 1 & " as RptBal
>>
>> With cmdCommand
>> .CommandText = vtSql
>> .CommandType = adCmdText
>> .Execute
>> End With
>>
>> '' Close the connections and clean up.
>> cnn.Close
>> Set cmdCommand = Nothing
>> Set cnn = Nothing
>>
>>
>> Sheets("PartsList").Select
>> Range("A2").Select
>> Selection.QueryTable.Refresh BackgroundQuery:=False
>> '2003
>> ' Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
>> '2007
>> Sheets("Form").Select
>>
>> ' Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
>> ' Sheets("Form").Select
>>
>> Unload DATA_ENTRY
>> ActiveSheet.Protect Password:="S"
>>
>> End Sub
>>
>>
>> __________ Information from ESET Smart Security, version of virus
>> signature database 4634 (20091124) __________
>>
>> The message was checked by ESET Smart Security.
>>
>> http://www.eset.com
>>
>>
>>
>> .
>>
>
> __________ Information from ESET Smart Security, version of virus
> signature database 4636 (20091125) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
>


__________ Information from ESET Smart Security, version of virus signature database 4643 (20091127) __________

The message was checked by ESET Smart Security.

http://www.eset.com