Actions Queries in ADO

[Jose Perdigao]

The following function works good in DAO

DAO:
Public Function AddRecsPPMs()
Dim DB As DAO.Database, strSQL As String, RS As DAO.Recordset
Set DB = DBEngine(0)(0)
strSQL = "INSERT INTO dtPPMs ( [Date], NameID ) SELECT inputDate() AS
[Date], axPPMs.NameID FROM axPPMs WHERE axPPMs.PlatformID=inputPlatform() ;"
DB.Execute strSQL ', dbFailOnError
DB.Close
End If

If a repeat this function doesn’t show the message (were not successful
because they would create duplicates values…), because ', dbFailOnError is
not enable.
Note: InputDate() and inputPlatform() are my ownerfunctions.

ADO:
Dim cnn As ADODB.Connection, RS As ADODB.Recordset, strSQL As String
Set cnn = CurrentProject.Connection
strSQL = "INSERT INTO dtPPMs ( [Date], NameID ) SELECT inputDate() AS
[Date], axPPMs.NameID FROM axPPMs WHERE axPPMs.PlatformID=inputPlatform() ;"
cnn.Execute strSQL
cnn.Close
End Function
If I call this function and if the records are not added it works fine, but
if call second time, shows the message: … were not successful because they
would create duplicates values…,

Question 1.
which attribute must I add after (cnn.execute strSQL,.....) to ignore the
msg above? I mean, if there are not records, add news set records; if there
are not, doesn’t do nothing like the example in DAO.

Question 2
Do you think, the code in DAO and ADO above, is the best for the best
performance? (action queries)

Question 3
Is it possible to add and/or delete records in SQL Server vs ADP by this
method, I mean by action queries?

Question 4
Is it possible to use my owner functions in SQL Server vs ADP?

Thanks a lot for you help or suggestions

Thanks
José

 

[Tim Ferguson]

"=?Utf-8?B?Sm9zZSBQZXJkaWdhbw==?="

strSQL = "INSERT INTO dtPPMs ( [Date], NameID ) " & _
" SELECT inputDate() AS [Date], " & _
" axPPMs.NameID " & _
" FROM axPPMs " & _
" WHERE axPPMs.PlatformID=inputPlatform();"

DB.Execute strSQL ' not using dbFailOnError


ADO:

[comparison code snipped]

Question 1.
which attribute must I add after (cnn.execute strSQL,.....) to ignore
the msg above? I mean, if there are not records, add news set records;
if there are not, doesn’t do nothing like the example in DAO.

There isn't one, quite appropriately. Switching off dbFailOnError is just
plain bloody foolhardy, frankly. You need to be cleaning your data before
insert, not just crossing your fingers and hoping the rubbish gets
magically excluded.

Question 2
Do you think, the code in DAO and ADO above, is the best for the best
performance? (action queries)

See above.

Question 3
Is it possible to add and/or delete records in SQL Server vs ADP by
this method, I mean by action queries?

In either system, using [Date] for a column name is asking for problems,
although it might not be actually illegal. Apart from that, this query
should be valid Jet-SQL or T-SQL.

Question 4
Is it possible to use my owner functions in SQL Server vs ADP?

I don't know what an owner function is.


HTH


Tim F

 

[Jose Perdigao]

Good Morngin Tim,


Question 1,
My question is, if I run the code for this example, I don't want to see the
msg box.
The table is protected against duplicate values, so I don't want see this
message.
The idea is, if is possible add new records, add, if the records are already
there, doesn’t add any records and doesn’t display any message.


Question 3,
I tested and is possible, thanks.

Question 4
Owner function is, I mean my functions developed in access.
Example:
SELECT Employees.* FROM Employees WHERE EmployeeID=EmployeeID();
The function EmployeeID() gets the EmployeeID number from a list box

Thanks

"=?Utf-8?B?Sm9zZSBQZXJkaWdhbw==?="

strSQL = "INSERT INTO dtPPMs ( [Date], NameID ) " & _
" SELECT inputDate() AS [Date], " & _
" axPPMs.NameID " & _
" FROM axPPMs " & _
" WHERE axPPMs.PlatformID=inputPlatform();"

DB.Execute strSQL ' not using dbFailOnError


ADO:

[comparison code snipped]

Question 1.
which attribute must I add after (cnn.execute strSQL,.....) to ignore
the msg above? I mean, if there are not records, add news set records;
if there are not, doesn’t do nothing like the example in DAO.

There isn't one, quite appropriately. Switching off dbFailOnError is just
plain bloody foolhardy, frankly. You need to be cleaning your data before
insert, not just crossing your fingers and hoping the rubbish gets
magically excluded.

Question 2
Do you think, the code in DAO and ADO above, is the best for the best
performance? (action queries)

See above.

Question 3
Is it possible to add and/or delete records in SQL Server vs ADP by
this method, I mean by action queries?

In either system, using [Date] for a column name is asking for problems,
although it might not be actually illegal. Apart from that, this query
should be valid Jet-SQL or T-SQL.

Question 4
Is it possible to use my owner functions in SQL Server vs ADP?

I don't know what an owner function is.


HTH


Tim F

 

[Tim Ferguson]

The table is protected against duplicate values, so I don't want see
this message.

So clean it. You could, for example,

import into a temp table, join it to the proper one and select records
where the [Proper Table].[ID Field] IS NULL;

mark records in the input table when they have been successfully
imported, and filter the import so they don't get to come in again;

sort out your data flows so that you don't keep feeding in out-of-date
rubbish;



My vote is for the last one, although it's often politically the hardest
to achieve. As I said before though, just crossing your fingers and
hoping the indexes are going to do your work for you is plain foolhardy.

All the best


Tim F

 

[Jose Perdigao]

Good Morning Terrel,

I type the following command
DoCmd.SetWarnings False,
Before action query and later, after the action query, but the daoesn't
work. The message is coming if I'm adding duplicate values. If I use DAO i
can solved this problem if I don't use dbFailOnError (DB.Execute strSQL ',
dbFailOnError?

Where I can typing DoCmd.SetWarnings False.
Thanks.

have you any ideia for Question 4 (How can use my fucntions in APP)?
Question 4
Owner function, I mean my functions developed in access.
Example:
SELECT Employees.* FROM Employees WHERE EmployeeID=EmployeeID();
The function EmployeeID() gets the EmployeeID number from a list box
Thanks