Re: Run timer Error

Tech-Archive recommends: Fix windows errors by optimizing your registry

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: VBScriptADO Max Length of String
    ... Dim objFSO, objFile, strServers, arrServers, strServer ... Dim strTableName, strSql, objConn, strSvrName, strDBName ... whole file is one string. ...
    (microsoft.public.windows.server.scripting)
  • Re: SQL Fustrations
    ... Dim strLogEvent As String ... Dim strSQL As String ...
    (microsoft.public.vb.general.discussion)
  • RE: Creating a query using multiselect list box and text boxes
    ... Dim strSQL As String ... Dim strWhere As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: repli
    ... Private Sub Program_NotInList(NewData As String, Response As Integer) ... Dim cmd As ADODB.Command ... Dim strSQL As String, strMessage As String ...
    (microsoft.public.access.forms)
  • Re: Update DB table from visual basic script
    ... Dim dbs As Database ... Public Function UpdateDB(dbName As String) As Boolean ... Public Function updateDB_fnameAs Boolean ... On Error Resume Next ' if proc does not exist then return blank string ...
    (comp.databases.ms-access)