Speeding up Front End DB while still developing (1 Viewer)

silentwolf

Member
Local time
Today, 13:39
Joined
Jun 12, 2009
Messages
363
Good Mooooorning Database Gurus :)
I am alive and kicking and ready to get back into it :) Coffee is on the go and now I am ready to do some DB improvements :)

@MajP
When you are developing your BE should be local. In this thread I have the code for relinking databases. This lets you switch between a live and design back end. The demo provides 2 databases in the folder and you can switch between them.

well that I did not have I had it on the network as I thought it makes no difference if it is split.
But changed that of course as you said.

@isladogs
In addition to the previous suggestions, make sure you have a persistent connection to your BE.
Otherwise, the Access FE will keep having to reconnect to the BE . . . which is inefficient
Ok well I don't have that and will do that.
Just never heard of it well there are so many things I never heard of once I am thinking about it :unsure:

@CJ_London
Modules
11. do they have Option Explicit just below Option Compare Database?
12. are they compiled?
13. have you done a decompile?
14. are user front ends .accde and each user has their own copy?
15. has currentdb been assigned to a database object which is referred to in code rather than currentdb
16. does the front end maintain a persistent connection with the back end?
- if 15 is done right, it will

Nr.14-- I never used accde file extension so not sure?
Do I need to change that while development?

Or just when I distribute the file?

All other things I am still working on the indexes are set at least all of the joins so still need to look for the filtered ones.
So working my way through CJ_London' list of context :)

Oh forgot
Having troubles with decompile...

this is what I tried but getting an error not found..

Code:
"C:\Program Files (x86)\Microsoft Office\Office16\MSACCESS.EXE""C:\Users\Albert\Ajl_Test\FrontEnd\DKG_FrontEnd.accdb" /decompile

or

"C:\Program Files (x86)\Microsoft Office\Office16\MSACCESS.EXE C:\Users\Albert\Ajl_Test\FrontEnd\DKG_FrontEnd.accdb" /decompile
 

silentwolf

Member
Local time
Today, 13:39
Joined
Jun 12, 2009
Messages
363
So I did include the excel TableSummary of my lates Version of the Front End..


See what you guys think of it especially @strive4peace

Thanks to all of course!!
 

Attachments

  • TableSummary_DKG_FrontEnd__220611-0919.zip
    38.7 KB · Views: 27

CJ_London

Super Moderator
Staff member
Local time
Today, 21:39
Joined
Feb 19, 2013
Messages
14,695
Nr.14-- I never used accde file extension so not sure?
Do I need to change that while development?

Or just when I distribute the file?
as others have mentioned, you should have 3 environments, development, test and production. The test environment should replicate the production environment so create .accde for the test environment. I sometimes do so as well during development, just to check something I developed works as a .accde.
 

isladogs

CID VIP
Local time
Today, 21:39
Joined
Jan 14, 2017
Messages
16,388
When you do convert to ACCDE, make sure you keep the original ACCDB file so you can continue with development work
 

silentwolf

Member
Local time
Today, 13:39
Joined
Jun 12, 2009
Messages
363
Ok so with a ACCDE you can not "work" it is only to test if it is working in the production environment is that right?

@isladogs
When you do convert to ACCDE, make sure you keep the original ACCDB file so you can continue with development work

ok no problem I am backing up and saving as much as possible so I don't get caught out loosing stuff :)

@CJ_London
Ok many thanks for explaination.. well this is all rather new for me I work with Access quite sometime but always just for myself or better own little databases.. now it got a little more into development. So it is my first time development Situation.

That is why I still ask silly questions or need to confirm things a bit more.

But with all of the forum help I am getting step by step closer :)
 

CJ_London

Super Moderator
Staff member
Local time
Today, 21:39
Joined
Feb 19, 2013
Messages
14,695
with .accde's you cannot edit forms, reports or code. Only (local) tables and queries. That is not to say you can't have code to manipulate the look of a form (hide/resize controls for example). But you can't use code to add or delete controls.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:39
Joined
Feb 19, 2002
Messages
36,871
1. Do NOT add specific indexes for foreign keys. Access automatically adds hidden indexes when you define these relationships. You do define relationships and enforce RI, right?
2. Do you have a timer running on some form other than the one you are modifying? It is very dangerous to modify a database while a timer is running. If you use timers, give yourself a way to turn them off and on again so you can have them off except when you are testing the timer itself.
3. Do you have a local or networked printer connected? Access gets slow in development if the default printer isn't available.
4. I keep the FE local when developing but usually leave my copy of the BE on the LAN so I can get the user's experience when testing. If you keep both local, then you need to swap the BE's for testing so you know if you've broken some query and caused slowness.
 

silentwolf

Member
Local time
Today, 13:39
Joined
Jun 12, 2009
Messages
363
relationships and enforce RI, right?
Sure do Pat.

Do NOT add specific indexes for foreign keys
Ok I need to take a a look what exactly I got at present and will show you..

On the foreign key I did when it was already on the Indiziert Yes(without dublikates) or yes with dublicates..

As shown in the picture attached.



2. Do you have a timer running on some form other than the one you are modifying? It is very dangerous to modify a database while a timer is running. If you use timers, give yourself a way to turn them off and on again so you can have them off except when you are testing the timer itself.
What do you mean by running a timer?
I have no timer at all anywhere.. if I like to meassure the speed of a procedure if you mean that.. then I have a small class I run to test the speed.

But that is a one off meaning I run it in particulare circumstances not the hole time or in the background.

3. Do you have a local or networked printer connected?
Just a local printer ..

4. I keep the FE local when developing but usually leave my copy of the BE on the LAN

I understand.. well I did clean up my database unecessary stuff deleted and did go through your sugestions.. and it seams to get alot better already..

So not sure what Crystal has to say about my database once she hed a look through it but I will after wards to a test again to see if it has improved.

Cheers
 

Attachments

  • Indexes.JPG
    Indexes.JPG
    27.3 KB · Views: 16

strive4peace

AWF VIP
Local time
Today, 15:39
Joined
Apr 3, 2020
Messages
921
hi @silentwolf

The default SIZE of a text field is 255 -- this is usually way too long! Especially if a field is indexed or used for searching. The SIZE of most of the fields should be much shorter. I can also see that some of the tables were probably imported since Format got @ (which should be deleted) and Unicode Compression is No (which is Yes by default when you create the data structure manually).

On Unicode Compression ... I have gone out to lunch on whether to set it to yes -- for a long time, I thought yes is best ... but now I am not so sure that is always best. Fields take more space to store. Whatever you do, however, it is important to be the same! If a string is stored without unicode compression in one table and with unicode compression in another, the values won't be "equal" even if they're the same.

A field with unicode compression can store more different types of characters. These are all 255.

TableFieldUC
AbsencesInfo+
AbsenceTypAbsenceTyp+
AuthorizationAuthorization+
BillingCodeBillC_Code+
BillingCodeBillC_Description+
ColoursColour+
Contact_AddressesAddressNo
Contact_AddressesPLZNo
Contact_AddressesTownNo
Contact_DocumentsDocumentname+
Contact_DocumentsFileLocation+
Contact_DocumentsInfo+
Contact_MethodsMethodValue+
Contact_MethodsRules+
ContactInfoInfo+
ContactsContactNumberNo
ContactsCompanyNameNo
ContactsFirstNameNo
ContactsLastNameNo
ContactsTelCompanyNo
ContactsMobileNo
ContactsTelPrivateNo
ContactsEmailNo
ContactsHomepageNo
ContactsDateCreatedNo
ContactsBuchhaltungskontoNo
ContactsErloeskontoNo
ContactsFaxNo
ContactsSteuernummerNo
ContactsUIDNo
CorrespondenceTypCorrespondenceTyp+
CountriesCountry+
CountriesCountryInitial+
DocumentTypDocumentTyp+
MandantMandantKurz+
MandantMandantLang+
MandantAddress+
MandantPLZ+
MandantTown+
MandantTelCompany+
MandantMobile+
MandantEmail+
MandantHomepage+
MandantInfoBIC+
MandantInfoIBAN+
MandantInfoBankInfo+
MandantInfoKontoNr+
MandantInfoBLZ+
MandantInfoDiensgebernr+
MandantInfoUID+
MethodsMobile+
MethodsLandLine+
MethodsEmail+
MethodsFax+
OutlookEmailsEmailFrom+
OutlookEmailsSubject+
OutlookEmailsCC+
OutlookKontakteSalution+
OutlookKontakteContactNumberNo
OutlookKontakteCompanyNameNo
OutlookKontakteFirstNameNo
OutlookKontakteLastNameNo
OutlookKontakteTelefon FirmaNo
OutlookKontakteMobileNo
OutlookKontakteTelPrivatNo
OutlookKontakteEmailNo
OutlookKontakteFaxNo
OutlookKontakteAddressNo
OutlookKontakteTownNo
OutlookKontaktePLZNo
OutlookKontakteCountry+
OutlookStatusUser+
OutlookStatusSubject+
OutlookStatusEmailTo+
OutlookStatusCC+
PaymentTermsPaymenterm+
PersonnelFirstName+
PersonnelLastName+
PersonnelUserName+
PersonnelPassword+
PersonnelAdresse+
PersonnelPLZ+
PersonnelTown+
PersonnelMobile+
PersonnelEmail+
PersonnelInfoInfo+
PositionsPosition+
SalutionSalution+
SalutionSalutionPost+
StatusStatus+
SubjectSubject+
tblBetreffBetreff+
tblEventsTitle+
tblEventsLocation+
tblEventsDescription+
tblGAVLieferantGAVLief_ArtikelNr+
tblGrundArtikelGA_Bezeichnung+
tblGrundArtikelGA_Bezeichnung2+
tblGrundArtikelGA_Bezeichnung3+
tblGrundArtikelGA_PictureFileLocation+
tblGrundArtikelGA_DocFileLocation+
tblHerstellerPlzNo
tblHerstellerKundenNr+
tblHerstellerUID+
tblImportedCalendarSubject+
tblImportedCalendarLocation+
tblImportedCalendarBody+
tblLieferantenPlzNo
tblLieferantenKundenNr+
tblLieferantenUID+
tblLieferantRechnungenLiefRechNr+
tblMEME_BezeichnungLang+
tblMwStMwSt_NameNo
tblProtokolleProtokoll+
tblProtokolleVorlagenPfad+
tblRechnungenrechgThema+
tblZeitraeumezeitrGruppe+
tblZeitraeumezeitrName+
tblZeitraeumezeitrBereich+
TitelTitleLong+
ZeitraumzeitrGruppe+
ZeitraumzeitrName+
ZeitraumzeitrBereich+
 

strive4peace

AWF VIP
Local time
Today, 15:39
Joined
Apr 3, 2020
Messages
921
adding on, @silentwolf

I also see that you have several number fields with a Default Value of 0. Access does this "for you" when you make number fields -- and sometimes you do indeed want a default value of 0 ... but usually NOT for a field that's a foreign key. These fields all have a default value of 0:

AbsencesPersoIDRefLong
AbsencesAbwesenheitsartIDLong
AbsenceTypColourIDRefLong
Contact_AddressesContIDRefLong
Contact_AddressesAdTypIDRefLong
Contact_AddressesCountIDRefLong
Contact_CorrespondenceSubIDRefLong
Contact_CorrespondenceStatusIDRefLong
Contact_CorrespondenceColourIDRefLong
Contact_DocumentsContIDRefLong
Contact_DocumentsBillCIDRefLong
Contact_DocumentsDocTIDRefLong
Contact_DocumentsStatusIDRefLong
Contact_DocumentsColourIDRefLong
Contact_MethodsContIDRefLong
Contact_MethodsMethodIDRefLong
ContactInfoContIDRefLong
ContactInfoPayTIDRefLong
ContactInfoDiscIDRefLong
ContactPerson_CorrespondenceSubIDRefLong
ContactPerson_CorrespondenceStatusIDRefLong
ContactPerson_CorrespondenceColourIDRefLong
ContactsMandIDRefLong
ContactsSalIDRefLong
ContactsTitleIDRefLong
ContactsPosIDRefLong
Document_MembersContDoIDRefLong
DocumentDetailsContDocIDRefLong
DocumentDetailsGAVAIDRefLong
DocumentDetailsQuantityDouble
DocumentDetailsSortOrderByte
MandantCountIDRefLong
MandantInfoMandIDLong
OutlookEmailsContIDRefLong
OutlookStatusContIDRefLong
PersonnelCountIDRefLong
Personnel_AuthorizationsPersoIDRefLong
Personnel_AuthorizationsAuthoIDRefLong
Personnel_CorrespondencePersoIDRefLong
Personnel_CorrespondenceSubIDRefLong
Personnel_CorrespondenceStatusIDRefLong
Personnel_CorrespondenceColourIDRefLong
PersonnelInfoPersoIDRefLong
PersonnelInfoPaymentAmountCurrency
tblGAVAIDGAVA_DiscIDRefLong
tblHerstellerMan_IDRefLong
tblHerstellerCountIDRefLong
tblLieferantenMandIDRefLong
tblLieferantenCountIDRefLong
tblLieferantRechnungenLiefIDRefLong
tblLieferantRechnungenBetragNettoCurrency
tblLieferantRechnungenBetragBruttoCurrency
tblLieferantRechnungenRechSteuerCurrency
tblLieferantRechnungenPayTIDRefLong
tblLiefRechDetailsGAVA_IDRefLong
tblLiefRechDetailsLiefRechIDLong
tblLiefRechDetailsQuantityDouble
tblMwStMwSt_WertDouble
tblZeitraeumezeitrNrLong
tblZeitraeumezeitrOffsetLong
ZeitraumzeitrOffsetLong
 

strive4peace

AWF VIP
Local time
Today, 15:39
Joined
Apr 3, 2020
Messages
921
adding on, @silentwolf

Why do each of these tables have the exact same number of records?
Table#Recs
tblGAVAID
8133​
tblGAVerpackung
8133​
tblGAVLieferant
8133​
tblGrundArtikel
8133​

~~~~~~~~~~~~~~~

On the Analyzer -- I think you forgot to take it OUT of the ZIP file before running it! Unzip it and try again -- the Table Summary and Deep Analysis reports are more informative than the output to Excel. Also give us your Table Indexes report from the Analyzer.

Indexes can speed things up, but they can also slow things down if there are too many of them and they aren't on fields where needed, and the text SIZE of those fields is 255! If you will ever upsize the back-end to SQL Server, don't let text fields that are indexed be longer than 220 (and in case they're part of a multi-field index, I generally don't go past 200).

Indexes don't seem to get changed when SIZE is changed ... so best, imo, to delete indexes on text fields that allow too many characters, save table, change SIZE of text fields, then add the index back again.
 
Last edited:

silentwolf

Member
Local time
Today, 13:39
Joined
Jun 12, 2009
Messages
363
Hi Crystal,

many thanks for looking into it for me!

Ok I understand and will change those subjects you mentioned!

Cheers and happy Sunday :)
 

strive4peace

AWF VIP
Local time
Today, 15:39
Joined
Apr 3, 2020
Messages
921
@silentwolf, on Required fields ... setting a field to be required is a mxed bag. I prefer to let anything go into the table so data that's imported won't get knocked out. To make sure a user fills something out, you can use the BeforeUpdate event for a form to check and give the user a message.

These are all Required:

Contact_CorrespondenceContIDRefLong
4​
Contact_CorrespondenceSubIDRefLong
4​
0
Contact_CorrespondenceCorrTypIDRefLong
4​
Contact_CorrespondenceDateCreatedDateTime
8​
Date()
Contact_DocumentsDateCreatedDateTime
8​
=now()
Contact_MethodsContIDRefLong
4​
0
Contact_MethodsMethodIDRefLong
4​
0
ContactPerson_CorrespondenceContPIDRefLong
4​
ContactPerson_CorrespondenceDateCreatedDateTime
8​
Date()
ContactPerson_CorrespondenceCorrTypIDRefLong
4​
ContactPerson_CorrespondenceSubIDRefLong
4​
0
ContactsMandIDRefLong
4​
0
DiscountsDiscIDLong
4​
DocumentDetailsQuantityDouble
8​
0
PersonnelFirstNameText
255​
PersonnelLastNameText
255​
PersonnelAdresseText
255​
PersonnelPLZText
255​
PersonnelTownText
255​
Personnel_AuthorizationsPersoIDRefLong
4​
0
Personnel_AuthorizationsAuthoIDRefLong
4​
0
Personnel_CorrespondenceSubIDRefLong
4​
0
Personnel_CorrespondenceCorrTypIDRefLong
4​
Personnel_CorrespondenceDateCreatedDateTime
8​
Date()
tblZeitraeumezeitrNrLong
4​
0

and FirstName and LastName are way too long! Probably other fields too.
 

silentwolf

Member
Local time
Today, 13:39
Joined
Jun 12, 2009
Messages
363
Oh there are more messages )
Why do each of these tables have the exact same number of records?

this looks like this in the below picture.

With this I could check prices from different Prices..

but I just importet those aritcles so I got data..

But I guess It is a old database I set up with someone helping me on a german forum.
Not sure now if that is even correct.. need to test something on that one..
 

Attachments

  • tblGAVAID.JPG
    tblGAVAID.JPG
    127 KB · Views: 18

silentwolf

Member
Local time
Today, 13:39
Joined
Jun 12, 2009
Messages
363
To make sure a user fills something out, you can use the BeforeUpdate event for a form to check and give the user a message.

Ok understood.
Yes it got all a little messy old data and so on that is why I find it so useful to have test, develop and production stages as you guys sugested so important.

Never done it like that so it got very messy... and lots of imports as you mentioned made things worse...
 

strive4peace

AWF VIP
Local time
Today, 15:39
Joined
Apr 3, 2020
Messages
921
you're welcome, @silentwolf!

after you UNZIP the Analyzer, you should be able to run it ok. It also tells you how long the maximum data is in your text fields, so that helps you with changing SIZE. I generally add more than the maximum length -- but many fields, I know how long they should be already.
 
Last edited:

silentwolf

Member
Local time
Today, 13:39
Joined
Jun 12, 2009
Messages
363
after you UNZIP the Analyzer, you should be able to run it ok.

hmm I did unzip it :unsure:

but will try again after I did clean up the mess first a little )

Oh by the way...

How could I write a small function to update those fields in one go?

Rather then go through them all by one?

so maybe something like get rid of all @ formating out of each field of the database?
 

strive4peace

AWF VIP
Local time
Today, 15:39
Joined
Apr 3, 2020
Messages
921
hi @silentwolf

here is code to loop through all tables and fields. All it does it output the table and field to the Debug window ... but if you have more fields in your database than the number of lines the debug window can have, they won't all be there.

Rich (BB code):
Sub LoopTablesFields()
'220109 s4p, Ade modify CurrentDb()
   On Error GoTo Proc_Err
   
   Dim db As DAO.Database _
      ,oTdf As DAO.TableDef _
      ,oFld As DAO.Field
   Dim i As Integer
  
   Set db = CurrentDb()
   i = 0
  
   'loop tables
   For Each oTdf In db.TableDefs
      With oTdf
         'skip system tables
         If Not Left(oTdf.Name,4) =  "MSys" Then
            'loop fields
            For Each oFld In oTdf.Fields
               i = i + 1
               With oFld
                  Debug.Print Format(i, "000 ") & oTdf.Name _
                  &  "." & oFld.Name
               End With  'field
            Next oFld
         End If
      End With  'table
   Next oTdf
   MsgBox  "Done"

Proc_Exit:
   On Error Resume Next
   Set oFld = Nothing
   Set oTdf = Nothing
   Set db = Nothing
   Exit Sub
 
Proc_Err:
   MsgBox Err.Description _
       ,, "ERROR " & Err.Number &  "   LoopTablesFields"
   Resume Proc_Exit
   Resume
End Sub

Instead of doing this, you could test fields to see, for instance, if Required is true -- or UnicodeCompression is true, ...

Rich (BB code):
dim iCountChanged as integer

'then in the loop:
if oFld.Properties( "Required") = true then
   oFld.Properties( "Required") = false
   iCountChanged = iCountChanged + 1
end if
 

Users who are viewing this thread

Top Bottom