Basic backend table modification questions ...

[LarryE]

I use the following code to select a backend file and connect to it:

Public Function ConnectFileDialog()
On Error GoTo ConnectFileDialog_Error
Dim ConnectDataFileDialog As FileDialog
Set ConnectDataFileDialog = Application.FileDialog(msoFileDialogFilePicker)
Dim SelectedFile As Variant
Dim db As DAO.Database
Set db = CurrentDb
With ConnectDataFileDialog
    .AllowMultiSelect = False
    .Title = "Select A Backend File"
    .ButtonName = "Connect"
    .Filters.Clear
    .Filters.Add "Access Files", "*.accdb", 1
    .FilterIndex = 1
    If .Show = -1 Then 'If user selected a file
        For Each SelectedFile In .SelectedItems
            'MsgBox SelectedFile
            Dim tdf As DAO.TableDef
            Dim BackEnd As String
            BackEnd = ";Database=" & SelectedFile & ""
            If Len(BackEnd) > 1 Then
                GoTo FinishConnection
                Exit Function
            Else
                Exit Function
            End If
FinishConnection:
        For Each tdf In db.TableDefs
            If Len(Left$(tdf.Connect, 1)) > 0 Then
                Set tdf = db.TableDefs(tdf.Name)
                    tdf.Connect = BackEnd
                    tdf.RefreshLink
            End If
        Next tdf
        Next
    Else 'If user cancelled
        Set FileOpenDialog = Nothing
        If Forms.Count > 0 Then
            Exit Function
        Else
            DoCmd.Quit
        End If
    End If
    Set FileOpenDialog = Nothing
End With
Exit Function
ConnectFileDialog_Error:
DoCmd.CancelEvent
'msgbox Err.DESCRIPTION
Resume Next
Exit Function
End Function

Maybe it will help you.

Also, ACCESS keeps the current connection information in the MSysObjects table. It can be viewed with this query:

SELECT DISTINCT MSysObjects.Database
FROM MSysObjects
GROUP BY MSysObjects.Database, MSysObjects.ParentId, MSysObjects.Name, MSysObjects.Type
HAVING (((MSysObjects.Name) Not Like "*TMP*") AND ((MSysObjects.Type)=6));

I named my query CurrentConnection

 

[Marshall Brooks]

Made some progress, but I still don't understand what I am doing ...

I was trying to use your database and link to my BE from it. Didn't work.

I copied your frmReLingJetOrACETables into my database and it SEEMS to work, but it either doesn't or I'm not using it properly.

I changed the form to load my switchboard form on close.

I also went into design view and changed txtOldPathName to Enabled so that I could copy the info from it, but left it locked so it can't be edited.

I have my BE files in three locations -
the production backend in ...network path\back end\database_be.accdb.
the test backend in ...network path\back end test\database_be.accdb.
a copy of the test backend in \\My Documents\Access Development\database_be.accdb (which no other users can access).

When I opened the form, it was showing the second link for the current back end (under New DB Name) which was correct.

I changed that to the path to the Development Folder and clicked Relink and it said everything worked fine.

I changed that to the production backend and it said it worked fine, but it didn't seem to have changed.

If I look at linked table manager, both the test links are shown, but neither one is checked.

 

[Marshall Brooks]

@LarryE - Your code is working for me - Much appreciated. I made two changes to it:

• In two places, I had to change "Set File Open Diaglog = Nothing" to "Set ConnectDataFileDialog = Nothing"
• As I said, the production back-end could be domain.subdomainA or domain.subdomainB and it was hard to remember the one I wanted using the browse function, so under the With Block, I added .InitialFileName = <ProductionBackEnd>.

I also changed my switchboard form activation code so that if I am using the production BE, the background color is dark gray and if I am using any other back end, the switchboard background color is light gray. (Saves me from releasing the front end with the wrong BE linked - although since the test back end is local, it would just give an error to other users and I'd have to release a quick update.

 

[Marshall Brooks]

@Pat Hartman - That looks more like what I was originally envisioning, but I have @LarryE's code working now and it does everything I wanted/Need. (i.e. it will select the production BE by default and I can browse to whatever test front end I choose, and I get visual confirmation (background color) if I am not connected to the production BE.

As a plus - when I changed the backend using the linked table manager, each form would initially open (or more accurately fail to open) with something like "Error 3034: Reserved Error - There is no message for this error." That no longer happens if I change the backend using VBA.

 

[Isaac]

I should know the answers to these, but I wanted to confirm them.

• I want to add two new tables to the back end of my database. Usually, I would get exclusive access to the BE in order to do this. Is this a firm requirement, or can other users have the back end open, since there is no way for the new tables to be accessed since they aren't linked to the FE yet.
• I know if I want to add or modify fields of a linked BE table, I need exclusive access to the BE - or at least I've always done it that way.
• Some of my tables are stored in the backend and then the data in the table is copied into local tables in the FE on startup. I'm assuming that if I ever wanted to add/modify fields to these tables, that is when I would have to have exclusive access to the BE, update the tables in the BE, update the FE, and then distribute and have everyone update to the new FE at the same time the updated BE was available, correct?
• Adding tables to the FE is simple, except nobody else can use them until the new FE is released?

Thanks in advance!

Try to stay in the habit of calling downtime for your app any time you need to edit the back end, as Pat said, do it when nobody is using FE.
That said, you can generally perform any editing operations that don't involve tables the FE is holding open or transacting on in any way (like you can add new tables).

But for example, you may wish to THEN perform other standard things after adding the tables, like a C&R, which you won't be able to do while FE is open anyway.

I like to maintain a message in my FE that scrolls a little banner any time I hvae to call downtime to warn them.
I also have a FE menu form with a timer that checks for force-closing the app any time. If I change a 0 to a 1 in a certain text file, their FE apps will instantly close - well after a minute or two, as I don't want to run the timer function too often. YES I realize this is the last resort, obviously, but there has been at least a couple times when I was glad I had it - someone left the db open for days and I needed everyone out like yesterday - changed the 0 to a 1 in the text file and watch that record locking file disappear! Beauty.

 

[Marshall Brooks]

@Issaac - Thanks - I have all of that in place already - but as you've said, there are times that I've been very glad I did!!!

 

[Isaac]

@Issaac - Thanks - I have all of that in place already - but as you've said, there are times that I've been very glad I did!!!

Nice job! Not to sound too hitler-ish, but control is pretty fun! App dev when you control A-Z, input to data storage, is a highly satisfying job - the most ever I've had, IMO. I like my pay now in sql server dev more than I did in the Access world, but I recall my days in the Access world (access fe, sql server be), as the most satisfying work I've ever done by a long shot - not because of Access per se, but because I controlled the design of everything which worked together nicely - FE input, sql back end.

 

[Marshall Brooks]

Concur - it's a blast figuring out how I think things SHOULD work and then seeing it actually happen!!!

 

[Marshall Brooks]

The timer function is ALWAYS running, counting down the interval to determine when to open again.

Unless you set .TimerInterval = 0

 

[Isaac]

The timer function is ALWAYS running, counting down the interval to determine when to open again.

What I meant of course was the function that I wrote in the part where the timer triggered 'something' - because the 'something' ran for a couple seconds to read the file

 

[Marshall Brooks]

I knew what @Issaac meant, but not everyone might ...

I did the same thing. I think the timer has a two-minute interval and then, if the "trigger" is there, the database gives you three minutes before it closes.

Actually, it's complicated - I think it checks every two minutes for the trigger, then if the trigger is present, it starts the three minute shutdown counter and then it also checks every 10 seconds to be sure the trigger is still present. (That way if I somehow accidentally trigger the shutdown, I have at least 3 minutes to cancel it.)

Also - not sure it matters, but I don't check for a 0 or a 1 in the file. I just check for a file with a certain name in a certain folder. (It's actually an empty text file). I'm not sure, but that is likely faster than having to open and read the file.

 

[Isaac]

Yeah, I mean ... what's the use of a timer function if you're not going to 'do' anything when the timer strikes?

Timer related functions are normally used to 'do' something every N seconds/minutes etc. I assumed everyone knew that, but thanks MB for clarifying

 

[Isaac]

Also - not sure it matters, but I don't check for a 0 or a 1 in the file. I just check for a file with a certain name in a certain folder. (It's actually an empty text file). I'm not sure, but that is likely faster than having to open and read the file.

Probably in nanoseconds, yes.

I'll confess to , quite often, opting for a process or design that is easier for someone else to make sense of compared to the fastest thing, I liked the 1 and 0 because at one point I had to instruct someone else to change it, they grasped that concept very quickly.

 

[Marshall Brooks]

Clarity and understandability are good things.

In my case, it's a file with the backend named something like "Down4Maintenance.txt". There is always a file in the folder (should be) named nDown4Maintenance.txt". So if I had to instruct someone else, it's simple to do.

The article where I found it - https://learn.microsoft.com/en-us/office/troubleshoot/access/shut-down-custom-application-remotely - recommended naming it "Chkfile.ocx", which I thought was NOT a good idea!!!

(And the initial website I tried wanted to use a value in a field in a back end table - which is not a good idea if I need to shut it down b/c it is locked up and I can't access it.

 

[Isaac]

chkfile.ocx, that's interesting, unless their instructions were for a dll type of thingy.

 

[Marshall Brooks]

I think they just didn't fully think it through. If you look at the article, it's actually a text file and all they use is VBA to check if it exists.

Another thing I didn't like with their example:

• Their code shuts down the database UNLESS the file is present. So all it takes is someone saying "What is chkfile.ocz? Looks unimportant, let's delete it!" - and everyone is locked out of the database.
• My code shuts down the database IF the flag file is present. Much less likely of someone accidentally adding a file that just happens to be in the correct folder with the correct name.

(Also - I had help from another DB admin who used similar code, I didn't just stumble to all of the above.)

 

[Isaac]

Their code shuts down the database UNLESS the file is present. So all it takes is someone saying "What is chkfile.ocz? Looks unimportant, let's delete it!" - and everyone is locked out of the database

maybe it's the same people as those guys who write triple-boolean-negatives in one line of code in order to look cool, then dare people to question it, which nobody does because everybody instinctively worries that if they question it, people will think (rightly so) that they had a damn hard time figuring it out. which of course, they did, because we are human beings. ha ha.

I vote for readable code every.single.time.

 

[gemma-the-husky]

@Isaac

What do you mean by triple boolean negatives?

Is that the a = b or not c sort of construct, using boolean operators.

It's perfectly readable, but the meaning may not be apparent to those who are not used to seeing that sort of code.

 

[Marshall Brooks]

Any reason to not use a triple not?

If logical not ! will switch a boolean value from false to true or true to false and a double not !! will cast a value from one primitive type to boolean is there any reason one can not use a logic...

softwareengineering.stackexchange.com

I had to google it, and it's basically a javascript term. I'm not sure it applies in VBA, but ... If it applies, it would work something like this:

Single Not - Valid
If Not IsNull(FieldA) Then

Double Not - same as If Is Null - (in VBA)
If Not (Not IsNull(FieldA)) Then

Triple Not - Same as Single Not
If Not (Not (Not IsNull(FieldA))) Then

Actually, I'll show some bias here, but I suspect it is invalid (syntax error) in VBA and works in JS. VBA is a bit more careful in the compiler and JS is a bit more forgiving. (JS is likely to run without errors regardless of what you type, even if it gives the incorrect result. VBA is more likely to fail to compile, but more likely to run correctly if it does compile.)

OT, but keep in mind Javascript uses a lot of double and triple operators - https://www.guru99.com/difference-e... is used for,datatype and compares two values.

 

[Isaac]

@Isaac

What do you mean by triple boolean negatives?

Is that the a = b or not c sort of construct, using boolean operators.

It's perfectly readable, but the meaning may not be apparent to those who are not used to seeing that sort of code.

It's far less readable when you're counting milliseconds that the brain has to slow or not slow - and I meant the ones a bit more complex as Marshall indicated below. Whether you're used to seeing it or not.

You can also increment variables, say, by 1, in sql server like

set @int += 1

But I never will, because it's less readable in my opinion, and has absolutely no redeeming value.