Question what is the essense of update query? (1 Viewer)
- Thread starter genesis
- Start date
thanks for the reply pbaldy. the question on this post relates to my idea of synchronizing two or more databases with each other.
for example I have two database and I want to synchronize their records to each other, so it is only right to use Append Query right and not update query?
so am I right with the code:
insert into target [IN externaldatabasename] ???
I placed ??? because I would like to ask what will be its syntax for all fields in the target object so that I dont need to state the name of the fields.
and how should it be coded so that there will be no duplicate records that will be appended?
for example I have two database and I want to synchronize their records to each other, so it is only right to use Append Query right and not update query?
so am I right with the code:
insert into target [IN externaldatabasename] ???
I placed ??? because I would like to ask what will be its syntax for all fields in the target object so that I dont need to state the name of the fields.
and how should it be coded so that there will be no duplicate records that will be appended?
INSERT INTO Table1 ( ID, Fn, Mn ) IN 'D:\rommelfiles\Database2.accdb'
SELECT Table1.ID, Table1.Fn, Table1.Mn
FROM Table1;
This is my test for append query code that will update records in database2 from database1. However there will error appear if there is violation due to duplicate records. How will I prevents this error to happen?
SELECT Table1.ID, Table1.Fn, Table1.Mn
FROM Table1;
This is my test for append query code that will update records in database2 from database1. However there will error appear if there is violation due to duplicate records. How will I prevents this error to happen?
- Local time
- Yesterday, 16:18
- Joined
- Aug 30, 2003
- Messages
- 36,127
You can see if this works:
http://support.microsoft.com/kb/208870
You may have missed Brent's point on your other thread. You can still have replication with Access 2007; you just need to leave the data file in MDB format.
http://support.microsoft.com/kb/208870
You may have missed Brent's point on your other thread. You can still have replication with Access 2007; you just need to leave the data file in MDB format.
I dont want to use MDB format pbaldy because I am also after Access 2007 db password security. there may have been problem with the link you provided pbaldy because its not accessible "page load error". Maybe you can give me the gist of the link you provided, please.
Galaxiom, what do you mean I need to outer join the two tables from two databases?
Oh I see. You mean so that I will not have this error due to duplicate records, I should make a criteria where the primary key is null.
Wait, Can you kindly edit my code above and include how the outerjoin and isnull criteria will appear. Im kinda still weak in that area. Kindly please.
Moreover, if I will use outerjoin, I need to link the table1 from database2 to table1 of database1. Should I need to link first or not needed?
Galaxiom, what do you mean I need to outer join the two tables from two databases?
Oh I see. You mean so that I will not have this error due to duplicate records, I should make a criteria where the primary key is null.
Wait, Can you kindly edit my code above and include how the outerjoin and isnull criteria will appear. Im kinda still weak in that area. Kindly please.
Moreover, if I will use outerjoin, I need to link the table1 from database2 to table1 of database1. Should I need to link first or not needed?
Try this:
INSERT INTO Table1 ( ID, Fn, Mn ) IN 'D:\rommelfiles\Database2.accdb' AS B
RIGHT JOIN Table1 AS A ON A.ID = B.ID
SELECT A.ID, A.Fn, A.Mn
FROM A
WHERE B.ID Is Null;
Hope I have it the right way around.
INSERT INTO Table1 ( ID, Fn, Mn ) IN 'D:\rommelfiles\Database2.accdb' AS B
RIGHT JOIN Table1 AS A ON A.ID = B.ID
SELECT A.ID, A.Fn, A.Mn
FROM A
WHERE B.ID Is Null;
Hope I have it the right way around.
You might be right about having to link the tables to use a join but I am in over my head.
However something also tells me that to use an insert query like this requires a subquery with the join to select the records.
One of the Power Posters will no doubt sort it for you.
However something also tells me that to use an insert query like this requires a subquery with the join to select the records.
One of the Power Posters will no doubt sort it for you.
INSERT INTO Table1 ( ID, Fn, Mn ) IN 'D:\rommelfiles\Database2.accdb'
SELECT Table1.ID, Table1.Fn, Table1.Mn
FROM Table1;
instead of the above, can I use below:
INSERT INTO Table1 ( * ) IN 'D:\rommelfiles\Database2.accdb'
SELECT *
FROM Table1;
I replaced * "asterisk" there so that I wont need to specify the fields. Can I do that in append query?
SELECT Table1.ID, Table1.Fn, Table1.Mn
FROM Table1;
instead of the above, can I use below:
INSERT INTO Table1 ( * ) IN 'D:\rommelfiles\Database2.accdb'
SELECT *
FROM Table1;
I replaced * "asterisk" there so that I wont need to specify the fields. Can I do that in append query?
INSERT INTO Table1 IN 'D:\rommelfiles\Database2.accdb'
SELECT *
FROM Table1;
I have tried the above code and it still works but the problem is it was just too slow to update the records in database2, maybe its because the fields where not specified unlike if the fields were specified, the result was much faster appending of records.
SELECT *
FROM Table1;
I have tried the above code and it still works but the problem is it was just too slow to update the records in database2, maybe its because the fields where not specified unlike if the fields were specified, the result was much faster appending of records.
datAdrenaline
AWF VIP
- Local time
- Yesterday, 18:18
- Joined
- Jun 23, 2008
- Messages
- 697
I would suggest that you use the following syntax to reference the destination database and table:
INSERT INTO [;DATABASE=D:\rommelfiles\Database2.accdb].[Destination] ( ID, Fn, Mn )
SELECT Source.ID, Source.Fn, Source.Mn
FROM Source
LEFT JOIN [;DATABASE=D:\rommelfiles\Database2.accdb].[Destination] As vDestination
ON Source.ID = vDestination.ID
WHERE vDestination.ID Is Null;
With the following:
Source: Name of the source table
Destination: Name of the destination table (note vDestination is merely an alias as you can leave that)
D:\rommelfiles\Database2.accdb: Is the full name and path of the destination database.
The assumption is taht you would execute this SQL statement from the database that contains the Source table with something like:
CurrentDb.Execute strSQL, dbFailOnError
INSERT INTO [;DATABASE=D:\rommelfiles\Database2.accdb].[Destination] ( ID, Fn, Mn )
SELECT Source.ID, Source.Fn, Source.Mn
FROM Source
LEFT JOIN [;DATABASE=D:\rommelfiles\Database2.accdb].[Destination] As vDestination
ON Source.ID = vDestination.ID
WHERE vDestination.ID Is Null;
With the following:
Source: Name of the source table
Destination: Name of the destination table (note vDestination is merely an alias as you can leave that)
D:\rommelfiles\Database2.accdb: Is the full name and path of the destination database.
The assumption is taht you would execute this SQL statement from the database that contains the Source table with something like:
CurrentDb.Execute strSQL, dbFailOnError
My earlier syntax was all back to front.
I usually rely on the query designer.
I was thinking "Where are the Power Posters."
And Lo, Behold datAdrenaline's solution.
I was almost there.
I got to a similar point except I was using IN instead of [;DATABASE= etc]
Is that just an alternative syntax or is the formal looking bit essentially different?
I usually rely on the query designer.
I was thinking "Where are the Power Posters."
And Lo, Behold datAdrenaline's solution.
I was almost there.
I got to a similar point except I was using IN instead of [;DATABASE= etc]
Is that just an alternative syntax or is the formal looking bit essentially different?
INSERT INTO [;DATABASE=D:\rommelfiles\Database2.accdb].table1 ( ID, Fn, Mn )
SELECT table1.ID, table1.Fn, table1.Mn
FROM table1 LEFT JOIN [;DATABASE=D:\rommelfiles\Database2.accdb].table1 AS vDestination ON table1.ID = vDestination.ID
WHERE vDestination.ID Is Null;
wow this is already perfect.
but I have just this question. why the different syntax of append query from the usual one?
and another thing is that when I edit the sql again, the "].destionation" part changes back to ") destination" Is there something wrong here. It is to be stated that I have saved it before I edited it.
Is there other way of writing its syntax. where can I find site for learning those syntax?
another thing is that, Do I need to specify the fields or can I just use the * asterisk sign on the Select statement and in the Insert Into fields?
SELECT table1.ID, table1.Fn, table1.Mn
FROM table1 LEFT JOIN [;DATABASE=D:\rommelfiles\Database2.accdb].table1 AS vDestination ON table1.ID = vDestination.ID
WHERE vDestination.ID Is Null;
wow this is already perfect.
but I have just this question. why the different syntax of append query from the usual one?
and another thing is that when I edit the sql again, the "].destionation" part changes back to ") destination" Is there something wrong here. It is to be stated that I have saved it before I edited it.
Is there other way of writing its syntax. where can I find site for learning those syntax?
another thing is that, Do I need to specify the fields or can I just use the * asterisk sign on the Select statement and in the Insert Into fields?
Last edited:
datAdrenaline
AWF VIP
- Local time
- Yesterday, 18:18
- Joined
- Jun 23, 2008
- Messages
- 697
I don't have time to expand now ... but ... that is essentially a bug.
If you SAVE the statement in the syntax I have shown, the don't edit it, you *should* be fine ... even if you VIEW the formatting done by MS Access's infinite wisdom ... but as soon as you edit anything on the query, you will have to correct the formatting ...
I have some links going into more detail ... but I am REALLY tired (3:15 am for me --- I need to get up in 4 hours!) ... I will check back in tommorrow!
EDITS>>>
That is a large part of the reason that I execute a lot of action queries through code. With a core line of code as I displayed earlier.
If you SAVE the statement in the syntax I have shown, the don't edit it, you *should* be fine ... even if you VIEW the formatting done by MS Access's infinite wisdom
... but as soon as you edit anything on the query, you will have to correct the formatting ... I have some links going into more detail ... but I am REALLY tired (3:15 am for me --- I need to get up in 4 hours!) ... I will check back in tommorrow!
EDITS>>>
That is a large part of the reason that I execute a lot of action queries through code. With a core line of code as I displayed earlier.
An Append query has two parts. The first part is the command to append and indicates the arrangement of the destination fields and table. The remainder is essentially a subquery to select the records to be appended. It just doesn't use the parentheses like usual subqueries.
The "AS somename" is also used in other queries. It is used to abbreviate the name of a table or to differentiate between two tables with the same name.
