Preventing tables,queries, forms and reports to be imported into a new database

[moke123]

Apologies to the OP for highjacking your thread.

Here's a few updates. Make sure you test it on a copy of a database as I haven't tested it thoroughly enough.

In the opening form, use the file picker to select a database file.
Click the link tables button and then the Code Generator button.


When done you can click the unlink button.

[isladogs]

Hi Moke
Just had a very quick look at this but will take a more detailed look in the next few days
Can I suggest you start a new thread for discussion of this utility - what it is/does etc...

As I originally said, there is a lot here of interest. Some initial comments:
1. Linking tables only links the local tables in that database. It would be useful to also link the linked BE tables in the selected database.
2. Linking is fast. Unlinking is much slower and there is no indication that anything is happening
3. When Generate Textboxes is clicked and the form frmTextboxes is closed, the popup form frmClose remains open. This is a bit confusing
Not sure I understand what the point of this feature is to be honest.
4. Add New DAO / Update DAO are both 'coming soon'. Perhaps disable for now? Perhaps add Insert & MakeTable to the list when you have time to look at this

[moke123]

Col,
I just didn't want to leave that old copy out there with the known bug that GasMan mentioned. The only purpose of the utility is that I'm a lazy typist and it really wasn't intended to share.

Just to answer your questions/comments:
1.You can link to any number of db's, front and back. The only purpose of linking is to make the tables and fields available.
2. theres a loop so maybe i'll add a progress bar.
3. The textboxes are so you can copy and paste them into a form(I use a lot of unbound forms on occasion) and you dont have to individually name them.
frmClose is just to close the textbox form that is in design mode.
4.I've lots of ideas to try but its a fun toy to play with so i'm sure I'll get around to those soon.

No need to open a thread, and again apologies to the OP.
Thanks again for saving it for me. I'll send you copies of all the rest of my stuff so I dont lose those too.

[davegri]

Moke, here's a bit of code that you might consider helpful to add functionality to form creation in your utility.
It will modify the form's textbox's label.captions to add spaces for readability. I always hated to manually modify all the labels to change LastName to Last Name, etc.

The code will essentially add a space before any capital letter in the caption, except for leading cap letter.
It also avoids multiple spaces, and attempts to avoid changing PK, ID and FK by not adding a space in the middle.

One procedure works on complete finished forms, the other can be used on the fly as each label caption is created by your utility code.

'---------------------------------------------------------------------------------------
' File   : basLabelTuner
' Author : davegri
' Date   : 7/3/2021
' Purpose: Change default form label names created by Access
'   Routine will modify label captions following "Camel" conventions.
'   Example: if label caption is LastName it will be changed to Last Name
'   ZipPostalCode will be changed to Zip Postal Code
'   Essentially every Capitol letter will be replaced by Space & Capital Letter (except the first).
'   Double spaces (and greater) in label captions will be reduced to one space.
'   Captions that contain "ID", "PK" or "FK" will not have a space added to center or prefix.
'---------------------------------------------------------------------------------------
Option Compare Database
Option Explicit


'---------------------------------------------------------------------------------------
' Method : fcnFormatAllLabels
' Author : davegri
' Date   : 6/29/2021
' Purpose: For existing form
'---------------------------------------------------------------------------------------
Public Function fcnFormatAllLabels(frmName As String)
    Dim Ctl As Control, frm As Form
    Dim C As String, i As Integer, QED As String
    DoCmd.Close acForm, frmName
    DoCmd.OpenForm frmName, acDesign
    Set frm = Forms(frmName)
    For Each Ctl In frm.Detail.Controls
        If Ctl.ControlType = acLabel Then
            C = Ctl.Caption
            For i = 65 To 90    'Cap A to cap Z
                C = fcnReplaceAll(C, Chr$(i), " " & Chr$(i))
                C = fcnReplaceAll(C, " I D", "ID")
                C = fcnReplaceAll(C, " P K", "PK")
                C = fcnReplaceAll(C, " F K", "FK")
                C = fcnReplaceAll(C, Space(2), Space(1))
            Next i
            Ctl.Caption = Trim(C)
        End If
    Next Ctl
    DoCmd.Close acForm, frm.Name, acSaveYes
End Function


'---------------------------------------------------------------------------------------
' Method : fcnFormatSingleLabel
' Author : davegri
' Date   : 6/29/2021
' Purpose:  Called by code while creating new form programatically.
'   arg is the field name (which normally becomes the textbox's label caption).
'---------------------------------------------------------------------------------------
Public Function fcnFormatSingleLabel(arg As String)

    Dim C As String, i As Integer
            C = arg
            For i = 65 To 90
                C = fcnReplaceAll(C, Chr$(i), " " & Chr$(i))
                C = fcnReplaceAll(C, " I D", "ID")
                C = fcnReplaceAll(C, " P K", "PK")
                C = fcnReplaceAll(C, " F K", "FK")
                C = fcnReplaceAll(C, Space(2), Space(1))
            Next i
            C = Trim(C)
    fcnFormatSingleLabel = C
End Function


Function fcnReplaceAll(ByVal Target As String, ByVal arg As String, ByVal NewArg As String) As String
    If InStr(1, NewArg, arg, vbBinaryCompare) > 0 Then
        Target = fcnReplaceAllOnce(Target, arg, NewArg)
    Else
        Do While InStr(1, Target, arg, vbBinaryCompare) > 0
            Target = fcnReplaceAllOnce(Target, arg, NewArg)
        Loop
    End If
    fcnReplaceAll = Target
End Function


Function fcnReplaceAllOnce(ByVal Target As String, ByVal arg As String, ByVal NewArg As String) As String
    Dim i As Long
    If fcnIsNothing(arg) Then
        fcnReplaceAllOnce = Target
    Else
        If StrComp(arg, NewArg, vbBinaryCompare) = 0 Then
            fcnReplaceAllOnce = Target
        Else
            i = InStr(1, Target, arg, vbBinaryCompare)
            Do While i > 0
                Target = Left(Target, i - 1) & NewArg & Mid(Target, i + Len(arg))
                i = i + Len(NewArg)
                i = InStr(i, Target, arg, vbBinaryCompare)
            Loop
        fcnReplaceAllOnce = Target
        End If
    End If
End Function


Function fcnIsNothing(ByVal Target As String) As Boolean
    If Target & "" = "" Then
        fcnIsNothing = True
    Else
        fcnIsNothing = False
    End If
End Function

[moke123]

Thanks Dave. That looks like it will come in handy. I'll give it a go.

Edit: Slight modification to match my needs and works great. Thanks again.