Re: Run timer Error

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" <giorgio_No_Spalmer_rancati@xxxxxxxxxx> wrote in message
news:eIF$9yvgGHA.1520@xxxxxxxxxxxxxxxxxxxxxxx
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" <perdijc@xxxxxxxxxxx> ha scritto nel messaggio
news:%23NlmGsvgGHA.3572@xxxxxxxxxxxxxxxxxxxxxxx
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 news:%23N1NkvngGHA.1208@xxxxxxxxxxxxxxxxxxxxxxx
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


"Jose Perdigao" <perdijc@xxxxxxxxxxx> wrote in message
news:uiM3QqngGHA.1260@xxxxxxxxxxxxxxxxxxxxxxx
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 news:usplUCmgGHA.3588@xxxxxxxxxxxxxxxxxxxxxxx
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


"Jose Perdigao" <perdijc@xxxxxxxxxxx> wrote in message
news:%23YQEtnkgGHA.4404@xxxxxxxxxxxxxxxxxxxxxxx
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
















.

Relevant Pages

  • Re: DAO vs ADO
    ... carrying member of the lurking public on a thread titled "Use Primary Key?" ... Is ADO a dead duck? ... Is DAO and/or ADO a useful stepping stone to the .NExT ...
    (microsoft.public.access.conversion)
  • Re: DAO vs ADO
    ... carrying member of the lurking public on a thread titled "Use Primary Key?" ... Is ADO a dead duck? ... Is DAO and/or ADO a useful stepping stone to the .NExT ...
    (microsoft.public.access.adp.sqlserver)
  • Re: DAO migration question
    ... The DAO version will execute a query that inserts ... > records into a table that has a primary key defined, but the ADO will not. ...
    (microsoft.public.vb.database.ado)
  • Re: DAO migration question
    ... The DAO version will execute a query that inserts ... > records into a table that has a primary key defined, but the ADO will not. ...
    (microsoft.public.data.ado)
  • ADO teething problems
    ... strange quirk in A2K (mdb, ... CurrentDB.Execute StrSQL - works fine ... But with ADO using cn.execute, ... differently to DAO / Straight Jet SQL queries. ...
    (comp.databases.ms-access)