Re: Run timer Error

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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: determining if this is the first new record in a subform
    ... sorts of errors in the subform... ... Dim rs As DAO.Recordset ... Dim strSql As String ... tblProjectStep.StepNum (Primary Key – Text – StepNum from tblProjectStep) ...
    (microsoft.public.access.modulesdaovba)
  • Re: Run timer Error
    ... What is the primary key of Z1_Allocation, ... Dim DB As DAO.Database, strSQL As String ... Dim cnn As ADODB.Connection, strSQL As String ... Run time error: '-214217873 ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Run timer Error
    ... primary key avoids duplication records. ... Dim DB As DAO.Database, strSQL As String ... Dim cnn As ADODB.Connection, strSQL As String ... dDatez, Wellz, IDWT FROM dbo.B1_Allocation" ...
    (microsoft.public.access.adp.sqlserver)
  • Re: determining if this is the first new record in a subform
    ... tblProjectStep.ProjectID (Primary Key – Text – description from tblProject) ... tblProjectTypeStep.ToolID ... Dim rs As DAO.Recordset ... Dim strSql As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: Record Lookup
    ... Dim SelectedRows() As DataRow ... The select method returns all rows matching the criteria. ... primary key in the table to the field that I wanted to use for the search ... Dim drSelectedRecord As DataRow ...
    (microsoft.public.dotnet.languages.vb)