Slow to Update

[DEI]

I am using the following ADO code to update records in a table, and it runs
very slowly. Does anyone have any suggestons about why? Is there a way to
make the updates run faster? The fields I am updating are not indexed -
thanks.

Public Sub Populate()

' - Populates blank fields.
' - ex. [LOC_CODE] and [MOD_CODE]
' - Fields targeted for update MUST have values in the first record.

Dim conn As ADODB.Connection
Dim rs As New ADODB.Recordset

Dim fonename As String
Dim ftwoname As String
Dim fone As String
Dim ftwo As String

Set conn = CurrentProject.Connection

fonename = "LOC_CODE"
ftwoname = "MOD_CODE"

rs.Open "[ZIPS]", conn, adOpenKeyset, adLockOptimistic

'Update LOC_CODE
'Check to see if first record is null

rs.MoveFirst

If IsNull(rs.Fields(fonename)) Then
MsgBox ("First Record CANNOT be Null")
Exit Sub
End If

For x = 1 To rs.RecordCount
If IsNull(rs.Fields(fonename)) Then
rs(fonename) = fone
rs.Update
End If
fone = rs.Fields(fonename)
rs.MoveNext
Next x

'Update MOD_CODE
'Check to see if first record is null

rs.MoveFirst

If IsNull(rs.Fields(ftwoname)) Then
MsgBox ("First Record CANNOT be Null")
Exit Sub
End If

For x = 1 To rs.RecordCount
If IsNull(rs.Fields(ftwoname)) Then
rs(ftwoname) = fone
rs.Update
End If
fone = rs.Fields(ftwoname)
rs.MoveNext
Next x

rs.Close

Set rs = Nothing
Set conn = Nothing

End Sub

 

[Tom Wickerath]

Hello DEI,

The first thing you should do is to enforce variable declaration by using
Option Explicit as the second line of code in all modules. You are using an
undeclared variable x. See this link for the reason why, and more
importantly, how to set the option in the VBE to always get Option Explicit:

"Always use Option Explicit"
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions

Question for you:
Are you updating linked tables? If so, does the path to the back-end
database include any folders that are longer than 8 characters in length?
See the following KB article for more information. Although the paragraph
talks specifically about append queries, I wonder if the same delay is not
seen with update queries?

http://support.microsoft.com/?id=889588#XSLTH4124121123120121120120

There are other useful tips in this article as well.

The fields I am updating are not indexed

Is [ZIPS] a query? If so, does it include any criteria or sorting? You
should create indexes on fields used as criteria and/or to apply sorts, in
order to avoid table scans. You might want to check the following article for
more information:

Use Microsoft Jet's ShowPlan to write more efficient queries
http://builder.com.com/5100-6388-5064388.html

If my answer has helped you, please answer yes to the question that reads
"Did this post answer the question?" at the bottom of the message thread.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

I am using the following ADO code to update records in a table, and it runs
very slowly. Does anyone have any suggestons about why? Is there a way to
make the updates run faster? The fields I am updating are not indexed -
thanks.

Public Sub Populate()

' - Populates blank fields.
' - ex. [LOC_CODE] and [MOD_CODE]
' - Fields targeted for update MUST have values in the first record.

Dim conn As ADODB.Connection
Dim rs As New ADODB.Recordset

Dim fonename As String
Dim ftwoname As String
Dim fone As String
Dim ftwo As String

Set conn = CurrentProject.Connection

fonename = "LOC_CODE"
ftwoname = "MOD_CODE"

rs.Open "[ZIPS]", conn, adOpenKeyset, adLockOptimistic

'Update LOC_CODE
'Check to see if first record is null

rs.MoveFirst

If IsNull(rs.Fields(fonename)) Then
MsgBox ("First Record CANNOT be Null")
Exit Sub
End If

For x = 1 To rs.RecordCount
If IsNull(rs.Fields(fonename)) Then
rs(fonename) = fone
rs.Update
End If
fone = rs.Fields(fonename)
rs.MoveNext
Next x

'Update MOD_CODE
'Check to see if first record is null

rs.MoveFirst

If IsNull(rs.Fields(ftwoname)) Then
MsgBox ("First Record CANNOT be Null")
Exit Sub
End If

For x = 1 To rs.RecordCount
If IsNull(rs.Fields(ftwoname)) Then
rs(ftwoname) = fone
rs.Update
End If
fone = rs.Fields(ftwoname)
rs.MoveNext
Next x

rs.Close

Set rs = Nothing
Set conn = Nothing

End Sub

 

[DEI]

Thanks for your reply, and I will look into the Option Explicit issue.

But [ZIPS] is a table in the currently open database; there is no use of a
reference to a back end database. The table is not involved in any
relationships, it actually just formats data that is eventually appended to
another table.

Hello DEI,

The first thing you should do is to enforce variable declaration by using
Option Explicit as the second line of code in all modules. You are using an
undeclared variable x. See this link for the reason why, and more
importantly, how to set the option in the VBE to always get Option Explicit:

"Always use Option Explicit"
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions

Question for you:
Are you updating linked tables? If so, does the path to the back-end
database include any folders that are longer than 8 characters in length?
See the following KB article for more information. Although the paragraph
talks specifically about append queries, I wonder if the same delay is not
seen with update queries?

http://support.microsoft.com/?id=889588#XSLTH4124121123120121120120

There are other useful tips in this article as well.

The fields I am updating are not indexed

Is [ZIPS] a query? If so, does it include any criteria or sorting? You
should create indexes on fields used as criteria and/or to apply sorts, in
order to avoid table scans. You might want to check the following article for
more information:

Use Microsoft Jet's ShowPlan to write more efficient queries
http://builder.com.com/5100-6388-5064388.html

If my answer has helped you, please answer yes to the question that reads
"Did this post answer the question?" at the bottom of the message thread.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

I am using the following ADO code to update records in a table, and it runs
very slowly. Does anyone have any suggestons about why? Is there a way to
make the updates run faster? The fields I am updating are not indexed -
thanks.

Public Sub Populate()

' - Populates blank fields.
' - ex. [LOC_CODE] and [MOD_CODE]
' - Fields targeted for update MUST have values in the first record.

Dim conn As ADODB.Connection
Dim rs As New ADODB.Recordset

Dim fonename As String
Dim ftwoname As String
Dim fone As String
Dim ftwo As String

Set conn = CurrentProject.Connection

fonename = "LOC_CODE"
ftwoname = "MOD_CODE"

rs.Open "[ZIPS]", conn, adOpenKeyset, adLockOptimistic

'Update LOC_CODE
'Check to see if first record is null

rs.MoveFirst

If IsNull(rs.Fields(fonename)) Then
MsgBox ("First Record CANNOT be Null")
Exit Sub
End If

For x = 1 To rs.RecordCount
If IsNull(rs.Fields(fonename)) Then
rs(fonename) = fone
rs.Update
End If
fone = rs.Fields(fonename)
rs.MoveNext
Next x

'Update MOD_CODE
'Check to see if first record is null

rs.MoveFirst

If IsNull(rs.Fields(ftwoname)) Then
MsgBox ("First Record CANNOT be Null")
Exit Sub
End If

For x = 1 To rs.RecordCount
If IsNull(rs.Fields(ftwoname)) Then
rs(ftwoname) = fone
rs.Update
End If
fone = rs.Fields(ftwoname)
rs.MoveNext
Next x

rs.Close

Set rs = Nothing
Set conn = Nothing

End Sub

 

[Tom Wickerath]

Hello DEI,

Is your database on your PC's local hard drive, or are you opening it over a
network? How many records are we talking about in the ZIPS table? Have you
tried moving your database to another PC to see if your code will run faster?

If it is any consolation, I created a new database to test your code. I
imported a copy of the Northwind Customers table (91 records) and renamed it
to ZIPS. I changed the following two lines of code from:

fonename = "LOC_CODE"
ftwoname = "MOD_CODE"

to:

fonename = "ContactName"
ftwoname = "ContactTitle"

I also inserted a debug.print statement at the end of your procedure, and
opened the Immediate window (Ctrl G):

Set rs = Nothing
Set conn = Nothing
Debug.Print "Done" <-----
End Sub

The message "Done" was printed to the immediate window almost
instantaneously when I tested your procedure. Of course, this was a
relatively small table and everything was run local (no network involved),
but my PC is already (4) years old, so it's not the fastest kid on the block
any longer.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Thanks for your reply, and I will look into the Option Explicit issue.

But [ZIPS] is a table in the currently open database; there is no use of a
reference to a back end database. The table is not involved in any
relationships, it actually just formats data that is eventually appended to
another table.
__________________________________________

:

Hello DEI,

The first thing you should do is to enforce variable declaration by using
Option Explicit as the second line of code in all modules. You are using an
undeclared variable x. See this link for the reason why, and more
importantly, how to set the option in the VBE to always get Option Explicit:

"Always use Option Explicit"
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions

Question for you:
Are you updating linked tables? If so, does the path to the back-end
database include any folders that are longer than 8 characters in length?
See the following KB article for more information. Although the paragraph
talks specifically about append queries, I wonder if the same delay is not
seen with update queries?

http://support.microsoft.com/?id=889588#XSLTH4124121123120121120120

There are other useful tips in this article as well.

The fields I am updating are not indexed

Is [ZIPS] a query? If so, does it include any criteria or sorting? You
should create indexes on fields used as criteria and/or to apply sorts, in
order to avoid table scans. You might want to check the following article for
more information:

Use Microsoft Jet's ShowPlan to write more efficient queries
http://builder.com.com/5100-6388-5064388.html

If my answer has helped you, please answer yes to the question that reads
"Did this post answer the question?" at the bottom of the message thread.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

I am using the following ADO code to update records in a table, and it runs
very slowly. Does anyone have any suggestons about why? Is there a way to
make the updates run faster? The fields I am updating are not indexed -
thanks.

Public Sub Populate()

' - Populates blank fields.
' - ex. [LOC_CODE] and [MOD_CODE]
' - Fields targeted for update MUST have values in the first record.

Dim conn As ADODB.Connection
Dim rs As New ADODB.Recordset

Dim fonename As String
Dim ftwoname As String
Dim fone As String
Dim ftwo As String

Set conn = CurrentProject.Connection

fonename = "LOC_CODE"
ftwoname = "MOD_CODE"

rs.Open "[ZIPS]", conn, adOpenKeyset, adLockOptimistic

'Update LOC_CODE
'Check to see if first record is null

rs.MoveFirst

If IsNull(rs.Fields(fonename)) Then
MsgBox ("First Record CANNOT be Null")
Exit Sub
End If

For x = 1 To rs.RecordCount
If IsNull(rs.Fields(fonename)) Then
rs(fonename) = fone
rs.Update
End If
fone = rs.Fields(fonename)
rs.MoveNext
Next x

'Update MOD_CODE
'Check to see if first record is null

rs.MoveFirst

If IsNull(rs.Fields(ftwoname)) Then
MsgBox ("First Record CANNOT be Null")
Exit Sub
End If

For x = 1 To rs.RecordCount
If IsNull(rs.Fields(ftwoname)) Then
rs(ftwoname) = fone
rs.Update
End If
fone = rs.Fields(ftwoname)
rs.MoveNext
Next x

rs.Close

Set rs = Nothing
Set conn = Nothing

End Sub