Relink back-end mdb from front-end Access form using VBA
0
My Access database is split into back and front-end.
I have created an Access form in the front-end that contains a textBox, a Browser button and a Relink button.
When I click Browse, file manager pops up to choose my .mdb file. After the file is choosen, the path to the file is displayed into the textBox.
What I want is when I press Relink button, it should take the path from textBox and link my back-end file to my front end.
Here is my code so far:
'browse button
Private Sub browseBtn_Click()
Dim objDialog As Object
set objDialog = Application.FileDialog(3)
With objDialog
.show
.AllowMultiSelect = False
If .SelectedItems.Count = 1 Then
'textFiled displays the path
Me.textField = .SelectedItems(1)
End If
End With
End Sub
'relink button
Private Sub linkBtn_Click()
Dim newConnection As String
Dim currentPath As String
currentPath = Me.textField
Dim tblDef As TableDef
tblDef.Connect = newConnection
tblDef.RefreshLink
End Sub
What is wrong with this?
database vba ms-access access-vba
edited Nov 21 '18 at 22:40
Parfait
53.3k94771
asked Nov 21 '18 at 21:52
mecnismmecnism
519
add a comment |
0
My Access database is split into back and front-end.
I have created an Access form in the front-end that contains a textBox, a Browser button and a Relink button.
When I click Browse, file manager pops up to choose my .mdb file. After the file is choosen, the path to the file is displayed into the textBox.
What I want is when I press Relink button, it should take the path from textBox and link my back-end file to my front end.
Here is my code so far:
'browse button
Private Sub browseBtn_Click()
Dim objDialog As Object
set objDialog = Application.FileDialog(3)
With objDialog
.show
.AllowMultiSelect = False
If .SelectedItems.Count = 1 Then
'textFiled displays the path
Me.textField = .SelectedItems(1)
End If
End With
End Sub
'relink button
Private Sub linkBtn_Click()
Dim newConnection As String
Dim currentPath As String
currentPath = Me.textField
Dim tblDef As TableDef
tblDef.Connect = newConnection
tblDef.RefreshLink
End Sub
What is wrong with this?
database vba ms-access access-vba
edited Nov 21 '18 at 22:40
Parfait
53.3k94771
asked Nov 21 '18 at 21:52
mecnismmecnism
519
sorry, i've edited it
– mecnism
Nov 21 '18 at 22:31
Why isn't the back-end already linked in front-end? Usually, a user should not have to regularly search and connect the data source. Also, your last question is something you should tell us. What does code currently do? Undesired results? Error?
– Parfait
Nov 21 '18 at 22:42
1
Need to loop through TableDefs collection. Review microsoft.com/en-us/microsoft-365/blog/2012/08/03/….
– June7
Nov 21 '18 at 23:42
add a comment |
0
0
0
My Access database is split into back and front-end.
I have created an Access form in the front-end that contains a textBox, a Browser button and a Relink button.
When I click Browse, file manager pops up to choose my .mdb file. After the file is choosen, the path to the file is displayed into the textBox.
What I want is when I press Relink button, it should take the path from textBox and link my back-end file to my front end.
Here is my code so far:
'browse button
Private Sub browseBtn_Click()
Dim objDialog As Object
set objDialog = Application.FileDialog(3)
With objDialog
.show
.AllowMultiSelect = False
If .SelectedItems.Count = 1 Then
'textFiled displays the path
Me.textField = .SelectedItems(1)
End If
End With
End Sub
'relink button
Private Sub linkBtn_Click()
Dim newConnection As String
Dim currentPath As String
currentPath = Me.textField
Dim tblDef As TableDef
tblDef.Connect = newConnection
tblDef.RefreshLink
End Sub
What is wrong with this?
database vba ms-access access-vba
edited Nov 21 '18 at 22:40
Parfait
53.3k94771
asked Nov 21 '18 at 21:52
mecnismmecnism
519
My Access database is split into back and front-end.
I have created an Access form in the front-end that contains a textBox, a Browser button and a Relink button.
When I click Browse, file manager pops up to choose my .mdb file. After the file is choosen, the path to the file is displayed into the textBox.
What I want is when I press Relink button, it should take the path from textBox and link my back-end file to my front end.
Here is my code so far:
'browse button
Private Sub browseBtn_Click()
Dim objDialog As Object
set objDialog = Application.FileDialog(3)
With objDialog
.show
.AllowMultiSelect = False
If .SelectedItems.Count = 1 Then
'textFiled displays the path
Me.textField = .SelectedItems(1)
End If
End With
End Sub
'relink button
Private Sub linkBtn_Click()
Dim newConnection As String
Dim currentPath As String
currentPath = Me.textField
Dim tblDef As TableDef
tblDef.Connect = newConnection
tblDef.RefreshLink
End Sub
What is wrong with this?
database vba ms-access access-vba
database vba ms-access access-vba
edited Nov 21 '18 at 22:40
Parfait
53.3k94771
asked Nov 21 '18 at 21:52
mecnismmecnism
519
edited Nov 21 '18 at 22:40
Parfait
53.3k94771
asked Nov 21 '18 at 21:52
mecnismmecnism
519
edited Nov 21 '18 at 22:40
Parfait
53.3k94771
edited Nov 21 '18 at 22:40
Parfait
53.3k94771
edited Nov 21 '18 at 22:40
Parfait
53.3k94771
53.3k94771
asked Nov 21 '18 at 21:52
mecnismmecnism
519
asked Nov 21 '18 at 21:52
mecnismmecnism
519
asked Nov 21 '18 at 21:52
mecnismmecnism
519
519
sorry, i've edited it
– mecnism
Nov 21 '18 at 22:31
Why isn't the back-end already linked in front-end? Usually, a user should not have to regularly search and connect the data source. Also, your last question is something you should tell us. What does code currently do? Undesired results? Error?
– Parfait
Nov 21 '18 at 22:42
1
Need to loop through TableDefs collection. Review microsoft.com/en-us/microsoft-365/blog/2012/08/03/….
– June7
Nov 21 '18 at 23:42
add a comment |
sorry, i've edited it
– mecnism
Nov 21 '18 at 22:31
Why isn't the back-end already linked in front-end? Usually, a user should not have to regularly search and connect the data source. Also, your last question is something you should tell us. What does code currently do? Undesired results? Error?
– Parfait
Nov 21 '18 at 22:42
1
Need to loop through TableDefs collection. Review microsoft.com/en-us/microsoft-365/blog/2012/08/03/….
– June7
Nov 21 '18 at 23:42
sorry, i've edited it
– mecnism
Nov 21 '18 at 22:31
sorry, i've edited it
– mecnism
Nov 21 '18 at 22:31
Why isn't the back-end already linked in front-end? Usually, a user should not have to regularly search and connect the data source. Also, your last question is something you should tell us. What does code currently do? Undesired results? Error?
– Parfait
Nov 21 '18 at 22:42
Why isn't the back-end already linked in front-end? Usually, a user should not have to regularly search and connect the data source. Also, your last question is something you should tell us. What does code currently do? Undesired results? Error?
– Parfait
Nov 21 '18 at 22:42
1
1
Need to loop through TableDefs collection. Review microsoft.com/en-us/microsoft-365/blog/2012/08/03/….
– June7
Nov 21 '18 at 23:42
Need to loop through TableDefs collection. Review microsoft.com/en-us/microsoft-365/blog/2012/08/03/….
– June7
Nov 21 '18 at 23:42
add a comment |
1 Answer
1
active
oldest
votes
0
I've figure it out in the end, here is the full code:
Private Sub browseBtn_Click()
Dim objDialog As Object
Set objDialog = Application.FileDialog(3)
With objDialog
.title = "Please select the backend file"
.AllowMultiSelect = False
.Show
If .SelectedItems.Count = 1 Then
Me.textField = .SelectedItems(1)
End If
End With
End Sub
Private Sub linkBtn_Click()
RefreshLinks (Me.textField)
End Sub
Public Function RefreshLinks(strFilename As String)
Dim dbs As dao.Database
Dim tdf As TableDef
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
If Len(tdf.Connect) > 0 Then
tdf.Connect = ";DATABASE=" & strFilename
Err = 0
On Error Resume Next
tdf.refreshlink
If Err <> 0 Then
RefreshLinks = False
Exit Function
End If
End If
Next tdf
RefreshLinks = True
End Function
answered Nov 22 '18 at 16:49
mecnismmecnism
519
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53420967%2frelink-back-end-mdb-from-front-end-access-form-using-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
I've figure it out in the end, here is the full code:
Private Sub browseBtn_Click()
Dim objDialog As Object
Set objDialog = Application.FileDialog(3)
With objDialog
.title = "Please select the backend file"
.AllowMultiSelect = False
.Show
If .SelectedItems.Count = 1 Then
Me.textField = .SelectedItems(1)
End If
End With
End Sub
Private Sub linkBtn_Click()
RefreshLinks (Me.textField)
End Sub
Public Function RefreshLinks(strFilename As String)
Dim dbs As dao.Database
Dim tdf As TableDef
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
If Len(tdf.Connect) > 0 Then
tdf.Connect = ";DATABASE=" & strFilename
Err = 0
On Error Resume Next
tdf.refreshlink
If Err <> 0 Then
RefreshLinks = False
Exit Function
End If
End If
Next tdf
RefreshLinks = True
End Function
answered Nov 22 '18 at 16:49
mecnismmecnism
519
add a comment |
0
I've figure it out in the end, here is the full code:
Private Sub browseBtn_Click()
Dim objDialog As Object
Set objDialog = Application.FileDialog(3)
With objDialog
.title = "Please select the backend file"
.AllowMultiSelect = False
.Show
If .SelectedItems.Count = 1 Then
Me.textField = .SelectedItems(1)
End If
End With
End Sub
Private Sub linkBtn_Click()
RefreshLinks (Me.textField)
End Sub
Public Function RefreshLinks(strFilename As String)
Dim dbs As dao.Database
Dim tdf As TableDef
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
If Len(tdf.Connect) > 0 Then
tdf.Connect = ";DATABASE=" & strFilename
Err = 0
On Error Resume Next
tdf.refreshlink
If Err <> 0 Then
RefreshLinks = False
Exit Function
End If
End If
Next tdf
RefreshLinks = True
End Function
answered Nov 22 '18 at 16:49
mecnismmecnism
519
add a comment |
0
0
0
I've figure it out in the end, here is the full code:
Private Sub browseBtn_Click()
Dim objDialog As Object
Set objDialog = Application.FileDialog(3)
With objDialog
.title = "Please select the backend file"
.AllowMultiSelect = False
.Show
If .SelectedItems.Count = 1 Then
Me.textField = .SelectedItems(1)
End If
End With
End Sub
Private Sub linkBtn_Click()
RefreshLinks (Me.textField)
End Sub
Public Function RefreshLinks(strFilename As String)
Dim dbs As dao.Database
Dim tdf As TableDef
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
If Len(tdf.Connect) > 0 Then
tdf.Connect = ";DATABASE=" & strFilename
Err = 0
On Error Resume Next
tdf.refreshlink
If Err <> 0 Then
RefreshLinks = False
Exit Function
End If
End If
Next tdf
RefreshLinks = True
End Function
answered Nov 22 '18 at 16:49
mecnismmecnism
519
I've figure it out in the end, here is the full code:
Private Sub browseBtn_Click()
Dim objDialog As Object
Set objDialog = Application.FileDialog(3)
With objDialog
.title = "Please select the backend file"
.AllowMultiSelect = False
.Show
If .SelectedItems.Count = 1 Then
Me.textField = .SelectedItems(1)
End If
End With
End Sub
Private Sub linkBtn_Click()
RefreshLinks (Me.textField)
End Sub
Public Function RefreshLinks(strFilename As String)
Dim dbs As dao.Database
Dim tdf As TableDef
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
If Len(tdf.Connect) > 0 Then
tdf.Connect = ";DATABASE=" & strFilename
Err = 0
On Error Resume Next
tdf.refreshlink
If Err <> 0 Then
RefreshLinks = False
Exit Function
End If
End If
Next tdf
RefreshLinks = True
End Function
answered Nov 22 '18 at 16:49
mecnismmecnism
519
answered Nov 22 '18 at 16:49
mecnismmecnism
519
answered Nov 22 '18 at 16:49
mecnismmecnism
519
answered Nov 22 '18 at 16:49
mecnismmecnism
519
519
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53420967%2frelink-back-end-mdb-from-front-end-access-form-using-vba%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
sorry, i've edited it
– mecnism
Nov 21 '18 at 22:31
Why isn't the back-end already linked in front-end? Usually, a user should not have to regularly search and connect the data source. Also, your last question is something you should tell us. What does code currently do? Undesired results? Error?
– Parfait
Nov 21 '18 at 22:42
1
Need to loop through TableDefs collection. Review microsoft.com/en-us/microsoft-365/blog/2012/08/03/….
– June7
Nov 21 '18 at 23:42