MS Access: Use a listbox to select and import table from a different Access database
I have a main Access db that exports certain tables to a different Access db (called Results_backup). I'd like to have a way to import one of those tables from Results_backup into the main db, other than expecting the user to go to External Data and so on.
Ideally I'd have a list box on my form that shows the user all of the tables in Results_backup that have "ETR" in the name. Then the user could select one of the tables in the list box, click a button, and import that table into the main db.
So far, I'm working from this:
Private Sub Form_Load()
Dim AccObject as Access.AccessObject
For each accObject in CurrentData.AllTables
If accObject like "*ETR*" then
me.listobjects.additem "ETR: " & accobject.name
End if
Next
End Sub
This only brings in tables from the current database (CurrentData.AllTables). So is there a way to do this on a different database, not the active database?
Thanks in advance for any guidance!
ms-access
asked Nov 22 '18 at 2:50
DrLDrL
1319
add a comment |
I have a main Access db that exports certain tables to a different Access db (called Results_backup). I'd like to have a way to import one of those tables from Results_backup into the main db, other than expecting the user to go to External Data and so on.
Ideally I'd have a list box on my form that shows the user all of the tables in Results_backup that have "ETR" in the name. Then the user could select one of the tables in the list box, click a button, and import that table into the main db.
So far, I'm working from this:
Private Sub Form_Load()
Dim AccObject as Access.AccessObject
For each accObject in CurrentData.AllTables
If accObject like "*ETR*" then
me.listobjects.additem "ETR: " & accobject.name
End if
Next
End Sub
This only brings in tables from the current database (CurrentData.AllTables). So is there a way to do this on a different database, not the active database?
Thanks in advance for any guidance!
ms-access
asked Nov 22 '18 at 2:50
DrLDrL
1319
add a comment |
I have a main Access db that exports certain tables to a different Access db (called Results_backup). I'd like to have a way to import one of those tables from Results_backup into the main db, other than expecting the user to go to External Data and so on.
Ideally I'd have a list box on my form that shows the user all of the tables in Results_backup that have "ETR" in the name. Then the user could select one of the tables in the list box, click a button, and import that table into the main db.
So far, I'm working from this:
Private Sub Form_Load()
Dim AccObject as Access.AccessObject
For each accObject in CurrentData.AllTables
If accObject like "*ETR*" then
me.listobjects.additem "ETR: " & accobject.name
End if
Next
End Sub
This only brings in tables from the current database (CurrentData.AllTables). So is there a way to do this on a different database, not the active database?
Thanks in advance for any guidance!
ms-access
asked Nov 22 '18 at 2:50
DrLDrL
1319
I have a main Access db that exports certain tables to a different Access db (called Results_backup). I'd like to have a way to import one of those tables from Results_backup into the main db, other than expecting the user to go to External Data and so on.
Ideally I'd have a list box on my form that shows the user all of the tables in Results_backup that have "ETR" in the name. Then the user could select one of the tables in the list box, click a button, and import that table into the main db.
So far, I'm working from this:
Private Sub Form_Load()
Dim AccObject as Access.AccessObject
For each accObject in CurrentData.AllTables
If accObject like "*ETR*" then
me.listobjects.additem "ETR: " & accobject.name
End if
Next
End Sub
This only brings in tables from the current database (CurrentData.AllTables). So is there a way to do this on a different database, not the active database?
Thanks in advance for any guidance!
ms-access
ms-access
asked Nov 22 '18 at 2:50
DrLDrL
1319
asked Nov 22 '18 at 2:50
DrLDrL
1319
edited Nov 22 '18 at 17:31
DrL
asked Nov 22 '18 at 2:50
DrLDrL
1319
asked Nov 22 '18 at 2:50
DrLDrL
1319
asked Nov 22 '18 at 2:50
DrLDrL
1319
1319
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
There are several ways:
1. You can query it from system table
SELECT [name]
FROM msysObjects IN 'b:Results_backup.mdb'
WHERE [name] Like '*ETR*' AND Type=1;
You can modify your sample code
Private Sub Form_Load()
Dim tdTable As TableDef
Dim DB as Database
Set DB = OpenDatabase("b:Results_backup.mdb")
For Each tdTable In DB.TableDefs
If tdTable.Name like "*ETR*" then
me.listobjects.additem "ETR: " & tdTable.Name
End if
Next
' clean up
DB.close
Set DB = nothing
End Sub
answered Nov 23 '18 at 6:21
4dmonster4dmonster
2,45111021
Thank you. The first solution works perfectly well for me so I'm giving that a correct answer, but the second option isn't working for me---I keep getting a compile error (method or data member not found) on the DB.AllTables. Any idea what that's about?
– DrL
Nov 23 '18 at 21:26
@DrL sorry, there was error in second way. I've corrected it.
– 4dmonster
Nov 26 '18 at 5:42
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
});
}
});
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%2f53423208%2fms-access-use-a-listbox-to-select-and-import-table-from-a-different-access-data%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
There are several ways:
1. You can query it from system table
SELECT [name]
FROM msysObjects IN 'b:Results_backup.mdb'
WHERE [name] Like '*ETR*' AND Type=1;
You can modify your sample code
Private Sub Form_Load()
Dim tdTable As TableDef
Dim DB as Database
Set DB = OpenDatabase("b:Results_backup.mdb")
For Each tdTable In DB.TableDefs
If tdTable.Name like "*ETR*" then
me.listobjects.additem "ETR: " & tdTable.Name
End if
Next
' clean up
DB.close
Set DB = nothing
End Sub
answered Nov 23 '18 at 6:21
4dmonster4dmonster
2,45111021
Thank you. The first solution works perfectly well for me so I'm giving that a correct answer, but the second option isn't working for me---I keep getting a compile error (method or data member not found) on the DB.AllTables. Any idea what that's about?
– DrL
Nov 23 '18 at 21:26
@DrL sorry, there was error in second way. I've corrected it.
– 4dmonster
Nov 26 '18 at 5:42
add a comment |
There are several ways:
1. You can query it from system table
SELECT [name]
FROM msysObjects IN 'b:Results_backup.mdb'
WHERE [name] Like '*ETR*' AND Type=1;
You can modify your sample code
Private Sub Form_Load()
Dim tdTable As TableDef
Dim DB as Database
Set DB = OpenDatabase("b:Results_backup.mdb")
For Each tdTable In DB.TableDefs
If tdTable.Name like "*ETR*" then
me.listobjects.additem "ETR: " & tdTable.Name
End if
Next
' clean up
DB.close
Set DB = nothing
End Sub
answered Nov 23 '18 at 6:21
4dmonster4dmonster
2,45111021
Thank you. The first solution works perfectly well for me so I'm giving that a correct answer, but the second option isn't working for me---I keep getting a compile error (method or data member not found) on the DB.AllTables. Any idea what that's about?
– DrL
Nov 23 '18 at 21:26
@DrL sorry, there was error in second way. I've corrected it.
– 4dmonster
Nov 26 '18 at 5:42
add a comment |
There are several ways:
1. You can query it from system table
SELECT [name]
FROM msysObjects IN 'b:Results_backup.mdb'
WHERE [name] Like '*ETR*' AND Type=1;
You can modify your sample code
Private Sub Form_Load()
Dim tdTable As TableDef
Dim DB as Database
Set DB = OpenDatabase("b:Results_backup.mdb")
For Each tdTable In DB.TableDefs
If tdTable.Name like "*ETR*" then
me.listobjects.additem "ETR: " & tdTable.Name
End if
Next
' clean up
DB.close
Set DB = nothing
End Sub
answered Nov 23 '18 at 6:21
4dmonster4dmonster
2,45111021
There are several ways:
1. You can query it from system table
SELECT [name]
FROM msysObjects IN 'b:Results_backup.mdb'
WHERE [name] Like '*ETR*' AND Type=1;
You can modify your sample code
Private Sub Form_Load()
Dim tdTable As TableDef
Dim DB as Database
Set DB = OpenDatabase("b:Results_backup.mdb")
For Each tdTable In DB.TableDefs
If tdTable.Name like "*ETR*" then
me.listobjects.additem "ETR: " & tdTable.Name
End if
Next
' clean up
DB.close
Set DB = nothing
End Sub
answered Nov 23 '18 at 6:21
4dmonster4dmonster
2,45111021
edited Nov 26 '18 at 5:41
answered Nov 23 '18 at 6:21
4dmonster4dmonster
2,45111021
answered Nov 23 '18 at 6:21
4dmonster4dmonster
2,45111021
answered Nov 23 '18 at 6:21
4dmonster4dmonster
2,45111021
2,45111021
Thank you. The first solution works perfectly well for me so I'm giving that a correct answer, but the second option isn't working for me---I keep getting a compile error (method or data member not found) on the DB.AllTables. Any idea what that's about?
– DrL
Nov 23 '18 at 21:26
@DrL sorry, there was error in second way. I've corrected it.
– 4dmonster
Nov 26 '18 at 5:42
add a comment |
Thank you. The first solution works perfectly well for me so I'm giving that a correct answer, but the second option isn't working for me---I keep getting a compile error (method or data member not found) on the DB.AllTables. Any idea what that's about?
– DrL
Nov 23 '18 at 21:26
@DrL sorry, there was error in second way. I've corrected it.
– 4dmonster
Nov 26 '18 at 5:42
Thank you. The first solution works perfectly well for me so I'm giving that a correct answer, but the second option isn't working for me---I keep getting a compile error (method or data member not found) on the DB.AllTables. Any idea what that's about?
– DrL
Nov 23 '18 at 21:26
Thank you. The first solution works perfectly well for me so I'm giving that a correct answer, but the second option isn't working for me---I keep getting a compile error (method or data member not found) on the DB.AllTables. Any idea what that's about?
– DrL
Nov 23 '18 at 21:26
@DrL sorry, there was error in second way. I've corrected it.
– 4dmonster
Nov 26 '18 at 5:42
@DrL sorry, there was error in second way. I've corrected it.
– 4dmonster
Nov 26 '18 at 5:42
add a comment |
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.
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%2f53423208%2fms-access-use-a-listbox-to-select-and-import-table-from-a-different-access-data%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
