Setting defaults for linked tables using VBA

[isladogs]

[SOLVED] Setting defaults for linked tables using VBA

Hi

I am developing an Access frontend with linked SQL tables
My problem is that I lose various display settings for these tables when they are relinked & would like to set default values using VBA

I know how to reset certain properties for table fields using VBA e.g.
CurrentDb.TableDefs("tblMessageLog").Fields("Success").Properties("DisplayControl") = acCheckBox
CurrentDb.TableDefs("tblMessageLog").Fields("Cost").Properties("DecimalPlaces") = 4
CurrentDb.TableDefs("tblMessageLog").Fields("TimeTaken").Properties("Format") = "Long Time"

However, I'm can't see how to set the following globally for all tables:
1. all bit (Yes/No) fields to be displayed as a checkbox
2. table alternate row color = pale green

Can anyone help me with this?
Many thanks in advance

 

[sneuberg]

I don't know what the table property is that controls alternate color. I didn't even know you could do that. As far as setting the binary fields to checkbox boxes you could just iterate through them all as done in the code below.

Sub SetCheckBox()

Dim tdef As DAO.TableDef
Dim fld As DAO.Field
For Each tdef In CurrentDb.TableDefs
    If tdef.Connect <> "" Then     'skip local tables
        For Each fld In tdef.Fields
            If fld.Type = dbBoolean Then
                fld.Properties("DisplayControl") = acCheckBox
            End If
        Next fld
    End If
Next tdef

End Sub

 

[isladogs]

Hi
This works well provided the property already exists
If not, the property needs to be added.
I've tried putting this within the loop but it doesn't work:

Set prp = fld.CreateProperty("DisplayControl", dbText, acCheckBox)
                        fld.Properties.Append prp

Full code below:

Sub SetCheckBox()

' Turn off error trapping - errors handled below.
On Error Resume Next
'On Error GoTo 0

Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property

Const conErrPropertyNotFound = 3270

Set db = CurrentDb
'Set tdf = db.TableDefs(strTable)

For Each tdf In CurrentDb.TableDefs
    If tdf.Connect <> "" Then     'skip local tables
        For Each fld In tdf.Fields
            If fld.Type = dbBoolean Then
                Debug.Print "Table : " & tdf.Name
                Debug.Print " - Field " & fld.Name
                
                If err.Number <> 0 Then
                    Debug.Print "           - Error = " & err.Number & " " & err.Description
                    If err.Number = conErrPropertyNotFound Then
                        'On Error GoTo 0
                        Set prp = fld.CreateProperty("DisplayControl", dbText, acCheckBox)
                        fld.Properties.Append prp
                        On Error GoTo 0
                    Else
                        On Error GoTo 0
                    End If
                Else
                    fld.Properties("DisplayControl") = acCheckBox
                End If
            End If
        Next fld
    End If
Next tdf

   Set prp = Nothing
    Set fld = Nothing
    Set tdf = Nothing

End Sub

Any idea how to fix the extra bit of code to create the property where needed?

As for the alternate row colour, as you can do it from the ribbon, it must also be possible to code it. I've used the database documenter to try & identify the wording needed but no luck as yet

Thanks for getting me started anyway

 

[Cronk]

As you probably know you cannot edit the design of a linked table in another Access database. That is what you are effectively doing programmatically using currentDB.

I would try opening an instance of the backend database (Note I have not tried this myself).
ie

set db = dbengine(0).opendatabase ("C:\yourpath\yourBE.accdb")
For Each tdf In CurrentDb.TableDefs
.......

 

[isladogs]

Hi Cronk
Sorry but you missed the point ....
Whilst you cannot edit design of linked tables, you can edit how fields are displayed in the front end db.
The changes I want to make are entirely possible to do manually from the front end - I just need to set the display properties globally using VBA

 

[sneuberg]

I can't see what you could be doing wrong in your code and I can't find a way to test this as I've been unable to create a linked table without the DisplayControl property and I haven't been able to delete it. You could try using the SetFieldProperty subroutine in this web page and see if you have better luck.

I've had better luck on the alternate row color problem. If you run the following code

Sub DisplayTableProperty()

Dim tdef As DAO.TableDef
Dim prp As Property

For Each tdef In CurrentDb.TableDefs
    If tdef.Connect <> "" Then     'skip local tables
        Debug.Print tdef.Name
        For Each prp In tdef.Properties
            On Error Resume Next    'prp.Value burbs sometimes
            Debug.Print "     " & prp.Name & "  " & prp.Value
        Next prp
    End If
Next tdef

End Sub

you will see that one of the properties is named DatasheetAlternateBackColor. I think this is the property to set. I've tried

Sub SetAlternateBackColor()

Dim tdef As DAO.TableDef
For Each tdef In CurrentDb.TableDefs
    If tdef.Connect <> "" Then     'skip local tables
        tdef.Properties("DatasheetAlternateBackColor") = RGB(0, 0, 255)
    End If
Next tdef

End Sub

and it sets the alternate color to blue on all of the linked tables.

 

[isladogs]

Hi Steve
Thanks for following this up
I had already tried this approach & the [FONT=&quot]DatasheetAlternateBackColor [/FONT]property for alternate row shading but once again it doesn't work for me using my test database in Access 2010 ...unless I had already created the property by setting a colour using the ribbon command. If that had been done, it works perfectly

This applies whether the tables are local, linked Access or linked SQL
However, my test database has lots of startup restrictions so maybe something in there is preventing VBA creating new properties.

Interestingly, even for tables where I had manually created the row colour from the ribbon, that property does not appear in the output of the DisplayTableProperty function. It is shown using the Access Database Documenter command

I'm going to try again using a 'clean' database & also using Access 2016.
I'll also look at the code from the MS link you supplied

However, I've also been trying an adapted version of code from Allen Browne's excellent website. See StandardProperties function on the DAO functions page. - I can't add the link as I've new so below 10 posts...

That does create the checkbox display control & various other properties as defaults for all tables. However, so far, it also doesn't allow me to create new 'non-standard properties' such as alternate row colour or changing the number of decimal places displayed in a currency field. I'll keep working on this & will upload all code if & when I've cracked it all...

Thanks again

Colin

Thanks again

 

[sneuberg]

I had already tried this approach & the [FONT=&quot]DatasheetAlternateBackColor [/FONT]property for alternate row shading but once again it doesn't work for me using my test database in Access 2010 ...unless I had already created the property by setting a colour using the ribbon command. If that had been done, it works perfectly

Sorry about that. I really thought I had that but I too had added alternate row colors to my table before I ran the code I posted. Now that I try it with a fresh table I'm getting a "property not found error" when the code hits the new table. I'll keep putzing with this as I have time and see if I can figure this out.

 

[isladogs]

Hi again
For info:
Alternate row color still fails with a fresh database for local & linked tables & with Access 2016
Similarly for other fields with new properties e.g.
- setting DateTime field to Long Time format
- setting Currency field to 4 d.p
Once again code works for both if the property already exists

Very odd...probably a glitch in Access!

I'm also going to keep trying as its bugging me..
I'll also try emailing Allen Browne & see if I can coax him out of retirement ..

Colin

 

[CJ_London]

instead of

Set prp = fld.CreateProperty("DisplayControl", dbText, acCheckBox)
fld.Properties.Append prp

have you tried

fld.Properties.Append fld.CreateProperty("DisplayControl", dbText, acCheckBox)

which is the other way of adding properties?

 

[isladogs]

Thanks but I'd already tried this
It has the same effect

I've now solved all the property issues except for alternate row colour.
As well as the 'obvious' properties, this appears to be dependant on the obscure properties GUID & NameMap

I'm now trying to use the various system tables MSys... to solve this
I'm slowly getting there ... like British Rail

 

[isladogs]

Hi Steve,



I’ve spent some (far too much!) more time on this issue & have now got all the additional properties working using VBA EXCEPT for alternate row colours!

For simplicity, I’m using a function SetPropertyDAO from Allen Browne’s website to create or assign a property value as this includes all the error code needed to trap error 3270 where the property doesn’t exist

For example:
If tdf.Name = "tblMessageLog" Then
Select Case fld.Name

Case "Cost"
Call SetPropertyDAO(fld, "DecimalPlaces", dbInteger, 4)

Case "TimeTaken"
Call SetPropertyDAO(fld, "Format", dbText, "Long Time")

End Select
End If

However, alternate row colours has still not been cracked…
I’ve found that setting this using the ribbon adds a total of 23 properties:

Group A – properties used to define the row colours
DatasheetAlternateBackColor
DatasheetBackColor
AlternateBackThemeColorIndex
AlternateBackTint
AlternateBackShade
DatasheetBackColor12
BackThemeColorIndex
BackTint
BackShade
ThemeFontIndex
DatasheetGridlinesThemeColorIndex
DatasheetForeThemeColorIndex

Group B – properties which seem to be unrelated but also created
PublishToWeb
Orientation
OrderByOn
DefaultView
DisplayViewsOnSharePointSite
TotalsRow
FilterOnLoad
OrderByOnLoad
HideNewField

Group C – complex properties (see below)
GUID
NameMap

---------------------------------------------------------------------------------

The code below successfully creates the properties in groups A & B :
Call SetPropertyDAO(tdf, "DatasheetAlternateBackColor", dbText, RGB(205, 220, 175))
Call SetPropertyDAO(tdf, "DatasheetBackColor", dbText, RGB(255, 255, 255))
Call SetPropertyDAO(tdf, "AlternateBackThemeColorIndex", dbInteger, -1) '6
Call SetPropertyDAO(tdf, "AlternateBackTint", dbInteger, 100) '60
Call SetPropertyDAO(tdf, "AlternateBackShade", dbInteger, 100)
Call SetPropertyDAO(tdf, "DatasheetBackColor12", dbLong, -2147483643)
Call SetPropertyDAO(tdf, "BackThemeColorIndex", dbInteger, -1)
Call SetPropertyDAO(tdf, "BackTint", dbInteger, 100)
Call SetPropertyDAO(tdf, "BackShade", dbInteger, 100)
Call SetPropertyDAO(tdf, "ThemeFontIndex", dbInteger, -1)

Call SetPropertyDAO(tdf, "DatasheetGridlinesThemeColorIndex", dbInteger, -1)
Call SetPropertyDAO(tdf, "DatasheetForeThemeColorIndex", dbInteger, -1)
Call SetPropertyDAO(tdf, "PublishToWeb", dbInteger, 1)
Call SetPropertyDAO(tdf, "Orientation", dbInteger, 0)
Call SetPropertyDAO(tdf, "OrderByOn", dbBoolean, False)
Call SetPropertyDAO(tdf, "DefaultView", dbInteger, 2)

Call SetPropertyDAO(tdf, "DisplayViewsOnSharePointSite", dbInteger, 1)
Call SetPropertyDAO(tdf, "TotalsRow", dbBoolean, False)
Call SetPropertyDAO(tdf, "FilterOnLoad", dbBoolean, False)
Call SetPropertyDAO(tdf, "OrderByOnLoad", dbBoolean, True)
Call SetPropertyDAO(tdf, "HideNewField", dbBoolean, False)

However, the alternate colours still aren’t displayed

Next I looked at the Group C properties GUID (dbGUID) & NameMap (dbLongBinary).
The problem is knowing what values to enter for each table
This is made even harder by the fact that the GUID value changes every time a table is relinked!

Where they exist, the two complex field values are found in the system table MSysNameMap

I found that assigning many other table properties such as setting the row height = default also populated the GUID & NameMap fields as well as most of the other properties listed above.So I did set this property first using:
Call SetPropertyDAO(tdf, "RowHeight", dbText, "Default")
I then grabbed the output using a query to assign the property values
strName = tdf.Name
strSelection = "{guid " & Nz(DLookup("GUID", "qrySDATablesList", "ObjectName = ‘” & strName & “'"), "") & "}"
Call SetPropertyDAO(tdf, "GUID", dbGUID, strSelection)

strSelection = Nz(DLookup("NameMap", "qrySDATablesList", "ObjectName = ‘” & strName & “'"), "") & "}"
Call SetPropertyDAO(tdf, "NameMap", dbLongBinary, strSelection)

I now had all 23 properties for each table apparently identical to those produced when creating the alternate row colours using the ribbon



And guess what – IT STILL DOESN’T WORK …..



By this point, I’m losing the will to live

I’m passing this on in case it helps you or anyone can solve the problem


Colin

 

[CJ_London]

why have you set the alternate back colour as text? I would expect it to be long - which is what the RGB function returns

 

[isladogs]

Hi CJ

You're right it should be dbLong.
Many thanks for pointing this out.

I had that datatype originally but as it didn't work when just setting the first 2 properties, DatasheetAlternateBackColor & DatasheetBackColor, I changed it to dbText as a test whilst adding other properties.
I then forgot to change it back...

Anyway, I've done that & relinked the tables - it now works! Hooray!
I've removed the DefaultHeight property as its no longer needed.
I suspect some of the other properties can also be 'lost'

Thanks again for your input

 

[sneuberg]

This is weird and I hope it doesn't affect you but I find that I can run

Call SetPropertyDAO(tdef, "DatasheetAlternateBackColor", dbLong, RGB(255, 0, 255))

multiple times, changing the color, and it works every time until I change the color using the ribbon. Then it stops working. The color is stuck on what I chose through the ribbon.

 

[isladogs]

Hi

Tried that - its not a problem for me
Did you set all the other properties I listed as well?

 

[sneuberg]

Did you set all the other properties I listed as well?

No. Just tried the one.