Re: Split database continued

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



"Douglas J. Steele" wrote:

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
http://I.Am/DougSteele
(no e-mails, please!)


"Dudley" <Dudley@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C1EE1097-A7CD-41F2-A41F-3E06B61C65FA@xxxxxxxxxxxxxxxx
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


"Klatuu" wrote:

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


"Douglas J. Steele" wrote:

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
http://I.Am/DougSteele
(no e-mails, please!)


"Klatuu" <Klatuu@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:08BEF03F-86F6-4FEC-8D30-1EC11D4787C2@xxxxxxxxxxxxxxxx
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


"Dudley" wrote:

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

"Klatuu" wrote:

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


"Dudley" wrote:

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








.

Loading