Could my ADODB connection be causing a network problem?

T

Ted

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
 
J

Joel

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



.
Click to expand...
 
T

Ted

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


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



.
Click to expand...

__________ Information from ESET Smart Security, version of virus
signature database 4636 (20091125) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...


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

The message was checked by ESET Smart Security.

http://www.eset.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

help with database connection - urgent 5

Top