Code Colors Like In VB Editor

[Dreamweaver]

Can the accda be compacted ??

 

[strive4peace]

not while you're in it. Better to put the big tables external.

 

[strive4peace]

... actually big tables are ok if they're static, but if they bloat the database, you wouldn't want to keep them in the add-in

 

[Dreamweaver]

All the tables would be linked

 

[strive4peace]

hi Mick,

Post a database with the tables you want to link -- with no data so I can see what you have.

 

[Dreamweaver]

I was watching your Video Again going over it again And think It's sinking in from what I see in the system table you spec a folder where the accda goes and noticed the same with the video so If I can get everything in that folder by installing it of unzipping it to that location everything should work.

The tables have been kept simple but also I designed them so they would work with @MajP treeview not 100% sure about the categories but used that design in a book library

 

[Dreamweaver]

Something had been bugging me as I forgot what It was but finally remembered in tblPlatforms you need to add another field UseColourCompiler I have for now given it a default of 1 which will be your VBA compiler but if I add say HTML code etc it will need to run a different compiler.

I will prob add another table for compilers but that would just be a lookup I missed the when I did the ERD

 

[strive4peace]

I was watching your Video Again going over it again And think It's sinking in from what I see in the system table you spec a folder where the accda goes and noticed the same with the video so If I can get everything in that folder by installing it of unzipping it to that location everything should work.

not really ... |ACCDIR\ gets substituted with the installation path -- that is why I have your the VBA to figure out what that is Again though, you wouldn't want that database to just grow and grow! Two ways around this (1) unlink tables by deleting the links then (a) compact the linked db or (b) create it again. Personally, I would put code in the add-in to create it.That is why I asked for your tables, was planning to help you with the code to do that.

 

[Dreamweaver]

But wouldn't a compact and repair do the same I remember you saying you couldn't do this as an addin but if you open the file directly you have that option same with data file or am I missing something?.

 

[Dreamweaver]

Just having a look into bloating as am concerned that deleting tables then recreating them could cause more bloat than it saves but not sure.

I came across this from @Pat Hartman https://www.access-programmers.co.u...to-bloat-file-size-increase.48759/post-181098 I don't know if what you want to do crystal will cause more problems I do know there are tools that can compact both the front end accda and data file which would also need compacting.

I'm not expecting a large anount of edits/updates and will make sure all queries a saved quiries that will help.

Any thought??

 

[strive4peace]

hi Mick,

compact would be on the database that has the linked tables, not the add-in. If someone told you that, it wasn't me because I haven't tried it.

deleting a LINKED table just deletes its reference

I don't know how your add-in will work. I got the idea it would store a lot of data. I've been using my Color Comments Green add-in quite a bit. I re-use the same 2 fields rather than making new records. I just compacted it. The file size went from 504kb to 452kb ... but it doesn't really store a lot -- but over time, left unattended, it might. Now you get me wondering too! I'll check its size peridically now to see if it gets much bigger!

 

[Dreamweaver]

I think The best thing it to try it and see what needs to be done It's all a learning curve at the moment, me I know nothing except what you video shows.

I have built a few librarys Which work on the principal of what we are trying to do except they need a reference in the main program.

 

[Dreamweaver]

Well I have it working sort of but I know what it don't like I thing that is Dlookup and the like

I will have to recode a bit and use SQL instead of Dlookup etc.

The Linked tables work and I can compact the addin.

my Access location is: C:\Program Files (x86)\Microsoft Office\root\Office16

This is images of my Newsletter creator with the code addin

 

[Dreamweaver]

Solved My Problem Well made a good start still have a minor issue with getting the fonts from my dslooup but think I'll have a workaround for that

I have had to make 2 functions as it didn't like IsMissing when using Optional

Public Function DDLookUp(StrField As String, StrTable As String) As Variant
'=================================================================
'Description: Replacement for DLookup
'Called By: As Required
'Calling: None
'Parameters: StrField The Field, StrTable The Table
'Returns: Variant
'Author: Michael Javes, Database Dreams
'Editor(s) :
'Date Created: 21 April 2020
'Rev. History:
'Requirements: None
'=================================================================
Dim R As DAO.Recordset
Dim Str As String
On Error GoTo HandleErr

Set R = CodeDb.OpenRecordset("SELECT * FROM " & StrTable, dbOpenSnapshot)
    DDLookUp = R(StrField)

R.Close
Set R = Nothing

HandleExit:
    Exit Function
   
HandleErr:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & vbCrLf & Err.Description
            Resume HandleExit
        Resume
    End Select
End Function
Public Function DDXLookUp(StrField As String, StrTable As String, DDWhere As String) As Variant
'=================================================================
'Description: Replacement for DLookup
'Called By: As Required
'Calling: None
'Parameters: StrField The Field, StrTable The Table DDWhere A where startment if included
'Returns: Variant
'Author: Michael Javes, Database Dreams
'Editor(s) :
'Date Created: 21 April 2020
'Rev. History:
'Requirements: None
'=================================================================
Dim R As DAO.Recordset
Dim Str As String
On Error GoTo HandleErr

Set R = CodeDb.OpenRecordset("SELECT * FROM " & StrTable & " WHERE " & DDWhere, dbOpenSnapshot)
    DDXLookUp = R(StrField)

R.Close
Set R = Nothing

HandleExit:
    Exit Function
   
HandleErr:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & vbCrLf & Err.Description
            Resume HandleExit
        Resume
    End Select
End Function

I will see If I can inprove these functions and have another go at getting them working as one

the addin works perfectly event my spash screen tomorrow I will rename the data file so I can check the link manager.

Also my style manager works

 

[Dreamweaver]

Link Manager works But think I'll Updating min as it uses a table to hold the link table names as I used to use multi linked db's

I had forgotten to run the linked table list function maybe I'll run it before I run the link manager from now on

but it all works so can continue

 

[strive4peace]

sounds like you're making progress, Mick ~ tarry on

 

[Dreamweaver]

Yeh I messed up a few times which cost me 3hrs thinking I had a problem when I didn't but Able now to futher develope it.

I'm going to do a final compact and repair just to make sure then I'll move on to the categories and add more data before I tackle the treeview

 

[strive4peace]

tackle the treeview

have fun with that!

 

[Dreamweaver]

As Luck Has is @MajP has already done the hard work I just need to get it working with my program

 

[Dreamweaver]

One Starange thing I noticed If The Main Program hasen't got a Icon Set Then mine wont display even though I'm using code
I do need to find another bmp so I can test what happens when there is one just to see if my code works in the addin

CodeDb.Properties("AppIcon").Value = DDLookUp("Location", "tblPreferences") & "\DD Icon.bmp"
Application.RefreshTitleBar

Edit It seems to use the Main App Icon Regardless of code I'll keep playing just to see