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