Hi Jose,
insert only the rows that don't exists into dbo.Z1_Allocation
ie
----
INSERT INTO dbo.Z1_Allocation (dDatez, Wellz, IDWT)
SELECT B1.dDatez, B1.Wellz, B1.IDWT
FROM dbo.B1_Allocation AS B1
LEFT JOIN dbo.Z1_Allocation AS Z1
ON B1.dDatez=Z1.dDatez AND
B1.Wellz=Z1.Wellz
WHERE Z1.dDatez IS NULL
----
bye
--
Giorgio Rancati
[Office Access MVP]
"Jose Perdigao" <
[email protected]> ha scritto nel messaggio
Hi Sylvain,
Z1_Allocation.dDatez is datetime, Length 8
Z1_Allocation.Wellz is char, Length 25
dDatez and Wellz are primary key
If you have another solution I will be appreciate.
The idea is: Every day, I should add a set of records from one table
to
another one. If the records don't exist, the function should be adding
new
set of records. If some records already exist , the function should be
add
the missing records and ignore the message for the duplicate records. The
primary key avoids duplication records.
In DAO, the example that I gave you, works fine I would use the same way
using ADO
Thanks,
José Perdigao
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
please)>
wrote in message What is the primary key of Z1_Allocation, its nature (integer or
datetime?) and is this primary key an identity column?
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail:
http://cerbermail.com/?QugbLEWINF
Thanks Sylvain,
I will give an example that I use in DAO
USING DAO:
Public Function test3()
Dim DB As DAO.Database, strSQL As String
Set DB = CurrentDb
'Add Recs
strSQL = "INSERT INTO Z1_Allocation ( dDatez, Wellz, IDWT ) SELECT
B1_Allocation.dDatez, B1_Allocation.Wellz, B1_Allocation.IDWT FROM
B1_Allocation;"
DB.Execute strSQL ', dbFailOnError
DB.Close
End Function
If I don't use dbFailOnError the message when there is duplicates
is
ignored. What happens, the function and the records that is
missing
and the duplicates, doesn't and and doesn't show the message.
I would like to do the same thing using ADO.
EXAMPLE USING ADO
Public Function test3()
Dim cnn As ADODB.Connection, strSQL As String
Set cnn = CurrentProject.Connection
'Add Recs
strSQL = "INSERT INTO dbo.Z1_Allocation (dDatez, Wellz, IDWT)
SELECT
dDatez, Wellz, IDWT FROM dbo.B1_Allocation"
cnn.Execute strSQL
cnn.Close
End Function
I want ignore the following message:
Run time error: '-214217873 (80040e2f)
Violation of primary key...
Any suggestions?
Thanks Sylvain.
jose perdigao
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
please)>
wrote in message You are repeating the same table B1_Allocation two times in your SQL
string, so it's impossible to know you want to do here.
Also, when you have error message such as a violation of primary key,
it's a good idea to tell us what are the structures of these two
tables.
Finally, even if you don't use a recordset here, using the
operator
New
with a Dim like in « Dim rs As New ADODB.Recordset » instead of
creating
it explicitely is not a good idea. Same thing with the others ADO
objects.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail:
http://cerbermail.com/?QugbLEWINF
I'm developing a database in ADP, access 2003.
I run the following function
Public Function test1()
Dim cnn As ADODB.Connection, rs As New ADODB.Recordset, strSQL As
String
SetWarnings = False
Set cnn = CurrentProject.Connection
'Add Recs
INSERT INTO dbo.B1_Allocation
(dDatez, Wellz, IDWT)
SELECT dbo.iDate() AS idate, Wellz, IDWT
FROM dbo.B1_Allocation
cnn.Execute strSQL
cnn.Close
End Function
If in table dbo.A1_Allocation there is no data, works fine.
If in table dbo.A1_Allocation there the data exists, comes the
following run time error:
Run time error: '-214217873 (80040e2f)
Violation of primary key...
How can I ignore this message? I mean, if there is not data, add new
data in table A1_Allocation. If there is data, or if there is
only
some
records, but not all, add the data that is missing but I want ignore
the message for duplication data.
Any suggestions?
Thansk a lot,
Jose´Perdigão
