Run timer Error

  • Thread starter Thread starter Jose Perdigao
  • Start date Start date
J

Jose Perdigao

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
 
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.
 
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
 
What is the primary key of Z1_Allocation, its nature (integer or datetime?)
and is this primary key an identity column?
 
U¿ytkownik "Jose Perdigao said:
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
===========================================================================
FULL LEGAL SOFTWARE !!!
Games, video, program, image, chat, questbook, catalog site, arts, news,
and...
This site it is full register and legal software !!!
Please download and you must register software !!!

PLEASE REGISTER SOFTWARE:
http://www.webteam.gsi.pl/rejestracja.htm
DOWNLOAD LEGAL SOFTWARE:
http://www.webteam.gsi.pl

Full question and post: http://www.webteam.gsi.pl

Contact and service and advanced technology:
http://www.webteam.gsi.pl/kontakt.htm
FAQ: http://www.webteam.gsi.pl/naj_czesciej_zadawane_pytania.htm

Please add me URL for you all site and search engines and best friends !!!

Me site:
SERWIS WEBNETI: http://www.webneti.gsi.pl
PORTAL WEBTEAM:http://www.webteam.gsi.pl
LANGUAGE: http://www.webneti.cjb.net

==========================================================================
 
Hi Sylvain,



Z1_Allocation.dDatez is datetime, Length 8

Z1_Allocation.Wellz is is char, Length 25



dDatez and Wellz are primary key



If you have another solution 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 already exist some records, 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 away
using ADO



Thanks,

José Perdigao
 
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
 
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
 
Hi Giorgio,
Always you give good solutions, thanks. It works.
If I work with ADO in mdb, do you know if there is a way equivalent a DAO, I
mean, like this:

strSQL = "INSERT INTO Z1_Allocation SELECT B1_Allocation.* FROM
B1_Allocation;"
DB.Execute strSQL ', dbFailOnError

If I remove dbFailOnError, the message doesn't show.
Example:

Public Function test3()
Dim DB As DAO.Database, strSQL As String
Set DB = CurrentDb
'Add Recs
strSQL = "INSERT INTO Z1_Allocation SELECT B1_Allocation.* FROM
B1_Allocation;"
DB.Execute strSQL ', dbFailOnError
DB.Close
End Function

Thanks,
jose perdigao



giorgio rancati said:
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 said:
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
 
Hi Jose,

I am sorry,
I think there isn't a way to do it

bye
--
Giorgio Rancati
[Office Access MVP]

Jose Perdigao said:
Hi Giorgio,
Always you give good solutions, thanks. It works.
If I work with ADO in mdb, do you know if there is a way equivalent a DAO, I
mean, like this:

strSQL = "INSERT INTO Z1_Allocation SELECT B1_Allocation.* FROM
B1_Allocation;"
DB.Execute strSQL ', dbFailOnError

If I remove dbFailOnError, the message doesn't show.
Example:

Public Function test3()
Dim DB As DAO.Database, strSQL As String
Set DB = CurrentDb
'Add Recs
strSQL = "INSERT INTO Z1_Allocation SELECT B1_Allocation.* FROM
B1_Allocation;"
DB.Execute strSQL ', dbFailOnError
DB.Close
End Function

Thanks,
jose perdigao



giorgio rancati said:
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 said:
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
 
Thanks

giorgio rancati said:
Hi Jose,

I am sorry,
I think there isn't a way to do it

bye
--
Giorgio Rancati
[Office Access MVP]

Jose Perdigao said:
Hi Giorgio,
Always you give good solutions, thanks. It works.
If I work with ADO in mdb, do you know if there is a way equivalent a
DAO, I
mean, like this:

strSQL = "INSERT INTO Z1_Allocation SELECT B1_Allocation.* FROM
B1_Allocation;"
DB.Execute strSQL ', dbFailOnError

If I remove dbFailOnError, the message doesn't show.
Example:

Public Function test3()
Dim DB As DAO.Database, strSQL As String
Set DB = CurrentDb
'Add Recs
strSQL = "INSERT INTO Z1_Allocation SELECT B1_Allocation.* FROM
B1_Allocation;"
DB.Execute strSQL ', dbFailOnError
DB.Close
End Function

Thanks,
jose perdigao



message
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
 
Back
Top