Speeding up Front End DB while still developing

[strive4peace]

@silentwolf

perhaps, if you did unzip the Analyzer before running, the file is blocked since it came from the web.

Unblock files if you have Mark of the Web issues:

https://msaccessgurus.com/MOTW_Unblock.htm

 

[silentwolf]

Oh ok wow got things do do here lol

Thanks again for your help I will try to work this all out ...

Cheers

 

[silentwolf]

so I am not sure how to change that code from crystal to delete all @ formated textboxes in my db

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
 
    Dim prpName As String
    prpName = "Format"
    
   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

What do I need to fil in instead of the debug.print so it only findes those fields and then get rid of the @ sign ?

Thanks for helping!
Cheers

 

[Pat Hartman]

I guess my explanation wasn't clear.

If your default printer is disconnected, Access will keep looking for it when your forms/reports are in design view and that produces slowness. This is very common when you take your laptop home from the office without changing the default printer to your local printer.

REMOVE any index YOU have placed on ALL foreign keys. Access creates these indexes automatically.

 

[silentwolf]

Hi Pat,

oh ok lol I stuffed it up .. ooops ..

Lots to change now in my littel db gg



Cheers for explaination!

 

[Pat Hartman]

Have fun

 

[silentwolf]

lol yeap nice to know what needs to be done on a sunday Evening gg

 

[strive4peace]

so I am not sure how to change that code from crystal to delete all @ formated textboxes in my db

What do I need to fil in instead of the debug.print so it only findes those fields and then get rid of the @ sign ?

Thanks for helping!
Cheers

@silentwolf, here you go!

Sub LoopTablesFields_RemoveFormat()
'220109 s4p, Ade CurrentDb(), 220612
   On Error GoTo Proc_Err
   
   Dim db As DAO.Database _
      ,oTdf As DAO.TableDef _
      ,oFld As DAO.Field
   Dim i As Integer _
      ,iCountDone As Integer
  
   Set db = CurrentDb()
   i = 0
   iCountDone = 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
                  If .Properties( "Format") =  "@" Then
                     .Properties.Delete  "Format"
                     Debug.Print Format(i, "000 ") & oTdf.Name _
                        &  "." & .Name
                     iCountDone = iCountDone + 1
                  End If
               End With  'field
proc_NextField:
            Next oFld
         End If
      End With  'table
   Next oTdf
   MsgBox  "Removed @ in Format for " & iCountDone &  " fields." _
      ,, "Done"

Proc_Exit:
   On Error Resume Next
   Set oFld = Nothing
   Set oTdf = Nothing
   Set db = Nothing
   Exit Sub

Proc_Err:
   If Err.Number = 3270 Then  'property not found
      Resume proc_NextField
   End If
   MsgBox Err.Description _
       ,, "ERROR " & Err.Number &  "   LoopTablesFields"
   Resume Proc_Exit
   Resume
End Sub

 

[silentwolf]

Wow many Thanks to you Crystal ;-)

Cheers so I can go for a beer now lol gg

Thanks

 

[strive4peace]

you're welcome, @silentwolf -- drink one for me too!

 

[silentwolf]

Sure do Crystal

 

[silentwolf]

Hi guys,

Crystal gave me that code and it works perfekt to change format with @ simpol

however to change the other Properties of each table I struggle.

here is what I tried to get the "Standart Value" out but did not find anything.. could someone give me a hint of what needs to change please?

Sub LoopTablesFields_RemoveStandart()
'220109 s4p, Ade CurrentDb(), 220612
   On Error GoTo Proc_Err
  
   Dim db As DAO.Database _
      , oTdf As DAO.TableDef _
      , oFld As DAO.Field
   Dim i As Integer _
      , iCountDone As Integer
 
   Set db = CurrentDb()
   i = 0
   iCountDone = 0
 
    Dim iCountChanged As Integer
   '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
              
                    'then in the loop:
                    If oFld.Properties("Required") = True Then
                       oFld.Properties("Required") = False
                       iCountChanged = iCountChanged + 1
                    End If
              
              
              

               End With  'field
proc_NextField:
            Next oFld
         End If
      End With  'table
   Next oTdf
   MsgBox "Removed @ in Format for " & iCountDone & " fields." _
      , , "Done"

Proc_Exit:
   On Error Resume Next
   Set oFld = Nothing
   Set oTdf = Nothing
   Set db = Nothing
   Exit Sub

Proc_Err:
   If Err.Number = 3270 Then  'property not found
      Resume proc_NextField
   End If
   MsgBox Err.Description _
       , , "ERROR " & Err.Number & "   LoopTablesFields"
   Resume Proc_Exit
   Resume
End Sub

 

[strive4peace]

@silentwolf hint: the property name is DefaultValue (no space) in English ... perhaps Standardwert in German -- not sure which you need to use

pattern after example for Format and delete value if it is a zero and data type is Long Integer (oFld.Type=4) -- however, maybe only for foreign key, so you might want to manually put some of those back ...

 

[silentwolf]

Hi Crystal,

I did update them already by hand as I did not want to stuff up again gg

But thanks for your info I will keep that in mind!

Cheers

 

[silentwolf]

@silentwolf hint: the property name is DefaultValue (no space) in English ... perhaps Standardwert in German -- not sure which you need to use

pattern after example for Format and delete value if it is a zero and data type is Long Integer (oFld.Type=4) -- however, maybe only for foreign key, so you might want to manually put some of those back ...

Hi Crystal,

thanks again for your help!
I understand I will look into it but today I was busy with finding duplicates lol also fun ..)

But will keep it in mind and study it and look into it!

Cheers

 

[silentwolf]

Hmm, you shouldn't develop using live data. It's recommended to have three versions of your db: dev, test, and live.

You make changes in the dev version, give it to users for testing, and then send it to production after fixing all the broken parts.

Hi guys,

I need to get back to those three Databases subject for a little.

Can someone give me a little inside of how you develop?

I started with a fresh slate as there where some issues with my old DB. "TableStructure" and some other issues I was not happy with.

However I created following FolderPath:

"C:\Users\Albert\Documents\AJL\Dev
another C:\Users\Albert\Documents\AJL\Test
and another C:\Users\Albert\Documents\AJL\Live

On the Dev Folder I got "AJL_BackEnd.accdb" and "AJL_FrontEnd.accdb" which is linked to the corresponding BackEnd in that Directory.

So how do you use your Folder Structure and when do you work with what file?

Sounds pretty silly I guess but as I said it is new for me and would just like to know how you work with it?

1. Do you have for each Folder a Front End and a Back End file?
2. When do you switch from Dev to Test and Production?
3. When you are in Development Stage do you stay on that Front End file and then create backups for BackEnd and FrontEnd.
but do you just keep the one from the Development.

For example I develop with my Front End .. "C....\Dev\AJL_FrontEnd.accedb."
then I create backups in a seperate Folder called "C:\Users\Albert\Documents\AJL\BackUp
where I save all Backups rigth?

Is this right so far?
And how you continue?

Do you have for each Stage seperate FrontEnd and BackEnd files?

Cheers
Sorry for beeing a bit blond

 

[CJ_London]

1 yes but test and live back ends should be on the server
2. When you are ready to test. Routine is test, identify issues and return to development. Goes to production once all testing completed without errors and signed of by production
3 backup all the time - I do it before every change. Since changes should only occur in development that’s where your backups need to be

I save backups in a subfolder of the development folder and I have another subfolder called archive where I put finished versions.

with regards testing, you should been provided with a document which dictates how the app is intended to work and should include user stories eg ‘I need to be able to click on a contract and view the contents on screen and email a copy’

so testing will be about the user being able to do this ‘easily’, not just about resolving errors