Microsoft Access Split Databases: Connection Strings

[@amedmesa814]

Absolutely amazing presentation and concept!

[@reyanthonysaguid1154]

Another great video. It is a step above the other Access Tutorials out there. Makes the Access application more professional looking indeed. Can I request a video on how to make an "Administrator" database for Access. thanks a bunch.

[@Accessjitsu]

+Rey Anthony Saguid What do you mean by an "Administrator" database?

[@reyanthonysaguid1154]

+Access Jitsu It is a database that can see the happenings of other databases like how many users are using a multi-user split database, who are those users, and other things. I currently have several split databases that I manage individually. I saw your videos just recently and utilized what I learned and it was a big boost, especially the custom ribbons.

[@Accessjitsu]

+Rey Anthony Saguid That could be fun. I'd like to finish the path on at the moment before embarking on something like that.

[@dushyanthooda]

The code is not listed. Can I request if the listing can be put please. Greatful

[@MrLupoNino]

Can you create a video how to connect tables to MSSQL with vba code? THank you!

[@ereswude]

Hi, I am trying to addapt it to refresh excel links also. I get error 3219 - do you know where the problem might be?

[@ritvikarora5181]

Website not showing code..

[@johndavy3820]

I am getting error Invalid use of Me keyword in Refreshlist ?? Can you offer suggestion? Thanks

[@ahmedalmulki]

I want to connect multiple BEs with one FE how can I do that is there any recommended video help me to do this ?

[@Accessjitsu]

You can do this. When you first connect to the back end, under the external data tab, choose Access. You can navigate to one back end for one table, then connect the next table and navigate to another backend for it.

[@brianwieland1669]

Thank you so much for your video series, I've learned so much from you. I was wondering though, how would I modify this so that if the backend password is changed, I can detect this through this form on the front end and reset the password? I am guessing that tdf.RefreshLink would fail or throw an error of some sorts, which should prompt me to change the password and then use that for the remainder of the loop. I am assuming that all of my tables only link to a single backend db.

[@Accessjitsu]

If you want to deal with the potential for the password of the backend changing, you'll need to make some changes to my code. First, I'm only trying to change the connection string, if the connectStr tdf.Connect. However, it might be the same, if you only change the password in the backend. Secondly, you'll need to remove the On error resume next statement right aboe the tdf.RefreshLink command. Then you'll get an error number of 3031 if the password in the connection string is wrong. You'll need to test for error 3031 and then display an input box or form to capture the new password and then use that in your connection string. yes, this code only links to a single backend.

[@brianwieland1669]

Thanks for the quick reply. I'm using both this and the code you provided in Part 5 and am attempting to modify the RefreshLinks function. This is the relevant portion I have so far: If ConnectStr tdf.Connect Then tdf.Connect = ConnectStr Err = 0 '' On Error Resume Next DoCmd.Echo True, "Linking to ... " & tdf.Name tdf.RefreshLink ' Relink the table. If Err.Number = 3031 Then '' Invalid password '' Open the UpdateDbPWD form to allow admin to change the front-end password MsgBox "Current database password has been changed. Please enter the updated database password." DoCmd.OpenForm "UpdateDbPWDF", acNormal, , , , acDialog, DbPWD NewDbPWD = Forms![UpdateDbPWDF]!VerifyNewDbPWD DoCmd.Close acForm, "UpdateDbPWDF" DbPWD = NewDbPWD ConnectStr = "MS Access;PWD=" & DbPWD & ";DATABASE=" & strFileName tdf.Connect = ConnectStr Err = 0 On Error Resume Next DoCmd.Echo True, "Linking to ... " & tdf.Name tdf.RefreshLink ' Relink the table. End If ElseIf Err 0 Then RefreshLinks = False Exit Function End If The "UpdateDbPWDF" form requests the current DB password and allows the admin to update the password. The above code should wait for the user to click on the "Update" button in UpdateDbPWDF, which really just hides the form so that the above code can get the value off of the VerifyNewDbPWD textbox, then close it. However, I am finding that it does not even open this form, it goes straight to error 3031 and then continues with the old password and does not link. Could you help on this one?

[@Accessjitsu]

Usually, the way we get at an error code is to have a label you can go to if an error is encountered. You then use an "on error goto [label]" statement to direct the code to your error handler. However, if you want to get at it directly in the code like this, then uncomment the 'on error resume next' statement. That will allow the code to 'fall through' to your if statement. With that statement commented, your code is going to either go to wherever you have your previous 'on error goto" directed, or if you don't have one, it will simply throw an error depending on your database settings. I just tried this in mine and it read the error in-line.

[@brianwieland1669]

I was able to transfer it into the On Error Goto [Label], but I did some debugging and realized that when it hits the If ConnectStr tdf.Connect, it is pulling from the links in the front end and thus the password from the front end, rather than comparing against the password that is on the backend. I know if the backend password changes, typically we have to delete all of the linked tables and re-link them with the new password. I'm hoping to go around that by simply prompting the user for the updated backend password, setting the updated password to DbPWD, and allowing it to automatically update for the remaining tables. I've modified a little to compare and see what is was using as the connection password. 'If the table has a connection string, it's a linked table If Len(tdf.Connect) > 0 Then 'extract the password from the old connection string i = InStr(tdf.Connect, "PWD=") j = InStr(i, tdf.Connect, ";") DbPWD = Mid(tdf.Connect, i + 4, j - i - 4) MsgBox "Extracted password: " & DbPWD, vbOKOnly, "RefreshLinks Password Extraction" MsgBox "tdf.Connect: " & tdf.Connect ConnectStr = "MS Access;PWD=" & DbPWD & ";DATABASE=" & strFileName If ConnectStr tdf.Connect Then tdf.Connect = ConnectStr Err = 0 On Error Resume Next DoCmd.Echo True, "Linking to ... " & tdf.Name tdf.RefreshLink ' Relink the table. If Err = 3031 Then MsgBox "Current database password has been changed. Please enter the updated database password." GoTo SubError ElseIf Err 0 And Err 3031 Then MsgBox "Password reset failed. Aborting...", vbCritical + vbOKOnly, "RefreshLinks Password Reset" RefreshLinks = False Exit Function End If End If End If

[@brianwieland1669]

Nevermind, I realized that "If ConnectStr tdfConnect" would always be false if only the backend password changed, as changing the backend password does not affect the connection strings on the front end.

[@nonsookafor722]

please can we have the vba code and secondly is it possible for users to edit or modify the data in the table from any location

[@Accessjitsu]

the code is here: accessjitsu.com/2015/12/23/microsoft-access-split-databases-connection-strings/ Any database that links to the table that is in the backend should be able to edit the data in the table.

[@stephenjones4135]

Using the following code makes the use of creating this form mute. RunCommand acCmdLinkedTableManager

[@sharathAlive]

bro dont know if i am dumb or is it too advanced im not able to use ur code on a new db either :(

[@vfxmastersthailand9330]

i'm getting an error: Ambiguous name detcted:cmdChange_Click does any knows why

[@Accessjitsu]

That suggests to me that you have more than one method by the name cmdChange_Click.