B
BobV
Group:
I am experiencing very slow performance of my 2002 Access application when I
install the backend database on the network server and the frontend database
on my local computer. Upon opening the frontend database I have a auto exec
macro that links the tables in the backend database to the front-end
database. I am using DAO to connect to the backend database file.
The slow performance occurs when I execute a number of UPDATE statements
that update tables in the backend database file. I have included a sample of
one of the UPDATE statements that slows down the performance of my
application. The variable MonthlyDepr is a fairly complex subroutine that
calculates the current month depreciation and enters it in columns 1 through
12 of the backend database table. The below UPDATE statement is just one of
several such statements that I have.
When I run the same UPDATE statements on a workstation where the frontend
and backend database files are in the same folder on the local computer, the
subroutine's performance is fairly fast. It's just when the backend database
is located on the external server that the performance slows to a snail's
pace.
Is there something that I am doing incorrectly? Or, should I be using a
different statement to do the update. Should I use ADO to make the
connection to the backend database? Will that make the performance quicker?
Thanks for your help.
Bob V
SAMPLE CODE:
MonthNumber = 1
For Counter1 = 1 To 12
db.Execute "UPDATE [" & TableName & "] SET [Month" & MonthNumber &
"] = MonthlyDepr(" & MonthNumber &
",nz([Depr-Books]),nz([Date]),nz([Life-Books]),nz([Date
Sold]),nz([Method-Books]),nz([Cost-Books]),nz([Cur Sec 179-Books]),nz([Prior
Depr-Books]),nz([% Bus Usage]),nz([Bonus-Books]));"
MonthNumber = MonthNumber + 1
If MonthNumber > 12 Then
MonthNumber = 1
intUpdateMeter = acbUpdateMeter(62 + Counter1 + 1)
End If
DoCmd.RepaintObject acForm, "ProgressMeter"
Next Counter1
I am experiencing very slow performance of my 2002 Access application when I
install the backend database on the network server and the frontend database
on my local computer. Upon opening the frontend database I have a auto exec
macro that links the tables in the backend database to the front-end
database. I am using DAO to connect to the backend database file.
The slow performance occurs when I execute a number of UPDATE statements
that update tables in the backend database file. I have included a sample of
one of the UPDATE statements that slows down the performance of my
application. The variable MonthlyDepr is a fairly complex subroutine that
calculates the current month depreciation and enters it in columns 1 through
12 of the backend database table. The below UPDATE statement is just one of
several such statements that I have.
When I run the same UPDATE statements on a workstation where the frontend
and backend database files are in the same folder on the local computer, the
subroutine's performance is fairly fast. It's just when the backend database
is located on the external server that the performance slows to a snail's
pace.
Is there something that I am doing incorrectly? Or, should I be using a
different statement to do the update. Should I use ADO to make the
connection to the backend database? Will that make the performance quicker?
Thanks for your help.
Bob V
SAMPLE CODE:
MonthNumber = 1
For Counter1 = 1 To 12
db.Execute "UPDATE [" & TableName & "] SET [Month" & MonthNumber &
"] = MonthlyDepr(" & MonthNumber &
",nz([Depr-Books]),nz([Date]),nz([Life-Books]),nz([Date
Sold]),nz([Method-Books]),nz([Cost-Books]),nz([Cur Sec 179-Books]),nz([Prior
Depr-Books]),nz([% Bus Usage]),nz([Bonus-Books]));"
MonthNumber = MonthNumber + 1
If MonthNumber > 12 Then
MonthNumber = 1
intUpdateMeter = acbUpdateMeter(62 + Counter1 + 1)
End If
DoCmd.RepaintObject acForm, "ProgressMeter"
Next Counter1