Speeding up Front End DB while still developing

[silentwolf]

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

@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..

"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]

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!!

 

[CJ_London]

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]

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

 

[silentwolf]

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]

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.

 

[silentwolf]

Ok understand!
Thank you!

 

[Pat Hartman]

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]

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

 

[strive4peace]

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.

Table	Field	UC
Absences	Info	+
AbsenceTyp	AbsenceTyp	+
Authorization	Authorization	+
BillingCode	BillC_Code	+
BillingCode	BillC_Description	+
Colours	Colour	+
Contact_Addresses	Address	No
Contact_Addresses	PLZ	No
Contact_Addresses	Town	No
Contact_Documents	Documentname	+
Contact_Documents	FileLocation	+
Contact_Documents	Info	+
Contact_Methods	MethodValue	+
Contact_Methods	Rules	+
ContactInfo	Info	+
Contacts	ContactNumber	No
Contacts	CompanyName	No
Contacts	FirstName	No
Contacts	LastName	No
Contacts	TelCompany	No
Contacts	Mobile	No
Contacts	TelPrivate	No
Contacts	Email	No
Contacts	Homepage	No
Contacts	DateCreated	No
Contacts	Buchhaltungskonto	No
Contacts	Erloeskonto	No
Contacts	Fax	No
Contacts	Steuernummer	No
Contacts	UID	No
CorrespondenceTyp	CorrespondenceTyp	+
Countries	Country	+
Countries	CountryInitial	+
DocumentTyp	DocumentTyp	+
Mandant	MandantKurz	+
Mandant	MandantLang	+
Mandant	Address	+
Mandant	PLZ	+
Mandant	Town	+
Mandant	TelCompany	+
Mandant	Mobile	+
Mandant	Email	+
Mandant	Homepage	+
MandantInfo	BIC	+
MandantInfo	IBAN	+
MandantInfo	BankInfo	+
MandantInfo	KontoNr	+
MandantInfo	BLZ	+
MandantInfo	Diensgebernr	+
MandantInfo	UID	+
Methods	Mobile	+
Methods	LandLine	+
Methods	Email	+
Methods	Fax	+
OutlookEmails	EmailFrom	+
OutlookEmails	Subject	+
OutlookEmails	CC	+
OutlookKontakte	Salution	+
OutlookKontakte	ContactNumber	No
OutlookKontakte	CompanyName	No
OutlookKontakte	FirstName	No
OutlookKontakte	LastName	No
OutlookKontakte	Telefon Firma	No
OutlookKontakte	Mobile	No
OutlookKontakte	TelPrivat	No
OutlookKontakte	Email	No
OutlookKontakte	Fax	No
OutlookKontakte	Address	No
OutlookKontakte	Town	No
OutlookKontakte	PLZ	No
OutlookKontakte	Country	+
OutlookStatus	User	+
OutlookStatus	Subject	+
OutlookStatus	EmailTo	+
OutlookStatus	CC	+
PaymentTerms	Paymenterm	+
Personnel	FirstName	+
Personnel	LastName	+
Personnel	UserName	+
Personnel	Password	+
Personnel	Adresse	+
Personnel	PLZ	+
Personnel	Town	+
Personnel	Mobile	+
Personnel	Email	+
PersonnelInfo	Info	+
Positions	Position	+
Salution	Salution	+
Salution	SalutionPost	+
Status	Status	+
Subject	Subject	+
tblBetreff	Betreff	+
tblEvents	Title	+
tblEvents	Location	+
tblEvents	Description	+
tblGAVLieferant	GAVLief_ArtikelNr	+
tblGrundArtikel	GA_Bezeichnung	+
tblGrundArtikel	GA_Bezeichnung2	+
tblGrundArtikel	GA_Bezeichnung3	+
tblGrundArtikel	GA_PictureFileLocation	+
tblGrundArtikel	GA_DocFileLocation	+
tblHersteller	Plz	No
tblHersteller	KundenNr	+
tblHersteller	UID	+
tblImportedCalendar	Subject	+
tblImportedCalendar	Location	+
tblImportedCalendar	Body	+
tblLieferanten	Plz	No
tblLieferanten	KundenNr	+
tblLieferanten	UID	+
tblLieferantRechnungen	LiefRechNr	+
tblME	ME_BezeichnungLang	+
tblMwSt	MwSt_Name	No
tblProtokolle	Protokoll	+
tblProtokolle	VorlagenPfad	+
tblRechnungen	rechgThema	+
tblZeitraeume	zeitrGruppe	+
tblZeitraeume	zeitrName	+
tblZeitraeume	zeitrBereich	+
Titel	TitleLong	+
Zeitraum	zeitrGruppe	+
Zeitraum	zeitrName	+
Zeitraum	zeitrBereich	+

 

[strive4peace]

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:

Absences	PersoIDRef	Long
Absences	AbwesenheitsartID	Long
AbsenceTyp	ColourIDRef	Long
Contact_Addresses	ContIDRef	Long
Contact_Addresses	AdTypIDRef	Long
Contact_Addresses	CountIDRef	Long
Contact_Correspondence	SubIDRef	Long
Contact_Correspondence	StatusIDRef	Long
Contact_Correspondence	ColourIDRef	Long
Contact_Documents	ContIDRef	Long
Contact_Documents	BillCIDRef	Long
Contact_Documents	DocTIDRef	Long
Contact_Documents	StatusIDRef	Long
Contact_Documents	ColourIDRef	Long
Contact_Methods	ContIDRef	Long
Contact_Methods	MethodIDRef	Long
ContactInfo	ContIDRef	Long
ContactInfo	PayTIDRef	Long
ContactInfo	DiscIDRef	Long
ContactPerson_Correspondence	SubIDRef	Long
ContactPerson_Correspondence	StatusIDRef	Long
ContactPerson_Correspondence	ColourIDRef	Long
Contacts	MandIDRef	Long
Contacts	SalIDRef	Long
Contacts	TitleIDRef	Long
Contacts	PosIDRef	Long
Document_Members	ContDoIDRef	Long
DocumentDetails	ContDocIDRef	Long
DocumentDetails	GAVAIDRef	Long
DocumentDetails	Quantity	Double
DocumentDetails	SortOrder	Byte
Mandant	CountIDRef	Long
MandantInfo	MandID	Long
OutlookEmails	ContIDRef	Long
OutlookStatus	ContIDRef	Long
Personnel	CountIDRef	Long
Personnel_Authorizations	PersoIDRef	Long
Personnel_Authorizations	AuthoIDRef	Long
Personnel_Correspondence	PersoIDRef	Long
Personnel_Correspondence	SubIDRef	Long
Personnel_Correspondence	StatusIDRef	Long
Personnel_Correspondence	ColourIDRef	Long
PersonnelInfo	PersoIDRef	Long
PersonnelInfo	PaymentAmount	Currency
tblGAVAID	GAVA_DiscIDRef	Long
tblHersteller	Man_IDRef	Long
tblHersteller	CountIDRef	Long
tblLieferanten	MandIDRef	Long
tblLieferanten	CountIDRef	Long
tblLieferantRechnungen	LiefIDRef	Long
tblLieferantRechnungen	BetragNetto	Currency
tblLieferantRechnungen	BetragBrutto	Currency
tblLieferantRechnungen	RechSteuer	Currency
tblLieferantRechnungen	PayTIDRef	Long
tblLiefRechDetails	GAVA_IDRef	Long
tblLiefRechDetails	LiefRechID	Long
tblLiefRechDetails	Quantity	Double
tblMwSt	MwSt_Wert	Double
tblZeitraeume	zeitrNr	Long
tblZeitraeume	zeitrOffset	Long
Zeitraum	zeitrOffset	Long

 

[strive4peace]

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.

 

[silentwolf]

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]

@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_Correspondence	ContIDRef	Long	4​
Contact_Correspondence	SubIDRef	Long	4​	0
Contact_Correspondence	CorrTypIDRef	Long	4​
Contact_Correspondence	DateCreated	DateTime	8​	Date()
Contact_Documents	DateCreated	DateTime	8​	=now()
Contact_Methods	ContIDRef	Long	4​	0
Contact_Methods	MethodIDRef	Long	4​	0
ContactPerson_Correspondence	ContPIDRef	Long	4​
ContactPerson_Correspondence	DateCreated	DateTime	8​	Date()
ContactPerson_Correspondence	CorrTypIDRef	Long	4​
ContactPerson_Correspondence	SubIDRef	Long	4​	0
Contacts	MandIDRef	Long	4​	0
Discounts	DiscID	Long	4​
DocumentDetails	Quantity	Double	8​	0
Personnel	FirstName	Text	255​
Personnel	LastName	Text	255​
Personnel	Adresse	Text	255​
Personnel	PLZ	Text	255​
Personnel	Town	Text	255​
Personnel_Authorizations	PersoIDRef	Long	4​	0
Personnel_Authorizations	AuthoIDRef	Long	4​	0
Personnel_Correspondence	SubIDRef	Long	4​	0
Personnel_Correspondence	CorrTypIDRef	Long	4​
Personnel_Correspondence	DateCreated	DateTime	8​	Date()
tblZeitraeume	zeitrNr	Long	4​	0

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

 

[silentwolf]

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..

 

[silentwolf]

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]

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.

 

[silentwolf]

after you UNZIP the Analyzer, you should be able to run it ok.

hmm I did unzip it

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]

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.

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, ...

dim iCountChanged as integer

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

 

[silentwolf]

Oh Wow many thanks Crystal I will try that very soon ..)

Cheers