How to update tables from an external Access database with identical tables?
up vote
0
down vote
favorite
We have a main Access database that we need to update from identically structured databases on our laptops that go out into the field. I have created a form that allows the user to select the tables to update from the external database. (see pic below)
What I am having trouble with is finding a method to use VBA to pull the new (unique) records from the external databases while not createing new duplicate records. Mostly I'm trying to find the SQL code I would need for my VBA function.
Also, just to make sure I'm on the right path, Do I need to create a connection to the external database (like I do working with Excel) or is there functionality within Access that makes this unecessary within my code (like calling it directly via file path)?
sql vba ms-access
asked Nov 15 at 1:48
LazyBear
120315
add a comment |
up vote
0
down vote
favorite
We have a main Access database that we need to update from identically structured databases on our laptops that go out into the field. I have created a form that allows the user to select the tables to update from the external database. (see pic below)
What I am having trouble with is finding a method to use VBA to pull the new (unique) records from the external databases while not createing new duplicate records. Mostly I'm trying to find the SQL code I would need for my VBA function.
Also, just to make sure I'm on the right path, Do I need to create a connection to the external database (like I do working with Excel) or is there functionality within Access that makes this unecessary within my code (like calling it directly via file path)?
sql vba ms-access
asked Nov 15 at 1:48
LazyBear
120315
Can't you just drop the tables and re-copy them?
– Comintern
Nov 15 at 1:50
@Comintern No, not all the computers have all the data. Only the main database (the one using this form), will have all the data (other than new data from external DBs that need to be added). They're coming from different offline areas, and the requirement states the laptops can't delete the old data and can't take on the entire main database.
– LazyBear
Nov 15 at 18:14
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
We have a main Access database that we need to update from identically structured databases on our laptops that go out into the field. I have created a form that allows the user to select the tables to update from the external database. (see pic below)
What I am having trouble with is finding a method to use VBA to pull the new (unique) records from the external databases while not createing new duplicate records. Mostly I'm trying to find the SQL code I would need for my VBA function.
Also, just to make sure I'm on the right path, Do I need to create a connection to the external database (like I do working with Excel) or is there functionality within Access that makes this unecessary within my code (like calling it directly via file path)?
sql vba ms-access
asked Nov 15 at 1:48
LazyBear
120315
We have a main Access database that we need to update from identically structured databases on our laptops that go out into the field. I have created a form that allows the user to select the tables to update from the external database. (see pic below)
What I am having trouble with is finding a method to use VBA to pull the new (unique) records from the external databases while not createing new duplicate records. Mostly I'm trying to find the SQL code I would need for my VBA function.
Also, just to make sure I'm on the right path, Do I need to create a connection to the external database (like I do working with Excel) or is there functionality within Access that makes this unecessary within my code (like calling it directly via file path)?
sql vba ms-access
sql vba ms-access
asked Nov 15 at 1:48
LazyBear
120315
asked Nov 15 at 1:48
LazyBear
120315
asked Nov 15 at 1:48
LazyBear
120315
asked Nov 15 at 1:48
LazyBear
120315
asked Nov 15 at 1:48
LazyBear
120315
120315
Can't you just drop the tables and re-copy them?
– Comintern
Nov 15 at 1:50
@Comintern No, not all the computers have all the data. Only the main database (the one using this form), will have all the data (other than new data from external DBs that need to be added). They're coming from different offline areas, and the requirement states the laptops can't delete the old data and can't take on the entire main database.
– LazyBear
Nov 15 at 18:14
add a comment |
Can't you just drop the tables and re-copy them?
– Comintern
Nov 15 at 1:50
@Comintern No, not all the computers have all the data. Only the main database (the one using this form), will have all the data (other than new data from external DBs that need to be added). They're coming from different offline areas, and the requirement states the laptops can't delete the old data and can't take on the entire main database.
– LazyBear
Nov 15 at 18:14
Can't you just drop the tables and re-copy them?
– Comintern
Nov 15 at 1:50
Can't you just drop the tables and re-copy them?
– Comintern
Nov 15 at 1:50
@Comintern No, not all the computers have all the data. Only the main database (the one using this form), will have all the data (other than new data from external DBs that need to be added). They're coming from different offline areas, and the requirement states the laptops can't delete the old data and can't take on the entire main database.
– LazyBear
Nov 15 at 18:14
@Comintern No, not all the computers have all the data. Only the main database (the one using this form), will have all the data (other than new data from external DBs that need to be added). They're coming from different offline areas, and the requirement states the laptops can't delete the old data and can't take on the entire main database.
– LazyBear
Nov 15 at 18:14
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
You can query the external DB by building SQL in vba such as "SELECT .... IN 'FilePathHere' ....". You can find that syntax easily if you search it.
To do it simply, I'd just loop through a record set and compare the unique values of the target location and source location, only bringing source values that have no match in the target table.
edit: if the NEW tables have all of the same values as the old PLUS MORE, then I'd just drop the old table and copy the entire thing over to save yourself any trouble.
answered Nov 15 at 2:24
imnotsorry
1
I was hoping there was a way to avoid looping through record set to determine unique entries. I've seen it done in a single query on other databases, I just never got a good look at the SQL query (at least, I don't remember it). Only the main database has all of the data. The other computers (laptops) are offline, and are brought in to update the main database. They only have the data the've sent to the main (done manually until now).
– LazyBear
Nov 15 at 22:04
I was sleepy when I posted this. Access has an "unmatched query" wizard. You can use that to get the skeleton of the query then just modify it to use the external DBs table
– imnotsorry
Nov 15 at 22:48
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
You can query the external DB by building SQL in vba such as "SELECT .... IN 'FilePathHere' ....". You can find that syntax easily if you search it.
To do it simply, I'd just loop through a record set and compare the unique values of the target location and source location, only bringing source values that have no match in the target table.
edit: if the NEW tables have all of the same values as the old PLUS MORE, then I'd just drop the old table and copy the entire thing over to save yourself any trouble.
answered Nov 15 at 2:24
imnotsorry
1
I was hoping there was a way to avoid looping through record set to determine unique entries. I've seen it done in a single query on other databases, I just never got a good look at the SQL query (at least, I don't remember it). Only the main database has all of the data. The other computers (laptops) are offline, and are brought in to update the main database. They only have the data the've sent to the main (done manually until now).
– LazyBear
Nov 15 at 22:04
I was sleepy when I posted this. Access has an "unmatched query" wizard. You can use that to get the skeleton of the query then just modify it to use the external DBs table
– imnotsorry
Nov 15 at 22:48
add a comment |
up vote
0
down vote
You can query the external DB by building SQL in vba such as "SELECT .... IN 'FilePathHere' ....". You can find that syntax easily if you search it.
To do it simply, I'd just loop through a record set and compare the unique values of the target location and source location, only bringing source values that have no match in the target table.
edit: if the NEW tables have all of the same values as the old PLUS MORE, then I'd just drop the old table and copy the entire thing over to save yourself any trouble.
answered Nov 15 at 2:24
imnotsorry
1
I was hoping there was a way to avoid looping through record set to determine unique entries. I've seen it done in a single query on other databases, I just never got a good look at the SQL query (at least, I don't remember it). Only the main database has all of the data. The other computers (laptops) are offline, and are brought in to update the main database. They only have the data the've sent to the main (done manually until now).
– LazyBear
Nov 15 at 22:04
I was sleepy when I posted this. Access has an "unmatched query" wizard. You can use that to get the skeleton of the query then just modify it to use the external DBs table
– imnotsorry
Nov 15 at 22:48
add a comment |
up vote
0
down vote
up vote
0
down vote
You can query the external DB by building SQL in vba such as "SELECT .... IN 'FilePathHere' ....". You can find that syntax easily if you search it.
To do it simply, I'd just loop through a record set and compare the unique values of the target location and source location, only bringing source values that have no match in the target table.
edit: if the NEW tables have all of the same values as the old PLUS MORE, then I'd just drop the old table and copy the entire thing over to save yourself any trouble.
answered Nov 15 at 2:24
imnotsorry
1
You can query the external DB by building SQL in vba such as "SELECT .... IN 'FilePathHere' ....". You can find that syntax easily if you search it.
To do it simply, I'd just loop through a record set and compare the unique values of the target location and source location, only bringing source values that have no match in the target table.
edit: if the NEW tables have all of the same values as the old PLUS MORE, then I'd just drop the old table and copy the entire thing over to save yourself any trouble.
answered Nov 15 at 2:24
imnotsorry
1
answered Nov 15 at 2:24
imnotsorry
1
answered Nov 15 at 2:24
imnotsorry
1
answered Nov 15 at 2:24
imnotsorry
1
1
I was hoping there was a way to avoid looping through record set to determine unique entries. I've seen it done in a single query on other databases, I just never got a good look at the SQL query (at least, I don't remember it). Only the main database has all of the data. The other computers (laptops) are offline, and are brought in to update the main database. They only have the data the've sent to the main (done manually until now).
– LazyBear
Nov 15 at 22:04
I was sleepy when I posted this. Access has an "unmatched query" wizard. You can use that to get the skeleton of the query then just modify it to use the external DBs table
– imnotsorry
Nov 15 at 22:48
add a comment |
I was hoping there was a way to avoid looping through record set to determine unique entries. I've seen it done in a single query on other databases, I just never got a good look at the SQL query (at least, I don't remember it). Only the main database has all of the data. The other computers (laptops) are offline, and are brought in to update the main database. They only have the data the've sent to the main (done manually until now).
– LazyBear
Nov 15 at 22:04
I was sleepy when I posted this. Access has an "unmatched query" wizard. You can use that to get the skeleton of the query then just modify it to use the external DBs table
– imnotsorry
Nov 15 at 22:48
I was hoping there was a way to avoid looping through record set to determine unique entries. I've seen it done in a single query on other databases, I just never got a good look at the SQL query (at least, I don't remember it). Only the main database has all of the data. The other computers (laptops) are offline, and are brought in to update the main database. They only have the data the've sent to the main (done manually until now).
– LazyBear
Nov 15 at 22:04
I was hoping there was a way to avoid looping through record set to determine unique entries. I've seen it done in a single query on other databases, I just never got a good look at the SQL query (at least, I don't remember it). Only the main database has all of the data. The other computers (laptops) are offline, and are brought in to update the main database. They only have the data the've sent to the main (done manually until now).
– LazyBear
Nov 15 at 22:04
I was sleepy when I posted this. Access has an "unmatched query" wizard. You can use that to get the skeleton of the query then just modify it to use the external DBs table
– imnotsorry
Nov 15 at 22:48
I was sleepy when I posted this. Access has an "unmatched query" wizard. You can use that to get the skeleton of the query then just modify it to use the external DBs table
– imnotsorry
Nov 15 at 22:48
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53311304%2fhow-to-update-tables-from-an-external-access-database-with-identical-tables%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
Can't you just drop the tables and re-copy them?
– Comintern
Nov 15 at 1:50
@Comintern No, not all the computers have all the data. Only the main database (the one using this form), will have all the data (other than new data from external DBs that need to be added). They're coming from different offline areas, and the requirement states the laptops can't delete the old data and can't take on the entire main database.
– LazyBear
Nov 15 at 18:14