Split database continued

[Guest]

Some time ago I posted a question about a database not working properly when
it was split, and Douglas J. Steele explained that it was because dbOpenTable
does not work in a split database, and he very kindly supplied revised code.
I had problems with getting the code working which I have only now had time
to sort out. The split database is far more stable across my network but
there are still two problems.

1. Some simple commands which are surprisingly slow are even slower in the
split database. For example this command button takes two minutes to run:
DoCmd.OpenReport "Enquiries by Enquiry Number", acViewPreview.

Command button:
DoCmd.OpenReport "All Enquiries by Type", acViewPreview
DoCmd.RunCommand acCmdZoom100
is so slow unsplit (20 minutes?) that I have not even tried it yet in the
split database.

2. There is still one live function, to cancel an invoice run, which does
not work as it uses dbOpenTable

Sub ReverseInvoicing()
Dim Invs As Recordset
Dim NewIN As Long

DoCmd.SetWarnings False
DoCmd.Hourglass True

Set Invs = CurrentDb().OpenRecordset("Invoices", dbOpenTable)
Invs.MoveFirst
Do While Invs.EOF = False
If Invs![Recent?] = True And Invs![One Off?] = False Then
DoCmd.RunSQL "UPDATE Enquiries SET [Invoice Number] = 0 WHERE "
& _
"([Invoice Number] = " & Invs![Invoice Number] & ");", False
End If
Invs.Move 1
Loop
DoCmd.RunSQL "UPDATE Invoices " & "SET [Processed?] = No, [Recent?] = No
" & _
"WHERE ([Recent?] = Yes AND [One Off?] = Yes);", False
DoCmd.RunSQL "DELETE * FROM [Invoices] WHERE [Recent?] = Yes;", False
Call RecalculateINSeed

DoCmd.SetWarnings True
DoCmd.Hourglass False
End Sub

Can anyone help on these points?

Many thanks
Dudley

 

[Guest]

To analyze your report problem, it would be necessary to know the report's
record source and if it is a query, the SQL of the query. It would also be
necessary to review all the code including expressions in any control source
properties in the report. How you structure these things can make a dramatic
difference in the performance of a report. I have found it much faster if
you leave the report's row source as plain as possible. If you have any
Domain Aggragate functions, put them in the report.

As to your other problem, it is not necessary to use the dbOpenTable option.
Additionally, it is helpful to specify DAO or ADO when establish database
and recordset object variables.

Here is a rewrite of your sub. Notice I use the Currentdb.Excecute method
for the action queries. The Execute is much faster than RunSQL or any other
method. It does not go through the Access User Interface, so it doesn't need
the Set Warnings. Bypassing the AUI is part of why it is faster. It goes
directly to Jet.

Sub ReverseInvoicing()
Dim dbf As DAO.Database
Dim Invs As DAO.Recordset
Dim NewIN As Long

DoCmd.Hourglass True

Set dbf = CurrentDB
Set Invs = dbf.OpenRecordset("Invoices", dbOpenDynaset)

With Invs
If .Recordcount = 0 Then
MsgBox "No Records in Invoices Table"
Else
.MoveFirst
.MoveFirst
Do While Not .EOF
If ![Recent?] = True And ![One Off?] = False Then
dbf.Execute "UPDATE Enquiries SET [Invoice Number] = 0
WHERE " _
& "([Invoice Number] = " & Invs![Invoice Number] &
");", _
dbFailOnError
End If
.MoveNext
Loop
.Close
End With

dbf.Execute "UPDATE Invoices " & "SET [Processed?] = No, _
[Recent?] = No WHERE ([Recent?] = Yes AND [One Off?] = Yes);", _
dbFailOnError

dbf.Execute "DELETE * FROM [Invoices] WHERE [Recent?] = Yes;", _
dbFailOnError

Call RecalculateINSeed

End If

DoCmd.Hourglass True

End Sub

Let me know if this helps.
--
Dave Hargis, Microsoft Access MVP

Some time ago I posted a question about a database not working properly when
it was split, and Douglas J. Steele explained that it was because dbOpenTable
does not work in a split database, and he very kindly supplied revised code.
I had problems with getting the code working which I have only now had time
to sort out. The split database is far more stable across my network but
there are still two problems.

1. Some simple commands which are surprisingly slow are even slower in the
split database. For example this command button takes two minutes to run:
DoCmd.OpenReport "Enquiries by Enquiry Number", acViewPreview.

Command button:
DoCmd.OpenReport "All Enquiries by Type", acViewPreview
DoCmd.RunCommand acCmdZoom100
is so slow unsplit (20 minutes?) that I have not even tried it yet in the
split database.

2. There is still one live function, to cancel an invoice run, which does
not work as it uses dbOpenTable

Sub ReverseInvoicing()
Dim Invs As Recordset
Dim NewIN As Long

DoCmd.SetWarnings False
DoCmd.Hourglass True

Set Invs = CurrentDb().OpenRecordset("Invoices", dbOpenTable)
Invs.MoveFirst
Do While Invs.EOF = False
If Invs![Recent?] = True And Invs![One Off?] = False Then
DoCmd.RunSQL "UPDATE Enquiries SET [Invoice Number] = 0 WHERE "
& _
"([Invoice Number] = " & Invs![Invoice Number] & ");", False
End If
Invs.Move 1
Loop
DoCmd.RunSQL "UPDATE Invoices " & "SET [Processed?] = No, [Recent?] = No
" & _
"WHERE ([Recent?] = Yes AND [One Off?] = Yes);", False
DoCmd.RunSQL "DELETE * FROM [Invoices] WHERE [Recent?] = Yes;", False
Call RecalculateINSeed

DoCmd.SetWarnings True
DoCmd.Hourglass False
End Sub

Can anyone help on these points?

Many thanks
Dudley

 

[Guest]

Thanks very much for your help. When I run this I get a message:

WHERE " _
& "([Invoice Number] = " & Invs![Invoice Number] &

Compile error:
Syntax error

Can you advise please.

Thanks
Dudley

To analyze your report problem, it would be necessary to know the report's
record source and if it is a query, the SQL of the query. It would also be
necessary to review all the code including expressions in any control source
properties in the report. How you structure these things can make a dramatic
difference in the performance of a report. I have found it much faster if
you leave the report's row source as plain as possible. If you have any
Domain Aggragate functions, put them in the report.

As to your other problem, it is not necessary to use the dbOpenTable option.
Additionally, it is helpful to specify DAO or ADO when establish database
and recordset object variables.

Here is a rewrite of your sub. Notice I use the Currentdb.Excecute method
for the action queries. The Execute is much faster than RunSQL or any other
method. It does not go through the Access User Interface, so it doesn't need
the Set Warnings. Bypassing the AUI is part of why it is faster. It goes
directly to Jet.

Sub ReverseInvoicing()
Dim dbf As DAO.Database
Dim Invs As DAO.Recordset
Dim NewIN As Long

DoCmd.Hourglass True

Set dbf = CurrentDB
Set Invs = dbf.OpenRecordset("Invoices", dbOpenDynaset)

With Invs
If .Recordcount = 0 Then
MsgBox "No Records in Invoices Table"
Else
.MoveFirst
.MoveFirst
Do While Not .EOF
If ![Recent?] = True And ![One Off?] = False Then
dbf.Execute "UPDATE Enquiries SET [Invoice Number] = 0
WHERE " _
& "([Invoice Number] = " & Invs![Invoice Number] &
");", _
dbFailOnError
End If
.MoveNext
Loop
.Close
End With

dbf.Execute "UPDATE Invoices " & "SET [Processed?] = No, _
[Recent?] = No WHERE ([Recent?] = Yes AND [One Off?] = Yes);", _
dbFailOnError

dbf.Execute "DELETE * FROM [Invoices] WHERE [Recent?] = Yes;", _
dbFailOnError

Call RecalculateINSeed

End If

DoCmd.Hourglass True

End Sub

Let me know if this helps.
--
Dave Hargis, Microsoft Access MVP

Some time ago I posted a question about a database not working properly when
it was split, and Douglas J. Steele explained that it was because dbOpenTable
does not work in a split database, and he very kindly supplied revised code.
I had problems with getting the code working which I have only now had time
to sort out. The split database is far more stable across my network but
there are still two problems.

1. Some simple commands which are surprisingly slow are even slower in the
split database. For example this command button takes two minutes to run:
DoCmd.OpenReport "Enquiries by Enquiry Number", acViewPreview.

Command button:
DoCmd.OpenReport "All Enquiries by Type", acViewPreview
DoCmd.RunCommand acCmdZoom100
is so slow unsplit (20 minutes?) that I have not even tried it yet in the
split database.

2. There is still one live function, to cancel an invoice run, which does
not work as it uses dbOpenTable

Sub ReverseInvoicing()
Dim Invs As Recordset
Dim NewIN As Long

DoCmd.SetWarnings False
DoCmd.Hourglass True

Set Invs = CurrentDb().OpenRecordset("Invoices", dbOpenTable)
Invs.MoveFirst
Do While Invs.EOF = False
If Invs![Recent?] = True And Invs![One Off?] = False Then
DoCmd.RunSQL "UPDATE Enquiries SET [Invoice Number] = 0 WHERE "
& _
"([Invoice Number] = " & Invs![Invoice Number] & ");", False
End If
Invs.Move 1
Loop
DoCmd.RunSQL "UPDATE Invoices " & "SET [Processed?] = No, [Recent?] = No
" & _
"WHERE ([Recent?] = Yes AND [One Off?] = Yes);", False
DoCmd.RunSQL "DELETE * FROM [Invoices] WHERE [Recent?] = Yes;", False
Call RecalculateINSeed

DoCmd.SetWarnings True
DoCmd.Hourglass False
End Sub

Can anyone help on these points?

Many thanks
Dudley

 

[Douglas J. Steele]

You may be the victim of wordwrap in the reply.

WHERE " _

was supposed to be on the same line as

dbf.Execute "UPDATE Enquiries SET [Invoice Number] = 0

(with a space between the 0 and the word WHERE)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks very much for your help. When I run this I get a message:

WHERE " _
& "([Invoice Number] = " & Invs![Invoice Number] &

Compile error:
Syntax error

Can you advise please.

Thanks
Dudley

To analyze your report problem, it would be necessary to know the
report's
record source and if it is a query, the SQL of the query. It would also
be
necessary to review all the code including expressions in any control
source
properties in the report. How you structure these things can make a
dramatic
difference in the performance of a report. I have found it much faster
if
you leave the report's row source as plain as possible. If you have any
Domain Aggragate functions, put them in the report.

As to your other problem, it is not necessary to use the dbOpenTable
option.
Additionally, it is helpful to specify DAO or ADO when establish
database
and recordset object variables.

Here is a rewrite of your sub. Notice I use the Currentdb.Excecute
method
for the action queries. The Execute is much faster than RunSQL or any
other
method. It does not go through the Access User Interface, so it doesn't
need
the Set Warnings. Bypassing the AUI is part of why it is faster. It goes
directly to Jet.

Sub ReverseInvoicing()
Dim dbf As DAO.Database
Dim Invs As DAO.Recordset
Dim NewIN As Long

DoCmd.Hourglass True

Set dbf = CurrentDB
Set Invs = dbf.OpenRecordset("Invoices", dbOpenDynaset)

With Invs
If .Recordcount = 0 Then
MsgBox "No Records in Invoices Table"
Else
.MoveFirst
.MoveFirst
Do While Not .EOF
If ![Recent?] = True And ![One Off?] = False Then
dbf.Execute "UPDATE Enquiries SET [Invoice Number] =
0
WHERE " _
& "([Invoice Number] = " & Invs![Invoice Number]
&
");", _
dbFailOnError
End If
.MoveNext
Loop
.Close
End With

dbf.Execute "UPDATE Invoices " & "SET [Processed?] = No, _
[Recent?] = No WHERE ([Recent?] = Yes AND [One Off?] =
Yes);", _
dbFailOnError

dbf.Execute "DELETE * FROM [Invoices] WHERE [Recent?] = Yes;", _
dbFailOnError

Call RecalculateINSeed

End If

DoCmd.Hourglass True

End Sub

Let me know if this helps.
--
Dave Hargis, Microsoft Access MVP

Some time ago I posted a question about a database not working properly
when
it was split, and Douglas J. Steele explained that it was because
dbOpenTable
does not work in a split database, and he very kindly supplied revised
code.
I had problems with getting the code working which I have only now had
time
to sort out. The split database is far more stable across my network
but
there are still two problems.

1. Some simple commands which are surprisingly slow are even slower in
the
split database. For example this command button takes two minutes to
run:
DoCmd.OpenReport "Enquiries by Enquiry Number", acViewPreview.

Command button:
DoCmd.OpenReport "All Enquiries by Type", acViewPreview
DoCmd.RunCommand acCmdZoom100
is so slow unsplit (20 minutes?) that I have not even tried it yet in
the
split database.

2. There is still one live function, to cancel an invoice run, which
does
not work as it uses dbOpenTable

Sub ReverseInvoicing()
Dim Invs As Recordset
Dim NewIN As Long

DoCmd.SetWarnings False
DoCmd.Hourglass True

Set Invs = CurrentDb().OpenRecordset("Invoices", dbOpenTable)
Invs.MoveFirst
Do While Invs.EOF = False
If Invs![Recent?] = True And Invs![One Off?] = False Then
DoCmd.RunSQL "UPDATE Enquiries SET [Invoice Number] = 0
WHERE "
& _
"([Invoice Number] = " & Invs![Invoice Number] & ");",
False
End If
Invs.Move 1
Loop
DoCmd.RunSQL "UPDATE Invoices " & "SET [Processed?] = No, [Recent?]
= No
" & _
"WHERE ([Recent?] = Yes AND [One Off?] = Yes);", False
DoCmd.RunSQL "DELETE * FROM [Invoices] WHERE [Recent?] = Yes;",
False
Call RecalculateINSeed

DoCmd.SetWarnings True
DoCmd.Hourglass False
End Sub

Can anyone help on these points?

Many thanks
Dudley

 

[Guest]

Actually, you are the victim of my poor typing
& "([Invoice Number] = " & Invs![Invoice Number]
Should be
& "([Invoice Number] = " & ![Invoice Number]

It is inside a With/ End With, so we are telling it
Invs!Invs![Invoice Number]
Which does not exist.
--
Dave Hargis, Microsoft Access MVP

Thanks very much for your help. When I run this I get a message:

WHERE " _
& "([Invoice Number] = " & Invs![Invoice Number] &

Compile error:
Syntax error

Can you advise please.

Thanks
Dudley

To analyze your report problem, it would be necessary to know the report's
record source and if it is a query, the SQL of the query. It would also be
necessary to review all the code including expressions in any control source
properties in the report. How you structure these things can make a dramatic
difference in the performance of a report. I have found it much faster if
you leave the report's row source as plain as possible. If you have any
Domain Aggragate functions, put them in the report.

As to your other problem, it is not necessary to use the dbOpenTable option.
Additionally, it is helpful to specify DAO or ADO when establish database
and recordset object variables.

Here is a rewrite of your sub. Notice I use the Currentdb.Excecute method
for the action queries. The Execute is much faster than RunSQL or any other
method. It does not go through the Access User Interface, so it doesn't need
the Set Warnings. Bypassing the AUI is part of why it is faster. It goes
directly to Jet.

Sub ReverseInvoicing()
Dim dbf As DAO.Database
Dim Invs As DAO.Recordset
Dim NewIN As Long

DoCmd.Hourglass True

Set dbf = CurrentDB
Set Invs = dbf.OpenRecordset("Invoices", dbOpenDynaset)

With Invs
If .Recordcount = 0 Then
MsgBox "No Records in Invoices Table"
Else
.MoveFirst
.MoveFirst
Do While Not .EOF
If ![Recent?] = True And ![One Off?] = False Then
dbf.Execute "UPDATE Enquiries SET [Invoice Number] = 0
WHERE " _
& "([Invoice Number] = " & Invs![Invoice Number] &
");", _
dbFailOnError
End If
.MoveNext
Loop
.Close
End With

dbf.Execute "UPDATE Invoices " & "SET [Processed?] = No, _
[Recent?] = No WHERE ([Recent?] = Yes AND [One Off?] = Yes);", _
dbFailOnError

dbf.Execute "DELETE * FROM [Invoices] WHERE [Recent?] = Yes;", _
dbFailOnError

Call RecalculateINSeed

End If

DoCmd.Hourglass True

End Sub

Let me know if this helps.
--
Dave Hargis, Microsoft Access MVP

Some time ago I posted a question about a database not working properly when
it was split, and Douglas J. Steele explained that it was because dbOpenTable
does not work in a split database, and he very kindly supplied revised code.
I had problems with getting the code working which I have only now had time
to sort out. The split database is far more stable across my network but
there are still two problems.

1. Some simple commands which are surprisingly slow are even slower in the
split database. For example this command button takes two minutes to run:
DoCmd.OpenReport "Enquiries by Enquiry Number", acViewPreview.

Command button:
DoCmd.OpenReport "All Enquiries by Type", acViewPreview
DoCmd.RunCommand acCmdZoom100
is so slow unsplit (20 minutes?) that I have not even tried it yet in the
split database.

2. There is still one live function, to cancel an invoice run, which does
not work as it uses dbOpenTable

Sub ReverseInvoicing()
Dim Invs As Recordset
Dim NewIN As Long

DoCmd.SetWarnings False
DoCmd.Hourglass True

Set Invs = CurrentDb().OpenRecordset("Invoices", dbOpenTable)
Invs.MoveFirst
Do While Invs.EOF = False
If Invs![Recent?] = True And Invs![One Off?] = False Then
DoCmd.RunSQL "UPDATE Enquiries SET [Invoice Number] = 0 WHERE "
& _
"([Invoice Number] = " & Invs![Invoice Number] & ");", False
End If
Invs.Move 1
Loop
DoCmd.RunSQL "UPDATE Invoices " & "SET [Processed?] = No, [Recent?] = No
" & _
"WHERE ([Recent?] = Yes AND [One Off?] = Yes);", False
DoCmd.RunSQL "DELETE * FROM [Invoices] WHERE [Recent?] = Yes;", False
Call RecalculateINSeed

DoCmd.SetWarnings True
DoCmd.Hourglass False
End Sub

Can anyone help on these points?

Many thanks
Dudley

 

[Douglas J. Steele]

I'm not sure about that, Dave.

Invs![InvoiceNumber] should essentially override the With/End With
construct. It shouldn't result in the equivalent of Invs!Invs![Invoice
Number]. The With only applies when you shortcut using . or !.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Actually, you are the victim of my poor typing
& "([Invoice Number] = " & Invs![Invoice Number]
Should be
& "([Invoice Number] = " & ![Invoice Number]

It is inside a With/ End With, so we are telling it
Invs!Invs![Invoice Number]
Which does not exist.
--
Dave Hargis, Microsoft Access MVP

Thanks very much for your help. When I run this I get a message:

WHERE " _
& "([Invoice Number] = " & Invs![Invoice Number] &

Compile error:
Syntax error

Can you advise please.

Thanks
Dudley

To analyze your report problem, it would be necessary to know the
report's
record source and if it is a query, the SQL of the query. It would
also be
necessary to review all the code including expressions in any control
source
properties in the report. How you structure these things can make a
dramatic
difference in the performance of a report. I have found it much faster
if
you leave the report's row source as plain as possible. If you have
any
Domain Aggragate functions, put them in the report.

As to your other problem, it is not necessary to use the dbOpenTable
option.
Additionally, it is helpful to specify DAO or ADO when establish
database
and recordset object variables.

Here is a rewrite of your sub. Notice I use the Currentdb.Excecute
method
for the action queries. The Execute is much faster than RunSQL or any
other
method. It does not go through the Access User Interface, so it
doesn't need
the Set Warnings. Bypassing the AUI is part of why it is faster. It
goes
directly to Jet.

Sub ReverseInvoicing()
Dim dbf As DAO.Database
Dim Invs As DAO.Recordset
Dim NewIN As Long

DoCmd.Hourglass True

Set dbf = CurrentDB
Set Invs = dbf.OpenRecordset("Invoices", dbOpenDynaset)

With Invs
If .Recordcount = 0 Then
MsgBox "No Records in Invoices Table"
Else
.MoveFirst
.MoveFirst
Do While Not .EOF
If ![Recent?] = True And ![One Off?] = False Then
dbf.Execute "UPDATE Enquiries SET [Invoice Number]
= 0
WHERE " _
& "([Invoice Number] = " & Invs![Invoice
Number] &
");", _
dbFailOnError
End If
.MoveNext
Loop
.Close
End With

dbf.Execute "UPDATE Invoices " & "SET [Processed?] = No, _
[Recent?] = No WHERE ([Recent?] = Yes AND [One Off?] =
Yes);", _
dbFailOnError

dbf.Execute "DELETE * FROM [Invoices] WHERE [Recent?] = Yes;",
_
dbFailOnError

Call RecalculateINSeed

End If

DoCmd.Hourglass True

End Sub

Let me know if this helps.
--
Dave Hargis, Microsoft Access MVP


:

Some time ago I posted a question about a database not working
properly when
it was split, and Douglas J. Steele explained that it was because
dbOpenTable
does not work in a split database, and he very kindly supplied
revised code.
I had problems with getting the code working which I have only now
had time
to sort out. The split database is far more stable across my network
but
there are still two problems.

1. Some simple commands which are surprisingly slow are even slower
in the
split database. For example this command button takes two minutes to
run:
DoCmd.OpenReport "Enquiries by Enquiry Number", acViewPreview.

Command button:
DoCmd.OpenReport "All Enquiries by Type", acViewPreview
DoCmd.RunCommand acCmdZoom100
is so slow unsplit (20 minutes?) that I have not even tried it yet in
the
split database.

2. There is still one live function, to cancel an invoice run, which
does
not work as it uses dbOpenTable

Sub ReverseInvoicing()
Dim Invs As Recordset
Dim NewIN As Long

DoCmd.SetWarnings False
DoCmd.Hourglass True

Set Invs = CurrentDb().OpenRecordset("Invoices", dbOpenTable)
Invs.MoveFirst
Do While Invs.EOF = False
If Invs![Recent?] = True And Invs![One Off?] = False Then
DoCmd.RunSQL "UPDATE Enquiries SET [Invoice Number] = 0
WHERE "
& _
"([Invoice Number] = " & Invs![Invoice Number] &
");", False
End If
Invs.Move 1
Loop
DoCmd.RunSQL "UPDATE Invoices " & "SET [Processed?] = No,
[Recent?] = No
" & _
"WHERE ([Recent?] = Yes AND [One Off?] = Yes);", False
DoCmd.RunSQL "DELETE * FROM [Invoices] WHERE [Recent?] = Yes;",
False
Call RecalculateINSeed

DoCmd.SetWarnings True
DoCmd.Hourglass False
End Sub

Can anyone help on these points?

Many thanks
Dudley

 

[Guest]

Hmmmm, I haven't tested that. I will take you at your word, but I will have
a look. Seems I remember throwing an object error once when doing that.
--
Dave Hargis, Microsoft Access MVP

I'm not sure about that, Dave.

Invs![InvoiceNumber] should essentially override the With/End With
construct. It shouldn't result in the equivalent of Invs!Invs![Invoice
Number]. The With only applies when you shortcut using . or !.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Actually, you are the victim of my poor typing
& "([Invoice Number] = " & Invs![Invoice Number]
Should be
& "([Invoice Number] = " & ![Invoice Number]

It is inside a With/ End With, so we are telling it
Invs!Invs![Invoice Number]
Which does not exist.
--
Dave Hargis, Microsoft Access MVP

Thanks very much for your help. When I run this I get a message:

WHERE " _
& "([Invoice Number] = " & Invs![Invoice Number] &

Compile error:
Syntax error

Can you advise please.

Thanks
Dudley

:

To analyze your report problem, it would be necessary to know the
report's
record source and if it is a query, the SQL of the query. It would
also be
necessary to review all the code including expressions in any control
source
properties in the report. How you structure these things can make a
dramatic
difference in the performance of a report. I have found it much faster
if
you leave the report's row source as plain as possible. If you have
any
Domain Aggragate functions, put them in the report.

As to your other problem, it is not necessary to use the dbOpenTable
option.
Additionally, it is helpful to specify DAO or ADO when establish
database
and recordset object variables.

Here is a rewrite of your sub. Notice I use the Currentdb.Excecute
method
for the action queries. The Execute is much faster than RunSQL or any
other
method. It does not go through the Access User Interface, so it
doesn't need
the Set Warnings. Bypassing the AUI is part of why it is faster. It
goes
directly to Jet.

Sub ReverseInvoicing()
Dim dbf As DAO.Database
Dim Invs As DAO.Recordset
Dim NewIN As Long

DoCmd.Hourglass True

Set dbf = CurrentDB
Set Invs = dbf.OpenRecordset("Invoices", dbOpenDynaset)

With Invs
If .Recordcount = 0 Then
MsgBox "No Records in Invoices Table"
Else
.MoveFirst
.MoveFirst
Do While Not .EOF
If ![Recent?] = True And ![One Off?] = False Then
dbf.Execute "UPDATE Enquiries SET [Invoice Number]
= 0
WHERE " _
& "([Invoice Number] = " & Invs![Invoice
Number] &
");", _
dbFailOnError
End If
.MoveNext
Loop
.Close
End With

dbf.Execute "UPDATE Invoices " & "SET [Processed?] = No, _
[Recent?] = No WHERE ([Recent?] = Yes AND [One Off?] =
Yes);", _
dbFailOnError

dbf.Execute "DELETE * FROM [Invoices] WHERE [Recent?] = Yes;",
_
dbFailOnError

Call RecalculateINSeed

End If

DoCmd.Hourglass True

End Sub

Let me know if this helps.
--
Dave Hargis, Microsoft Access MVP


:

Some time ago I posted a question about a database not working
properly when
it was split, and Douglas J. Steele explained that it was because
dbOpenTable
does not work in a split database, and he very kindly supplied
revised code.
I had problems with getting the code working which I have only now
had time
to sort out. The split database is far more stable across my network
but
there are still two problems.

1. Some simple commands which are surprisingly slow are even slower
in the
split database. For example this command button takes two minutes to
run:
DoCmd.OpenReport "Enquiries by Enquiry Number", acViewPreview.

Command button:
DoCmd.OpenReport "All Enquiries by Type", acViewPreview
DoCmd.RunCommand acCmdZoom100
is so slow unsplit (20 minutes?) that I have not even tried it yet in
the
split database.

2. There is still one live function, to cancel an invoice run, which
does
not work as it uses dbOpenTable

Sub ReverseInvoicing()
Dim Invs As Recordset
Dim NewIN As Long

DoCmd.SetWarnings False
DoCmd.Hourglass True

Set Invs = CurrentDb().OpenRecordset("Invoices", dbOpenTable)
Invs.MoveFirst
Do While Invs.EOF = False
If Invs![Recent?] = True And Invs![One Off?] = False Then
DoCmd.RunSQL "UPDATE Enquiries SET [Invoice Number] = 0
WHERE "
& _
"([Invoice Number] = " & Invs![Invoice Number] &
");", False
End If
Invs.Move 1
Loop
DoCmd.RunSQL "UPDATE Invoices " & "SET [Processed?] = No,
[Recent?] = No
" & _
"WHERE ([Recent?] = Yes AND [One Off?] = Yes);", False
DoCmd.RunSQL "DELETE * FROM [Invoices] WHERE [Recent?] = Yes;",
False
Call RecalculateINSeed

DoCmd.SetWarnings True
DoCmd.Hourglass False
End Sub

Can anyone help on these points?

Many thanks
Dudley

 

[Guest]

Thanks very much to Dave and Douglas for your advice. I am puzzled what is
happening. When I paste, the line:

dbf.Execute "UPDATE Enquiries SET [Invoice Number] = 0

I get dbf.Execute "UPDATE Enquiries SET [Invoice Number] = 0"

with " added at the end. If I delete " and test then I get the same error
message and the " has come back.

If I merge the lines as

dbf.Execute "UPDATE Enquiries SET [Invoice Number] = 0 WHERE " _

the " does not come back but I still get the error message, as I do if I
delete Invs in the next line.

Also the 4 lines from WHERE" _ TO dbFaillOn Error, and the 3 lines after End
With, dbf.Execute... to dbFaillOn Error, are in red.

Thanks
Dudley Miles

Hmmmm, I haven't tested that. I will take you at your word, but I will have
a look. Seems I remember throwing an object error once when doing that.
--
Dave Hargis, Microsoft Access MVP

I'm not sure about that, Dave.

Invs![InvoiceNumber] should essentially override the With/End With
construct. It shouldn't result in the equivalent of Invs!Invs![Invoice
Number]. The With only applies when you shortcut using . or !.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Actually, you are the victim of my poor typing
& "([Invoice Number] = " & Invs![Invoice Number]
Should be
& "([Invoice Number] = " & ![Invoice Number]

It is inside a With/ End With, so we are telling it
Invs!Invs![Invoice Number]
Which does not exist.
--
Dave Hargis, Microsoft Access MVP


:

Thanks very much for your help. When I run this I get a message:

WHERE " _
& "([Invoice Number] = " & Invs![Invoice Number] &

Compile error:
Syntax error

Can you advise please.

Thanks
Dudley

:

To analyze your report problem, it would be necessary to know the
report's
record source and if it is a query, the SQL of the query. It would
also be
necessary to review all the code including expressions in any control
source
properties in the report. How you structure these things can make a
dramatic
difference in the performance of a report. I have found it much faster
if
you leave the report's row source as plain as possible. If you have
any
Domain Aggragate functions, put them in the report.

As to your other problem, it is not necessary to use the dbOpenTable
option.
Additionally, it is helpful to specify DAO or ADO when establish
database
and recordset object variables.

Here is a rewrite of your sub. Notice I use the Currentdb.Excecute
method
for the action queries. The Execute is much faster than RunSQL or any
other
method. It does not go through the Access User Interface, so it
doesn't need
the Set Warnings. Bypassing the AUI is part of why it is faster. It
goes
directly to Jet.

Sub ReverseInvoicing()
Dim dbf As DAO.Database
Dim Invs As DAO.Recordset
Dim NewIN As Long

DoCmd.Hourglass True

Set dbf = CurrentDB
Set Invs = dbf.OpenRecordset("Invoices", dbOpenDynaset)

With Invs
If .Recordcount = 0 Then
MsgBox "No Records in Invoices Table"
Else
.MoveFirst
.MoveFirst
Do While Not .EOF
If ![Recent?] = True And ![One Off?] = False Then
dbf.Execute "UPDATE Enquiries SET [Invoice Number]
= 0
WHERE " _
& "([Invoice Number] = " & Invs![Invoice
Number] &
");", _
dbFailOnError
End If
.MoveNext
Loop
.Close
End With

dbf.Execute "UPDATE Invoices " & "SET [Processed?] = No, _
[Recent?] = No WHERE ([Recent?] = Yes AND [One Off?] =
Yes);", _
dbFailOnError

dbf.Execute "DELETE * FROM [Invoices] WHERE [Recent?] = Yes;",
_
dbFailOnError

Call RecalculateINSeed

End If

DoCmd.Hourglass True

End Sub

Let me know if this helps.
--
Dave Hargis, Microsoft Access MVP


:

Some time ago I posted a question about a database not working
properly when
it was split, and Douglas J. Steele explained that it was because
dbOpenTable
does not work in a split database, and he very kindly supplied
revised code.
I had problems with getting the code working which I have only now
had time
to sort out. The split database is far more stable across my network
but
there are still two problems.

1. Some simple commands which are surprisingly slow are even slower
in the
split database. For example this command button takes two minutes to
run:
DoCmd.OpenReport "Enquiries by Enquiry Number", acViewPreview.

Command button:
DoCmd.OpenReport "All Enquiries by Type", acViewPreview
DoCmd.RunCommand acCmdZoom100
is so slow unsplit (20 minutes?) that I have not even tried it yet in
the
split database.

2. There is still one live function, to cancel an invoice run, which
does
not work as it uses dbOpenTable

Sub ReverseInvoicing()
Dim Invs As Recordset
Dim NewIN As Long

DoCmd.SetWarnings False
DoCmd.Hourglass True

Set Invs = CurrentDb().OpenRecordset("Invoices", dbOpenTable)
Invs.MoveFirst
Do While Invs.EOF = False
If Invs![Recent?] = True And Invs![One Off?] = False Then
DoCmd.RunSQL "UPDATE Enquiries SET [Invoice Number] = 0
WHERE "
& _
"([Invoice Number] = " & Invs![Invoice Number] &
");", False
End If
Invs.Move 1
Loop
DoCmd.RunSQL "UPDATE Invoices " & "SET [Processed?] = No,
[Recent?] = No
" & _
"WHERE ([Recent?] = Yes AND [One Off?] = Yes);", False
DoCmd.RunSQL "DELETE * FROM [Invoices] WHERE [Recent?] = Yes;",
False
Call RecalculateINSeed

DoCmd.SetWarnings True
DoCmd.Hourglass False
End Sub

Can anyone help on these points?

Many thanks
Dudley

 

[Douglas J. Steele]

dbf.Execute "UPDATE Enquiries SET [Invoice Number] = 0 " & _
"WHERE ([Invoice Number] = " & Invs![Invoice Number] & ")", _
dbFailOnError

Also, there was a typo. (Dave included a continuation character, but forgot
that it was in the middle of a string)

dbf.Execute "UPDATE Invoices " & "SET [Processed?] = False, " & _
"[Recent?] = False WHERE ([Recent?] = True AND [One Off?] = True)", _
dbFailOnError


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks very much to Dave and Douglas for your advice. I am puzzled what is
happening. When I paste, the line:

dbf.Execute "UPDATE Enquiries SET [Invoice Number] = 0

I get dbf.Execute "UPDATE Enquiries SET [Invoice Number] = 0"

with " added at the end. If I delete " and test then I get the same error
message and the " has come back.

If I merge the lines as

dbf.Execute "UPDATE Enquiries SET [Invoice Number] = 0 WHERE " _

the " does not come back but I still get the error message, as I do if I
delete Invs in the next line.

Also the 4 lines from WHERE" _ TO dbFaillOn Error, and the 3 lines after
End
With, dbf.Execute... to dbFaillOn Error, are in red.

Thanks
Dudley Miles

Hmmmm, I haven't tested that. I will take you at your word, but I will
have
a look. Seems I remember throwing an object error once when doing that.
--
Dave Hargis, Microsoft Access MVP

I'm not sure about that, Dave.

Invs![InvoiceNumber] should essentially override the With/End With
construct. It shouldn't result in the equivalent of Invs!Invs![Invoice
Number]. The With only applies when you shortcut using . or !.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Actually, you are the victim of my poor typing
& "([Invoice Number] = " & Invs![Invoice
Number]
Should be
& "([Invoice Number] = " & ![Invoice Number]

It is inside a With/ End With, so we are telling it
Invs!Invs![Invoice Number]
Which does not exist.
--
Dave Hargis, Microsoft Access MVP


:

Thanks very much for your help. When I run this I get a message:

WHERE " _
& "([Invoice Number] = " & Invs![Invoice Number] &

Compile error:
Syntax error

Can you advise please.

Thanks
Dudley

:

To analyze your report problem, it would be necessary to know the
report's
record source and if it is a query, the SQL of the query. It
would
also be
necessary to review all the code including expressions in any
control
source
properties in the report. How you structure these things can make
a
dramatic
difference in the performance of a report. I have found it much
faster
if
you leave the report's row source as plain as possible. If you
have
any
Domain Aggragate functions, put them in the report.

As to your other problem, it is not necessary to use the
dbOpenTable
option.
Additionally, it is helpful to specify DAO or ADO when establish
database
and recordset object variables.

Here is a rewrite of your sub. Notice I use the
Currentdb.Excecute
method
for the action queries. The Execute is much faster than RunSQL or
any
other
method. It does not go through the Access User Interface, so it
doesn't need
the Set Warnings. Bypassing the AUI is part of why it is faster.
It
goes
directly to Jet.

Sub ReverseInvoicing()
Dim dbf As DAO.Database
Dim Invs As DAO.Recordset
Dim NewIN As Long

DoCmd.Hourglass True

Set dbf = CurrentDB
Set Invs = dbf.OpenRecordset("Invoices", dbOpenDynaset)

With Invs
If .Recordcount = 0 Then
MsgBox "No Records in Invoices Table"
Else
.MoveFirst
.MoveFirst
Do While Not .EOF
If ![Recent?] = True And ![One Off?] = False Then
dbf.Execute "UPDATE Enquiries SET [Invoice
Number]
= 0
WHERE " _
& "([Invoice Number] = " & Invs![Invoice
Number] &
");", _
dbFailOnError
End If
.MoveNext
Loop
.Close
End With

dbf.Execute "UPDATE Invoices " & "SET [Processed?] = No, _
[Recent?] = No WHERE ([Recent?] = Yes AND [One Off?] =
Yes);", _
dbFailOnError

dbf.Execute "DELETE * FROM [Invoices] WHERE [Recent?] =
Yes;",
_
dbFailOnError

Call RecalculateINSeed

End If

DoCmd.Hourglass True

End Sub

Let me know if this helps.
--
Dave Hargis, Microsoft Access MVP


:

Some time ago I posted a question about a database not working
properly when
it was split, and Douglas J. Steele explained that it was
because
dbOpenTable
does not work in a split database, and he very kindly supplied
revised code.
I had problems with getting the code working which I have only
now
had time
to sort out. The split database is far more stable across my
network
but
there are still two problems.

1. Some simple commands which are surprisingly slow are even
slower
in the
split database. For example this command button takes two
minutes to
run:
DoCmd.OpenReport "Enquiries by Enquiry Number", acViewPreview.

Command button:
DoCmd.OpenReport "All Enquiries by Type", acViewPreview
DoCmd.RunCommand acCmdZoom100
is so slow unsplit (20 minutes?) that I have not even tried it
yet in
the
split database.

2. There is still one live function, to cancel an invoice run,
which
does
not work as it uses dbOpenTable

Sub ReverseInvoicing()
Dim Invs As Recordset
Dim NewIN As Long

DoCmd.SetWarnings False
DoCmd.Hourglass True

Set Invs = CurrentDb().OpenRecordset("Invoices",
dbOpenTable)
Invs.MoveFirst
Do While Invs.EOF = False
If Invs![Recent?] = True And Invs![One Off?] = False
Then
DoCmd.RunSQL "UPDATE Enquiries SET [Invoice Number]
= 0
WHERE "
& _
"([Invoice Number] = " & Invs![Invoice Number] &
");", False
End If
Invs.Move 1
Loop
DoCmd.RunSQL "UPDATE Invoices " & "SET [Processed?] = No,
[Recent?] = No
" & _
"WHERE ([Recent?] = Yes AND [One Off?] = Yes);", False
DoCmd.RunSQL "DELETE * FROM [Invoices] WHERE [Recent?] =
Yes;",
False
Call RecalculateINSeed

DoCmd.SetWarnings True
DoCmd.Hourglass False
End Sub

Can anyone help on these points?

Many thanks
Dudley

 

[Guest]

I tested this and it does override the With. Interesting.
--
Dave Hargis, Microsoft Access MVP

I'm not sure about that, Dave.

Invs![InvoiceNumber] should essentially override the With/End With
construct. It shouldn't result in the equivalent of Invs!Invs![Invoice
Number]. The With only applies when you shortcut using . or !.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Actually, you are the victim of my poor typing
& "([Invoice Number] = " & Invs![Invoice Number]
Should be
& "([Invoice Number] = " & ![Invoice Number]

It is inside a With/ End With, so we are telling it
Invs!Invs![Invoice Number]
Which does not exist.
--
Dave Hargis, Microsoft Access MVP

Thanks very much for your help. When I run this I get a message:

WHERE " _
& "([Invoice Number] = " & Invs![Invoice Number] &

Compile error:
Syntax error

Can you advise please.

Thanks
Dudley

:

To analyze your report problem, it would be necessary to know the
report's
record source and if it is a query, the SQL of the query. It would
also be
necessary to review all the code including expressions in any control
source
properties in the report. How you structure these things can make a
dramatic
difference in the performance of a report. I have found it much faster
if
you leave the report's row source as plain as possible. If you have
any
Domain Aggragate functions, put them in the report.

As to your other problem, it is not necessary to use the dbOpenTable
option.
Additionally, it is helpful to specify DAO or ADO when establish
database
and recordset object variables.

Here is a rewrite of your sub. Notice I use the Currentdb.Excecute
method
for the action queries. The Execute is much faster than RunSQL or any
other
method. It does not go through the Access User Interface, so it
doesn't need
the Set Warnings. Bypassing the AUI is part of why it is faster. It
goes
directly to Jet.

Sub ReverseInvoicing()
Dim dbf As DAO.Database
Dim Invs As DAO.Recordset
Dim NewIN As Long

DoCmd.Hourglass True

Set dbf = CurrentDB
Set Invs = dbf.OpenRecordset("Invoices", dbOpenDynaset)

With Invs
If .Recordcount = 0 Then
MsgBox "No Records in Invoices Table"
Else
.MoveFirst
.MoveFirst
Do While Not .EOF
If ![Recent?] = True And ![One Off?] = False Then
dbf.Execute "UPDATE Enquiries SET [Invoice Number]
= 0
WHERE " _
& "([Invoice Number] = " & Invs![Invoice
Number] &
");", _
dbFailOnError
End If
.MoveNext
Loop
.Close
End With

dbf.Execute "UPDATE Invoices " & "SET [Processed?] = No, _
[Recent?] = No WHERE ([Recent?] = Yes AND [One Off?] =
Yes);", _
dbFailOnError

dbf.Execute "DELETE * FROM [Invoices] WHERE [Recent?] = Yes;",
_
dbFailOnError

Call RecalculateINSeed

End If

DoCmd.Hourglass True

End Sub

Let me know if this helps.
--
Dave Hargis, Microsoft Access MVP


:

Some time ago I posted a question about a database not working
properly when
it was split, and Douglas J. Steele explained that it was because
dbOpenTable
does not work in a split database, and he very kindly supplied
revised code.
I had problems with getting the code working which I have only now
had time
to sort out. The split database is far more stable across my network
but
there are still two problems.

1. Some simple commands which are surprisingly slow are even slower
in the
split database. For example this command button takes two minutes to
run:
DoCmd.OpenReport "Enquiries by Enquiry Number", acViewPreview.

Command button:
DoCmd.OpenReport "All Enquiries by Type", acViewPreview
DoCmd.RunCommand acCmdZoom100
is so slow unsplit (20 minutes?) that I have not even tried it yet in
the
split database.

2. There is still one live function, to cancel an invoice run, which
does
not work as it uses dbOpenTable

Sub ReverseInvoicing()
Dim Invs As Recordset
Dim NewIN As Long

DoCmd.SetWarnings False
DoCmd.Hourglass True

Set Invs = CurrentDb().OpenRecordset("Invoices", dbOpenTable)
Invs.MoveFirst
Do While Invs.EOF = False
If Invs![Recent?] = True And Invs![One Off?] = False Then
DoCmd.RunSQL "UPDATE Enquiries SET [Invoice Number] = 0
WHERE "
& _
"([Invoice Number] = " & Invs![Invoice Number] &
");", False
End If
Invs.Move 1
Loop
DoCmd.RunSQL "UPDATE Invoices " & "SET [Processed?] = No,
[Recent?] = No
" & _
"WHERE ([Recent?] = Yes AND [One Off?] = Yes);", False
DoCmd.RunSQL "DELETE * FROM [Invoices] WHERE [Recent?] = Yes;",
False
Call RecalculateINSeed

DoCmd.SetWarnings True
DoCmd.Hourglass False
End Sub

Can anyone help on these points?

Many thanks
Dudley

 

[Douglas J. Steele]

What? You didn't believe me? That's it, I'm never going to talk to you
again! <g>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I tested this and it does override the With. Interesting.
--
Dave Hargis, Microsoft Access MVP

I'm not sure about that, Dave.

Invs![InvoiceNumber] should essentially override the With/End With
construct. It shouldn't result in the equivalent of Invs!Invs![Invoice
Number]. The With only applies when you shortcut using . or !.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Actually, you are the victim of my poor typing
& "([Invoice Number] = " & Invs![Invoice
Number]
Should be
& "([Invoice Number] = " & ![Invoice Number]

It is inside a With/ End With, so we are telling it
Invs!Invs![Invoice Number]
Which does not exist.
--
Dave Hargis, Microsoft Access MVP


:

Thanks very much for your help. When I run this I get a message:

WHERE " _
& "([Invoice Number] = " & Invs![Invoice Number] &

Compile error:
Syntax error

Can you advise please.

Thanks
Dudley

:

To analyze your report problem, it would be necessary to know the
report's
record source and if it is a query, the SQL of the query. It would
also be
necessary to review all the code including expressions in any
control
source
properties in the report. How you structure these things can make a
dramatic
difference in the performance of a report. I have found it much
faster
if
you leave the report's row source as plain as possible. If you have
any
Domain Aggragate functions, put them in the report.

As to your other problem, it is not necessary to use the dbOpenTable
option.
Additionally, it is helpful to specify DAO or ADO when establish
database
and recordset object variables.

Here is a rewrite of your sub. Notice I use the Currentdb.Excecute
method
for the action queries. The Execute is much faster than RunSQL or
any
other
method. It does not go through the Access User Interface, so it
doesn't need
the Set Warnings. Bypassing the AUI is part of why it is faster. It
goes
directly to Jet.

Sub ReverseInvoicing()
Dim dbf As DAO.Database
Dim Invs As DAO.Recordset
Dim NewIN As Long

DoCmd.Hourglass True

Set dbf = CurrentDB
Set Invs = dbf.OpenRecordset("Invoices", dbOpenDynaset)

With Invs
If .Recordcount = 0 Then
MsgBox "No Records in Invoices Table"
Else
.MoveFirst
.MoveFirst
Do While Not .EOF
If ![Recent?] = True And ![One Off?] = False Then
dbf.Execute "UPDATE Enquiries SET [Invoice
Number]
= 0
WHERE " _
& "([Invoice Number] = " & Invs![Invoice
Number] &
");", _
dbFailOnError
End If
.MoveNext
Loop
.Close
End With

dbf.Execute "UPDATE Invoices " & "SET [Processed?] = No, _
[Recent?] = No WHERE ([Recent?] = Yes AND [One Off?] =
Yes);", _
dbFailOnError

dbf.Execute "DELETE * FROM [Invoices] WHERE [Recent?] =
Yes;",
_
dbFailOnError

Call RecalculateINSeed

End If

DoCmd.Hourglass True

End Sub

Let me know if this helps.
--
Dave Hargis, Microsoft Access MVP


:

Some time ago I posted a question about a database not working
properly when
it was split, and Douglas J. Steele explained that it was because
dbOpenTable
does not work in a split database, and he very kindly supplied
revised code.
I had problems with getting the code working which I have only now
had time
to sort out. The split database is far more stable across my
network
but
there are still two problems.

1. Some simple commands which are surprisingly slow are even
slower
in the
split database. For example this command button takes two minutes
to
run:
DoCmd.OpenReport "Enquiries by Enquiry Number", acViewPreview.

Command button:
DoCmd.OpenReport "All Enquiries by Type", acViewPreview
DoCmd.RunCommand acCmdZoom100
is so slow unsplit (20 minutes?) that I have not even tried it yet
in
the
split database.

2. There is still one live function, to cancel an invoice run,
which
does
not work as it uses dbOpenTable

Sub ReverseInvoicing()
Dim Invs As Recordset
Dim NewIN As Long

DoCmd.SetWarnings False
DoCmd.Hourglass True

Set Invs = CurrentDb().OpenRecordset("Invoices", dbOpenTable)
Invs.MoveFirst
Do While Invs.EOF = False
If Invs![Recent?] = True And Invs![One Off?] = False Then
DoCmd.RunSQL "UPDATE Enquiries SET [Invoice Number] =
0
WHERE "
& _
"([Invoice Number] = " & Invs![Invoice Number] &
");", False
End If
Invs.Move 1
Loop
DoCmd.RunSQL "UPDATE Invoices " & "SET [Processed?] = No,
[Recent?] = No
" & _
"WHERE ([Recent?] = Yes AND [One Off?] = Yes);", False
DoCmd.RunSQL "DELETE * FROM [Invoices] WHERE [Recent?] =
Yes;",
False
Call RecalculateINSeed

DoCmd.SetWarnings True
DoCmd.Hourglass False
End Sub

Can anyone help on these points?

Many thanks
Dudley

 

[Guest]

Trust but Verify (Ronald Regan)
--
Dave Hargis, Microsoft Access MVP

What? You didn't believe me? That's it, I'm never going to talk to you
again! <g>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I tested this and it does override the With. Interesting.
--
Dave Hargis, Microsoft Access MVP

I'm not sure about that, Dave.

Invs![InvoiceNumber] should essentially override the With/End With
construct. It shouldn't result in the equivalent of Invs!Invs![Invoice
Number]. The With only applies when you shortcut using . or !.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Actually, you are the victim of my poor typing
& "([Invoice Number] = " & Invs![Invoice
Number]
Should be
& "([Invoice Number] = " & ![Invoice Number]

It is inside a With/ End With, so we are telling it
Invs!Invs![Invoice Number]
Which does not exist.
--
Dave Hargis, Microsoft Access MVP


:

Thanks very much for your help. When I run this I get a message:

WHERE " _
& "([Invoice Number] = " & Invs![Invoice Number] &

Compile error:
Syntax error

Can you advise please.

Thanks
Dudley

:

To analyze your report problem, it would be necessary to know the
report's
record source and if it is a query, the SQL of the query. It would
also be
necessary to review all the code including expressions in any
control
source
properties in the report. How you structure these things can make a
dramatic
difference in the performance of a report. I have found it much
faster
if
you leave the report's row source as plain as possible. If you have
any
Domain Aggragate functions, put them in the report.

As to your other problem, it is not necessary to use the dbOpenTable
option.
Additionally, it is helpful to specify DAO or ADO when establish
database
and recordset object variables.

Here is a rewrite of your sub. Notice I use the Currentdb.Excecute
method
for the action queries. The Execute is much faster than RunSQL or
any
other
method. It does not go through the Access User Interface, so it
doesn't need
the Set Warnings. Bypassing the AUI is part of why it is faster. It
goes
directly to Jet.

Sub ReverseInvoicing()
Dim dbf As DAO.Database
Dim Invs As DAO.Recordset
Dim NewIN As Long

DoCmd.Hourglass True

Set dbf = CurrentDB
Set Invs = dbf.OpenRecordset("Invoices", dbOpenDynaset)

With Invs
If .Recordcount = 0 Then
MsgBox "No Records in Invoices Table"
Else
.MoveFirst
.MoveFirst
Do While Not .EOF
If ![Recent?] = True And ![One Off?] = False Then
dbf.Execute "UPDATE Enquiries SET [Invoice
Number]
= 0
WHERE " _
& "([Invoice Number] = " & Invs![Invoice
Number] &
");", _
dbFailOnError
End If
.MoveNext
Loop
.Close
End With

dbf.Execute "UPDATE Invoices " & "SET [Processed?] = No, _
[Recent?] = No WHERE ([Recent?] = Yes AND [One Off?] =
Yes);", _
dbFailOnError

dbf.Execute "DELETE * FROM [Invoices] WHERE [Recent?] =
Yes;",
_
dbFailOnError

Call RecalculateINSeed

End If

DoCmd.Hourglass True

End Sub

Let me know if this helps.
--
Dave Hargis, Microsoft Access MVP


:

Some time ago I posted a question about a database not working
properly when
it was split, and Douglas J. Steele explained that it was because
dbOpenTable
does not work in a split database, and he very kindly supplied
revised code.
I had problems with getting the code working which I have only now
had time
to sort out. The split database is far more stable across my
network
but
there are still two problems.

1. Some simple commands which are surprisingly slow are even
slower
in the
split database. For example this command button takes two minutes
to
run:
DoCmd.OpenReport "Enquiries by Enquiry Number", acViewPreview.

Command button:
DoCmd.OpenReport "All Enquiries by Type", acViewPreview
DoCmd.RunCommand acCmdZoom100
is so slow unsplit (20 minutes?) that I have not even tried it yet
in
the
split database.

2. There is still one live function, to cancel an invoice run,
which
does
not work as it uses dbOpenTable

Sub ReverseInvoicing()
Dim Invs As Recordset
Dim NewIN As Long

DoCmd.SetWarnings False
DoCmd.Hourglass True

Set Invs = CurrentDb().OpenRecordset("Invoices", dbOpenTable)
Invs.MoveFirst
Do While Invs.EOF = False
If Invs![Recent?] = True And Invs![One Off?] = False Then
DoCmd.RunSQL "UPDATE Enquiries SET [Invoice Number] =
0
WHERE "
& _
"([Invoice Number] = " & Invs![Invoice Number] &
");", False
End If
Invs.Move 1
Loop
DoCmd.RunSQL "UPDATE Invoices " & "SET [Processed?] = No,
[Recent?] = No
" & _
"WHERE ([Recent?] = Yes AND [One Off?] = Yes);", False
DoCmd.RunSQL "DELETE * FROM [Invoices] WHERE [Recent?] =
Yes;",
False
Call RecalculateINSeed

DoCmd.SetWarnings True
DoCmd.Hourglass False
End Sub

Can anyone help on these points?

Many thanks
Dudley

 

[Guest]

Many thanks. It now works fine and instantly.

The code for creating a one off invoice takes 12 seconds with the database
unsplit and 35 seconds split. Is it possible to do anything about this? it is:

Private Sub Create_New_Invoice_Click()
Dim NewIN As Long
Dim ThisDate

On Error GoTo errcust
ThisDate = InputBox("What date should the new invoice be dated with?",
"Date of Invoicing", Date)
If ThisDate = "" Then Exit Sub

DoCmd.SetWarnings False
DoCmd.Hourglass True

NewIN = NewInvoiceNumber()
DoCmd.RunSQL "UPDATE Enquiries " & "SET [Invoice Number] = " & _
NewIN & " WHERE ([Customer Code] = '" & _
Me![Customer Code Field] & "') AND ([Invoice Number] = 0);", False

DoCmd.RunSQL ("INSERT INTO Invoices ([Invoice Number],[Customer
Code],[Creation Date],[One Off?]) " & _
"VALUES (" & NewIN & ",'" & Me![Customer Code Field] & "','" &
ThisDate & "', Yes);")

DoCmd.OpenForm "Invoices", , , "[Invoice Number]=" & NewIN
DoCmd.Hourglass False

errcust:
DoCmd.SetWarnings True
DoCmd.Hourglass False
End Sub

Thanks very much.
Dudley Miles

dbf.Execute "UPDATE Enquiries SET [Invoice Number] = 0 " & _
"WHERE ([Invoice Number] = " & Invs![Invoice Number] & ")", _
dbFailOnError

Also, there was a typo. (Dave included a continuation character, but forgot
that it was in the middle of a string)

dbf.Execute "UPDATE Invoices " & "SET [Processed?] = False, " & _
"[Recent?] = False WHERE ([Recent?] = True AND [One Off?] = True)", _
dbFailOnError


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks very much to Dave and Douglas for your advice. I am puzzled what is
happening. When I paste, the line:

dbf.Execute "UPDATE Enquiries SET [Invoice Number] = 0

I get dbf.Execute "UPDATE Enquiries SET [Invoice Number] = 0"

with " added at the end. If I delete " and test then I get the same error
message and the " has come back.

If I merge the lines as

dbf.Execute "UPDATE Enquiries SET [Invoice Number] = 0 WHERE " _

the " does not come back but I still get the error message, as I do if I
delete Invs in the next line.

Also the 4 lines from WHERE" _ TO dbFaillOn Error, and the 3 lines after
End
With, dbf.Execute... to dbFaillOn Error, are in red.

Thanks
Dudley Miles

Hmmmm, I haven't tested that. I will take you at your word, but I will
have
a look. Seems I remember throwing an object error once when doing that.
--
Dave Hargis, Microsoft Access MVP


:

I'm not sure about that, Dave.

Invs![InvoiceNumber] should essentially override the With/End With
construct. It shouldn't result in the equivalent of Invs!Invs![Invoice
Number]. The With only applies when you shortcut using . or !.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Actually, you are the victim of my poor typing
& "([Invoice Number] = " & Invs![Invoice
Number]
Should be
& "([Invoice Number] = " & ![Invoice Number]

It is inside a With/ End With, so we are telling it
Invs!Invs![Invoice Number]
Which does not exist.
--
Dave Hargis, Microsoft Access MVP


:

Thanks very much for your help. When I run this I get a message:

WHERE " _
& "([Invoice Number] = " & Invs![Invoice Number] &

Compile error:
Syntax error

Can you advise please.

Thanks
Dudley

:

To analyze your report problem, it would be necessary to know the
report's
record source and if it is a query, the SQL of the query. It
would
also be
necessary to review all the code including expressions in any
control
source
properties in the report. How you structure these things can make
a
dramatic
difference in the performance of a report. I have found it much
faster
if
you leave the report's row source as plain as possible. If you
have
any
Domain Aggragate functions, put them in the report.

As to your other problem, it is not necessary to use the
dbOpenTable
option.
Additionally, it is helpful to specify DAO or ADO when establish
database
and recordset object variables.

Here is a rewrite of your sub. Notice I use the
Currentdb.Excecute
method
for the action queries. The Execute is much faster than RunSQL or
any
other
method. It does not go through the Access User Interface, so it
doesn't need
the Set Warnings. Bypassing the AUI is part of why it is faster.
It
goes
directly to Jet.

Sub ReverseInvoicing()
Dim dbf As DAO.Database
Dim Invs As DAO.Recordset
Dim NewIN As Long

DoCmd.Hourglass True

Set dbf = CurrentDB
Set Invs = dbf.OpenRecordset("Invoices", dbOpenDynaset)

With Invs
If .Recordcount = 0 Then
MsgBox "No Records in Invoices Table"
Else
.MoveFirst
.MoveFirst
Do While Not .EOF
If ![Recent?] = True And ![One Off?] = False Then
dbf.Execute "UPDATE Enquiries SET [Invoice
Number]
= 0
WHERE " _
& "([Invoice Number] = " & Invs![Invoice
Number] &
");", _
dbFailOnError
End If
.MoveNext
Loop
.Close
End With

dbf.Execute "UPDATE Invoices " & "SET [Processed?] = No, _
[Recent?] = No WHERE ([Recent?] = Yes AND [One Off?] =
Yes);", _
dbFailOnError

dbf.Execute "DELETE * FROM [Invoices] WHERE [Recent?] =
Yes;",
_
dbFailOnError

Call RecalculateINSeed

End If

DoCmd.Hourglass True

End Sub

Let me know if this helps.
--
Dave Hargis, Microsoft Access MVP


:

Some time ago I posted a question about a database not working
properly when
it was split, and Douglas J. Steele explained that it was
because
dbOpenTable
does not work in a split database, and he very kindly supplied
revised code.
I had problems with getting the code working which I have only
now
had time
to sort out. The split database is far more stable across my
network
but
there are still two problems.

1. Some simple commands which are surprisingly slow are even
slower
in the
split database. For example this command button takes two
minutes to
run:
DoCmd.OpenReport "Enquiries by Enquiry Number", acViewPreview.

Command button:
DoCmd.OpenReport "All Enquiries by Type", acViewPreview
DoCmd.RunCommand acCmdZoom100
is so slow unsplit (20 minutes?) that I have not even tried it
yet in
the
split database.

2. There is still one live function, to cancel an invoice run,
which
does
not work as it uses dbOpenTable

Sub ReverseInvoicing()
Dim Invs As Recordset
Dim NewIN As Long

DoCmd.SetWarnings False
DoCmd.Hourglass True

Set Invs = CurrentDb().OpenRecordset("Invoices",
dbOpenTable)
Invs.MoveFirst
Do While Invs.EOF = False
If Invs![Recent?] = True And Invs![One Off?] = False
Then
DoCmd.RunSQL "UPDATE Enquiries SET [Invoice Number]
= 0
WHERE "
& _
"([Invoice Number] = " & Invs![Invoice Number] &
");", False
End If
Invs.Move 1
Loop
DoCmd.RunSQL "UPDATE Invoices " & "SET [Processed?] = No,
[Recent?] = No
" & _
"WHERE ([Recent?] = Yes AND [One Off?] = Yes);", False
DoCmd.RunSQL "DELETE * FROM [Invoices] WHERE [Recent?] =
Yes;",
False
Call RecalculateINSeed

DoCmd.SetWarnings True
DoCmd.Hourglass False
End Sub

Can anyone help on these points?

Many thanks
Dudley

 

[Guest]

The correct configuration is to have the back end on a file server and a copy
of the front end on each user's computer.
A couple of things that will help network performance:
Give your files names with eight or fewer characters:
Not: XLTPq_Larrys_UltraGreat_and_Amazing_Database_Application_be.mdb
But: XLApp_be.mdb

Keep the backend as close to the Share Root as possible:
Not:\\SomeServer\SomeShare\AnotherFolder\ThirdLevel\ToThe\Absurd\XlApp_be.mdb
But: \\SomeServer\SomeShare\XlApp_be.mdb

If performace still suffers, talk your network admin.

--
Dave Hargis, Microsoft Access MVP

Many thanks. It now works fine and instantly.

The code for creating a one off invoice takes 12 seconds with the database
unsplit and 35 seconds split. Is it possible to do anything about this? it is:

Private Sub Create_New_Invoice_Click()
Dim NewIN As Long
Dim ThisDate

On Error GoTo errcust
ThisDate = InputBox("What date should the new invoice be dated with?",
"Date of Invoicing", Date)
If ThisDate = "" Then Exit Sub

DoCmd.SetWarnings False
DoCmd.Hourglass True

NewIN = NewInvoiceNumber()
DoCmd.RunSQL "UPDATE Enquiries " & "SET [Invoice Number] = " & _
NewIN & " WHERE ([Customer Code] = '" & _
Me![Customer Code Field] & "') AND ([Invoice Number] = 0);", False

DoCmd.RunSQL ("INSERT INTO Invoices ([Invoice Number],[Customer
Code],[Creation Date],[One Off?]) " & _
"VALUES (" & NewIN & ",'" & Me![Customer Code Field] & "','" &
ThisDate & "', Yes);")

DoCmd.OpenForm "Invoices", , , "[Invoice Number]=" & NewIN
DoCmd.Hourglass False

errcust:
DoCmd.SetWarnings True
DoCmd.Hourglass False
End Sub

Thanks very much.
Dudley Miles

dbf.Execute "UPDATE Enquiries SET [Invoice Number] = 0 " & _
"WHERE ([Invoice Number] = " & Invs![Invoice Number] & ")", _
dbFailOnError

Also, there was a typo. (Dave included a continuation character, but forgot
that it was in the middle of a string)

dbf.Execute "UPDATE Invoices " & "SET [Processed?] = False, " & _
"[Recent?] = False WHERE ([Recent?] = True AND [One Off?] = True)", _
dbFailOnError


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks very much to Dave and Douglas for your advice. I am puzzled what is
happening. When I paste, the line:

dbf.Execute "UPDATE Enquiries SET [Invoice Number] = 0

I get dbf.Execute "UPDATE Enquiries SET [Invoice Number] = 0"

with " added at the end. If I delete " and test then I get the same error
message and the " has come back.

If I merge the lines as

dbf.Execute "UPDATE Enquiries SET [Invoice Number] = 0 WHERE " _

the " does not come back but I still get the error message, as I do if I
delete Invs in the next line.

Also the 4 lines from WHERE" _ TO dbFaillOn Error, and the 3 lines after
End
With, dbf.Execute... to dbFaillOn Error, are in red.

Thanks
Dudley Miles


:

Hmmmm, I haven't tested that. I will take you at your word, but I will
have
a look. Seems I remember throwing an object error once when doing that.
--
Dave Hargis, Microsoft Access MVP


:

I'm not sure about that, Dave.

Invs![InvoiceNumber] should essentially override the With/End With
construct. It shouldn't result in the equivalent of Invs!Invs![Invoice
Number]. The With only applies when you shortcut using . or !.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Actually, you are the victim of my poor typing
& "([Invoice Number] = " & Invs![Invoice
Number]
Should be
& "([Invoice Number] = " & ![Invoice Number]

It is inside a With/ End With, so we are telling it
Invs!Invs![Invoice Number]
Which does not exist.
--
Dave Hargis, Microsoft Access MVP


:

Thanks very much for your help. When I run this I get a message:

WHERE " _
& "([Invoice Number] = " & Invs![Invoice Number] &

Compile error:
Syntax error

Can you advise please.

Thanks
Dudley

:

To analyze your report problem, it would be necessary to know the
report's
record source and if it is a query, the SQL of the query. It
would
also be
necessary to review all the code including expressions in any
control
source
properties in the report. How you structure these things can make
a
dramatic
difference in the performance of a report. I have found it much
faster
if
you leave the report's row source as plain as possible. If you
have
any
Domain Aggragate functions, put them in the report.

As to your other problem, it is not necessary to use the
dbOpenTable
option.
Additionally, it is helpful to specify DAO or ADO when establish
database
and recordset object variables.

Here is a rewrite of your sub. Notice I use the
Currentdb.Excecute
method
for the action queries. The Execute is much faster than RunSQL or
any
other
method. It does not go through the Access User Interface, so it
doesn't need
the Set Warnings. Bypassing the AUI is part of why it is faster.
It
goes
directly to Jet.

Sub ReverseInvoicing()
Dim dbf As DAO.Database
Dim Invs As DAO.Recordset
Dim NewIN As Long

DoCmd.Hourglass True

Set dbf = CurrentDB
Set Invs = dbf.OpenRecordset("Invoices", dbOpenDynaset)

With Invs
If .Recordcount = 0 Then
MsgBox "No Records in Invoices Table"
Else
.MoveFirst
.MoveFirst
Do While Not .EOF
If ![Recent?] = True And ![One Off?] = False Then
dbf.Execute "UPDATE Enquiries SET [Invoice
Number]
= 0
WHERE " _
& "([Invoice Number] = " & Invs![Invoice
Number] &
");", _
dbFailOnError
End If
.MoveNext
Loop
.Close
End With

dbf.Execute "UPDATE Invoices " & "SET [Processed?] = No, _
[Recent?] = No WHERE ([Recent?] = Yes AND [One Off?] =
Yes);", _
dbFailOnError

dbf.Execute "DELETE * FROM [Invoices] WHERE [Recent?] =
Yes;",
_
dbFailOnError

Call RecalculateINSeed

End If

DoCmd.Hourglass True

End Sub

Let me know if this helps.
--
Dave Hargis, Microsoft Access MVP


:

Some time ago I posted a question about a database not working
properly when
it was split, and Douglas J. Steele explained that it was
because
dbOpenTable
does not work in a split database, and he very kindly supplied
revised code.
I had problems with getting the code working which I have only
now
had time
to sort out. The split database is far more stable across my
network
but
there are still two problems.

1. Some simple commands which are surprisingly slow are even
slower
in the
split database. For example this command button takes two
minutes to
run:
DoCmd.OpenReport "Enquiries by Enquiry Number", acViewPreview.

Command button:
DoCmd.OpenReport "All Enquiries by Type", acViewPreview
DoCmd.RunCommand acCmdZoom100
is so slow unsplit (20 minutes?) that I have not even tried it
yet in
the
split database.

2. There is still one live function, to cancel an invoice run,
which
does
not work as it uses dbOpenTable

Sub ReverseInvoicing()
Dim Invs As Recordset
Dim NewIN As Long

DoCmd.SetWarnings False
DoCmd.Hourglass True

Set Invs = CurrentDb().OpenRecordset("Invoices",
dbOpenTable)
Invs.MoveFirst
Do While Invs.EOF = False
If Invs![Recent?] = True And Invs![One Off?] = False
Then
DoCmd.RunSQL "UPDATE Enquiries SET [Invoice Number]
= 0
WHERE "
& _
"([Invoice Number] = " & Invs![Invoice Number] &
");", False
End If
Invs.Move 1
Loop
DoCmd.RunSQL "UPDATE Invoices " & "SET [Processed?] = No,
[Recent?] = No
" & _
"WHERE ([Recent?] = Yes AND [One Off?] = Yes);", False

 

[Guest]

I have a peer to peer network and short file names. I have gone live with the
split database which is working fine apart from the slowing down of some
code. I will now carry on with the more robust split database and again thank
you and Doug for all your help.

Dudley

The correct configuration is to have the back end on a file server and a copy
of the front end on each user's computer.
A couple of things that will help network performance:
Give your files names with eight or fewer characters:
Not: XLTPq_Larrys_UltraGreat_and_Amazing_Database_Application_be.mdb
But: XLApp_be.mdb

Keep the backend as close to the Share Root as possible:
Not:\\SomeServer\SomeShare\AnotherFolder\ThirdLevel\ToThe\Absurd\XlApp_be.mdb
But: \\SomeServer\SomeShare\XlApp_be.mdb

If performace still suffers, talk your network admin.

--
Dave Hargis, Microsoft Access MVP

Many thanks. It now works fine and instantly.

The code for creating a one off invoice takes 12 seconds with the database
unsplit and 35 seconds split. Is it possible to do anything about this? it is:

Private Sub Create_New_Invoice_Click()
Dim NewIN As Long
Dim ThisDate

On Error GoTo errcust
ThisDate = InputBox("What date should the new invoice be dated with?",
"Date of Invoicing", Date)
If ThisDate = "" Then Exit Sub

DoCmd.SetWarnings False
DoCmd.Hourglass True

NewIN = NewInvoiceNumber()
DoCmd.RunSQL "UPDATE Enquiries " & "SET [Invoice Number] = " & _
NewIN & " WHERE ([Customer Code] = '" & _
Me![Customer Code Field] & "') AND ([Invoice Number] = 0);", False

DoCmd.RunSQL ("INSERT INTO Invoices ([Invoice Number],[Customer
Code],[Creation Date],[One Off?]) " & _
"VALUES (" & NewIN & ",'" & Me![Customer Code Field] & "','" &
ThisDate & "', Yes);")

DoCmd.OpenForm "Invoices", , , "[Invoice Number]=" & NewIN
DoCmd.Hourglass False

errcust:
DoCmd.SetWarnings True
DoCmd.Hourglass False
End Sub

Thanks very much.
Dudley Miles

dbf.Execute "UPDATE Enquiries SET [Invoice Number] = 0 " & _
"WHERE ([Invoice Number] = " & Invs![Invoice Number] & ")", _
dbFailOnError

Also, there was a typo. (Dave included a continuation character, but forgot
that it was in the middle of a string)

dbf.Execute "UPDATE Invoices " & "SET [Processed?] = False, " & _
"[Recent?] = False WHERE ([Recent?] = True AND [One Off?] = True)", _
dbFailOnError


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks very much to Dave and Douglas for your advice. I am puzzled what is
happening. When I paste, the line:

dbf.Execute "UPDATE Enquiries SET [Invoice Number] = 0

I get dbf.Execute "UPDATE Enquiries SET [Invoice Number] = 0"

with " added at the end. If I delete " and test then I get the same error
message and the " has come back.

If I merge the lines as

dbf.Execute "UPDATE Enquiries SET [Invoice Number] = 0 WHERE " _

the " does not come back but I still get the error message, as I do if I
delete Invs in the next line.

Also the 4 lines from WHERE" _ TO dbFaillOn Error, and the 3 lines after
End
With, dbf.Execute... to dbFaillOn Error, are in red.

Thanks
Dudley Miles


:

Hmmmm, I haven't tested that. I will take you at your word, but I will
have
a look. Seems I remember throwing an object error once when doing that.
--
Dave Hargis, Microsoft Access MVP


:

I'm not sure about that, Dave.

Invs![InvoiceNumber] should essentially override the With/End With
construct. It shouldn't result in the equivalent of Invs!Invs![Invoice
Number]. The With only applies when you shortcut using . or !.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Actually, you are the victim of my poor typing
& "([Invoice Number] = " & Invs![Invoice
Number]
Should be
& "([Invoice Number] = " & ![Invoice Number]

It is inside a With/ End With, so we are telling it
Invs!Invs![Invoice Number]
Which does not exist.
--
Dave Hargis, Microsoft Access MVP


:

Thanks very much for your help. When I run this I get a message:

WHERE " _
& "([Invoice Number] = " & Invs![Invoice Number] &

Compile error:
Syntax error

Can you advise please.

Thanks
Dudley

:

To analyze your report problem, it would be necessary to know the
report's
record source and if it is a query, the SQL of the query. It
would
also be
necessary to review all the code including expressions in any
control
source
properties in the report. How you structure these things can make
a
dramatic
difference in the performance of a report. I have found it much
faster
if
you leave the report's row source as plain as possible. If you
have
any
Domain Aggragate functions, put them in the report.

As to your other problem, it is not necessary to use the
dbOpenTable
option.
Additionally, it is helpful to specify DAO or ADO when establish
database
and recordset object variables.

Here is a rewrite of your sub. Notice I use the
Currentdb.Excecute
method
for the action queries. The Execute is much faster than RunSQL or
any
other
method. It does not go through the Access User Interface, so it
doesn't need
the Set Warnings. Bypassing the AUI is part of why it is faster.
It
goes
directly to Jet.

Sub ReverseInvoicing()
Dim dbf As DAO.Database
Dim Invs As DAO.Recordset
Dim NewIN As Long

DoCmd.Hourglass True

Set dbf = CurrentDB
Set Invs = dbf.OpenRecordset("Invoices", dbOpenDynaset)

With Invs
If .Recordcount = 0 Then
MsgBox "No Records in Invoices Table"
Else
.MoveFirst
.MoveFirst
Do While Not .EOF
If ![Recent?] = True And ![One Off?] = False Then
dbf.Execute "UPDATE Enquiries SET [Invoice
Number]
= 0
WHERE " _
& "([Invoice Number] = " & Invs![Invoice
Number] &
");", _
dbFailOnError
End If
.MoveNext
Loop
.Close
End With

dbf.Execute "UPDATE Invoices " & "SET [Processed?] = No, _
[Recent?] = No WHERE ([Recent?] = Yes AND [One Off?] =
Yes);", _
dbFailOnError

dbf.Execute "DELETE * FROM [Invoices] WHERE [Recent?] =
Yes;",
_
dbFailOnError

Call RecalculateINSeed

End If

DoCmd.Hourglass True

End Sub

Let me know if this helps.
--
Dave Hargis, Microsoft Access MVP


:

Some time ago I posted a question about a database not working
properly when
it was split, and Douglas J. Steele explained that it was
because
dbOpenTable
does not work in a split database, and he very kindly supplied
revised code.
I had problems with getting the code working which I have only
now
had time
to sort out. The split database is far more stable across my
network
but
there are still two problems.

1. Some simple commands which are surprisingly slow are even
slower
in the
split database. For example this command button takes two
minutes to
run:
DoCmd.OpenReport "Enquiries by Enquiry Number", acViewPreview.

Command button:
DoCmd.OpenReport "All Enquiries by Type", acViewPreview
DoCmd.RunCommand acCmdZoom100
is so slow unsplit (20 minutes?) that I have not even tried it
yet in
the
split database.

2. There is still one live function, to cancel an invoice run,
which
does
not work as it uses dbOpenTable

Sub ReverseInvoicing()
Dim Invs As Recordset
Dim NewIN As Long

DoCmd.SetWarnings False
DoCmd.Hourglass True

 

[Guest]

You can expect some degradation on a peer to peer because that computer has
to take care of its user as well as service other database requests. It
would help to have a machine dedicated to the access backend database(s), if
possible.


--
Dave Hargis, Microsoft Access MVP

I have a peer to peer network and short file names. I have gone live with the
split database which is working fine apart from the slowing down of some
code. I will now carry on with the more robust split database and again thank
you and Doug for all your help.

Dudley

The correct configuration is to have the back end on a file server and a copy
of the front end on each user's computer.
A couple of things that will help network performance:
Give your files names with eight or fewer characters:
Not: XLTPq_Larrys_UltraGreat_and_Amazing_Database_Application_be.mdb
But: XLApp_be.mdb

Keep the backend as close to the Share Root as possible:
Not:\\SomeServer\SomeShare\AnotherFolder\ThirdLevel\ToThe\Absurd\XlApp_be.mdb
But: \\SomeServer\SomeShare\XlApp_be.mdb

If performace still suffers, talk your network admin.

--
Dave Hargis, Microsoft Access MVP

Many thanks. It now works fine and instantly.

The code for creating a one off invoice takes 12 seconds with the database
unsplit and 35 seconds split. Is it possible to do anything about this? it is:

Private Sub Create_New_Invoice_Click()
Dim NewIN As Long
Dim ThisDate

On Error GoTo errcust
ThisDate = InputBox("What date should the new invoice be dated with?",
"Date of Invoicing", Date)
If ThisDate = "" Then Exit Sub

DoCmd.SetWarnings False
DoCmd.Hourglass True

NewIN = NewInvoiceNumber()
DoCmd.RunSQL "UPDATE Enquiries " & "SET [Invoice Number] = " & _
NewIN & " WHERE ([Customer Code] = '" & _
Me![Customer Code Field] & "') AND ([Invoice Number] = 0);", False

DoCmd.RunSQL ("INSERT INTO Invoices ([Invoice Number],[Customer
Code],[Creation Date],[One Off?]) " & _
"VALUES (" & NewIN & ",'" & Me![Customer Code Field] & "','" &
ThisDate & "', Yes);")

DoCmd.OpenForm "Invoices", , , "[Invoice Number]=" & NewIN
DoCmd.Hourglass False

errcust:
DoCmd.SetWarnings True
DoCmd.Hourglass False
End Sub

Thanks very much.
Dudley Miles



:

dbf.Execute "UPDATE Enquiries SET [Invoice Number] = 0 " & _
"WHERE ([Invoice Number] = " & Invs![Invoice Number] & ")", _
dbFailOnError

Also, there was a typo. (Dave included a continuation character, but forgot
that it was in the middle of a string)

dbf.Execute "UPDATE Invoices " & "SET [Processed?] = False, " & _
"[Recent?] = False WHERE ([Recent?] = True AND [One Off?] = True)", _
dbFailOnError


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks very much to Dave and Douglas for your advice. I am puzzled what is
happening. When I paste, the line:

dbf.Execute "UPDATE Enquiries SET [Invoice Number] = 0

I get dbf.Execute "UPDATE Enquiries SET [Invoice Number] = 0"

with " added at the end. If I delete " and test then I get the same error
message and the " has come back.

If I merge the lines as

dbf.Execute "UPDATE Enquiries SET [Invoice Number] = 0 WHERE " _

the " does not come back but I still get the error message, as I do if I
delete Invs in the next line.

Also the 4 lines from WHERE" _ TO dbFaillOn Error, and the 3 lines after
End
With, dbf.Execute... to dbFaillOn Error, are in red.

Thanks
Dudley Miles


:

Hmmmm, I haven't tested that. I will take you at your word, but I will
have
a look. Seems I remember throwing an object error once when doing that.
--
Dave Hargis, Microsoft Access MVP


:

I'm not sure about that, Dave.

Invs![InvoiceNumber] should essentially override the With/End With
construct. It shouldn't result in the equivalent of Invs!Invs![Invoice
Number]. The With only applies when you shortcut using . or !.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Actually, you are the victim of my poor typing
& "([Invoice Number] = " & Invs![Invoice
Number]
Should be
& "([Invoice Number] = " & ![Invoice Number]

It is inside a With/ End With, so we are telling it
Invs!Invs![Invoice Number]
Which does not exist.
--
Dave Hargis, Microsoft Access MVP


:

Thanks very much for your help. When I run this I get a message:

WHERE " _
& "([Invoice Number] = " & Invs![Invoice Number] &

Compile error:
Syntax error

Can you advise please.

Thanks
Dudley

:

To analyze your report problem, it would be necessary to know the
report's
record source and if it is a query, the SQL of the query. It
would
also be
necessary to review all the code including expressions in any
control
source
properties in the report. How you structure these things can make
a
dramatic
difference in the performance of a report. I have found it much
faster
if
you leave the report's row source as plain as possible. If you
have
any
Domain Aggragate functions, put them in the report.

As to your other problem, it is not necessary to use the
dbOpenTable
option.
Additionally, it is helpful to specify DAO or ADO when establish
database
and recordset object variables.

Here is a rewrite of your sub. Notice I use the
Currentdb.Excecute
method
for the action queries. The Execute is much faster than RunSQL or
any
other
method. It does not go through the Access User Interface, so it
doesn't need
the Set Warnings. Bypassing the AUI is part of why it is faster.
It
goes
directly to Jet.

Sub ReverseInvoicing()
Dim dbf As DAO.Database
Dim Invs As DAO.Recordset
Dim NewIN As Long

DoCmd.Hourglass True

Set dbf = CurrentDB
Set Invs = dbf.OpenRecordset("Invoices", dbOpenDynaset)

With Invs
If .Recordcount = 0 Then
MsgBox "No Records in Invoices Table"
Else
.MoveFirst
.MoveFirst
Do While Not .EOF
If ![Recent?] = True And ![One Off?] = False Then
dbf.Execute "UPDATE Enquiries SET [Invoice
Number]
= 0
WHERE " _
& "([Invoice Number] = " & Invs![Invoice
Number] &
");", _
dbFailOnError
End If
.MoveNext
Loop
.Close
End With

dbf.Execute "UPDATE Invoices " & "SET [Processed?] = No, _
[Recent?] = No WHERE ([Recent?] = Yes AND [One Off?] =
Yes);", _
dbFailOnError

dbf.Execute "DELETE * FROM [Invoices] WHERE [Recent?] =
Yes;",
_
dbFailOnError

Call RecalculateINSeed

End If

DoCmd.Hourglass True

End Sub

Let me know if this helps.
--
Dave Hargis, Microsoft Access MVP


:

Some time ago I posted a question about a database not working
properly when
it was split, and Douglas J. Steele explained that it was
because
dbOpenTable
does not work in a split database, and he very kindly supplied
revised code.
I had problems with getting the code working which I have only
now
had time
to sort out. The split database is far more stable across my
network
but
there are still two problems.

1. Some simple commands which are surprisingly slow are even
slower
in the
split database. For example this command button takes two
minutes to
run:
DoCmd.OpenReport "Enquiries by Enquiry Number", acViewPreview.

Command button:
DoCmd.OpenReport "All Enquiries by Type", acViewPreview
DoCmd.RunCommand acCmdZoom100
is so slow unsplit (20 minutes?) that I have not even tried it
yet in
the
split database.

2. There is still one live function, to cancel an invoice run,
which
does