Basic backend table modification questions ...

[Pat Hartman]

users "think" they are changing the production data rather than the copy data

This would NEVER happen if you used a local version of the BE.

I have to log pretty carefully what I've changed, so that I don't make changes to the COPY BE that work fine and then get errors when I link to the production BE b/c it doesn't have the same changes.

That takes discipline. Making DDL is the best solution. Make the DDL FIRST. Then use it to make the actual change so you know the DDL works. Just keep accumulating it in a code module or table in the FE. I offered another solution that is pretty easy to use if you have SQL Server installed and are willing to take the step of uploading your current BE to SQL server so it can be compared to the previous version and the tool will generate DDL for you. I rely on this tool because I have it since most of my BE's are SQL Server anyway. It's a whole lot easier than making "notes to self".

 

[Marshall Brooks]

This would NEVER happen if you used a local version of the BE.

Indeed! I hadn't considered that, but it makes perfect sense. Right now, the copy BE is on the network different subfolder. The production Database BE is in a folder named "Back End" and the development copy is in a folder called "Back End Test". I doubt the other users know where it is, but I think they DO have access to it.

Let me ask you something about the linked Table Manager:

I'm hiding the network path, but you can see that the box is unchecked, but the database still reads from the backend. I used the folder names above, b/c I can just click Edit and Add or Remove Test and be at the correct level.

The reason I did it this way is b/c there are multiple networks paths that might work. For example - my path might be \\domain.subdomain1.company.com\ ...

S:\ might work for me, but not if users have their network paths named differently. \\domain.subdomain2.company.com re-directs to the same files, but might be slower or less reliable.

I've had times that I just browsed for the folder and ended up with both subdomain1 and subdomain2 listed.

An obvious solution is to copy the production path and the development (local) path to Notepad and copy and paste as needed.

Is there a way to have both listed? I.e. could I click add and add "C:\Users\MyUserName\Documents\CopyBe.accdb and then whichever one is checked is used by the development FE?

That takes discipline. Making DDL is the best solution. Make the DDL FIRST.

I need to look more into how to use DDL. I can find the previous links, just haven't gotten to researching it yet.

 

[ebs17]

Structural changes on the backend
Here you will find a small collection of instructions from DDL, DAO and ADOX.
It always makes sense to check a point first and then act accordingly
=> if FieldX does not exist in tableY, then create it

This means you can run such code as often as you like without errors and then accumulate as many actions as you want.

 

[Marshall Brooks]

Structural changes on the backend
Here you will find a small collection of instructions from DDL, DAO and ADOX.

Assuming I spoke or read German, which I do not ...

 

[Pat Hartman]

Users should NEVER be relinking the BE. You need to move the replacement FE to the "master" folder on the server and do the relink there. Using UNC instead of specific drive paths solves a lot of problems so that is a good idea, but for your local testing, linking to C: is fine. I have my owh relink tool which I've attached but there are others. Usually, when I add this form to an application, I create a text field on the form that has the UNC link I want to use for production so it makes it easy for me to switch when I'm ready to distribute the new FE. I just copy the string and paste it.

 

[Marshall Brooks]

I never said users were re-linking to the BE. "I" have to sometimes and I've been tripped up doing it incorrectly.

Nifty form and good suggestion on the text field in the database.

Thank you!!!

 

[Pat Hartman]

I made the form years ago when I was working with an application that had links to two different databases. It was pretty nearly impossible to get the relinking right using the old version of the Access relinker. This solved the problem so I add it to all applications for my own convenience even though it never shows up on any menu for the user to access. I didn't look closely but I think it also works for SQL Server and Excel files. If not, I may be able to find a newer version that does.

 

[gemma-the-husky]

I would never use the internal table link manager. I store the necessary links in the front end, and rebuild them if any are missing as part of the start up process, all in code.

 

[Pat Hartman]

The convenience of using a relinking form becomes obvious when you have multiple BE's connected to the same FE AND you have multiple versions of staging libraries. Say two BE's and three staging libraries (unit test, system test, production).

 

[Marshall Brooks]

@Pat Hartman - Not trying to look stupid, but I was playing with the relink form example, and I can't see how to get it to work ...
I open the database and I get:

I click OK and I get:

If I paste the development BE in the New DB Name Field, nothing happens. If I click Browse, I get the "A file was not selected" message. I don't see a way to edit or change the Old DB Name fields. The Relink buttons give me an "Please select a New DB Name before clicking the Relink button."

Also - I don't want you to remake the form, but for my use, what I would like to see is the current BE listed on the New DB Name Field, and then the first text box would be the production BE location and the second text box would be the test BE location, and relink would connect to the selected BE and disconnect from the other one.

 

[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.

 

[Pat Hartman]

The form works ONE table at a time. The picture shows three of MY databases which is obviously not going to work for you.

Start by deleting the existing links to the unknown tables and then linking to a few of your own.

Now, YOUR databases will show up in the list.

To work the form, decide which of the databases in the list that you want to relink.
Use the brows button to pick the new target folder.
Press the relink button next to the database you want to replace.

Also - I don't want you to remake the form, but for my use, what I would like to see is the current BE listed on the New DB Name Field, and then the first text box would be the production BE location and the second text box would be the test BE location, and relink would connect to the selected BE and disconnect from the other one.

That is not the concept of this form. This form is intended to support applications that link to multiple BEs at one time. I have a different application that I used for a more specific ODBC situation. It used a table to hold all the options. The picture shows six. If you look, you will see that it is actually two databases but three testing locations. In this form you click on the "target" row. If you click on The fifth option - Replicate, QA, the code finds all tables linked to the replicate database and switches them to the QA connection string. If the Replicate database tables were already linked to QA, clicking on Replicate, QA just refreshed the links since you weren't actually relinking them.

The app contains three forms. This is a picture of the relink form. It displays the list of rows in the tblConnectionStrings for you to choose from. This may be more like what you are interested in

 

[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!!!

 

[Pat Hartman]

as I don't want to run the timer function too often.

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