Reporting number of records ADDED and UPDATED

[ejstefl]

Hello all,

I have a command button that runs 2 queries - an update query, and an append query. I'd like a message box to pop up when its done saying something to the effect of "You've added 2 records and updated 4 records."

My current solution is below - but with two problems. The first is it is very slow. The second is that as soon as you click on the button, although there is no hourglass, the system is "frozen" as the DCount works. What alternatives do I have??

Private Sub cmdUpdate_Click()
If IsNull(DCount("CUSIP_ID", "dbo_ASSET Without Matching tblFunds")) Then
      strAdded = "0"
Else
      strAdded = DCount("CUSIP_ID", "dbo_ASSET Without Matching tblFunds")
End If

If IsNull(DCount("CUSIP", "tblFunds Without Matching dbo_ASSET")) Then
      strUpdated = "0"
Else
      strUpdated = DCount("CUSIP", "tblFunds Without Matching dbo_ASSET")
End If

DoCmd.SetWarnings (False)
DoCmd.OpenQuery ("qryAppendNewCUSIPS")
DoCmd.OpenQuery ("qryUpdateNewNames")
DoCmd.SetWarnings (True)

Me.Requery

strMessage = "You have successfully added " & strAdded & " CUIP(s) and updated " & strUpdated & " CUSIPs."

MsgBox (strMessage)

End Sub

Thanks in advance for your expert help!!

Eric

 

[sfreeman@co.mer]

Not sure what exactly you want to accomplish, except to speed it up. You are running two queries that we do not see so they may be the problem.

You need to declare your variables, strAdded and strUpdated [suggest you change them from string vars to int].

Suggested changes [but do not see much of a speed increase - code not tested-may need tweaking]:

Private Sub cmdUpdate_Click()
strAdded = "0"
strUpdated = "0"

DoCmd.Hourglass True

If Not IsNull(DCount("CUSIP_ID", "dbo_ASSET Without Matching tblFunds"))
then strAdded = DCount("CUSIP_ID", "dbo_ASSET Without Matching tblFunds")

If Not IsNull(DCount("CUSIP", "tblFunds Without Matching dbo_ASSET")) Then strUpdated = DCount("CUSIP", "tblFunds Without Matching dbo_ASSET")

DoCmd.Hourglass False
DoCmd.SetWarnings (False)
DoCmd.OpenQuery ("qryAppendNewCUSIPS")
DoCmd.OpenQuery ("qryUpdateNewNames")
DoCmd.SetWarnings (True)

Me.Requery

strMessage = "You have successfully added " & strAdded & " records and updated " & strUpdated & " records."

MsgBox (strMessage)

End Sub

 

[ejstefl]

If you remove the setwarnings off, Access will give you the messages. You don't have to do anything.

That is true - however, it will also ask the user to confirm that they want to run each query, etc. I want to make it as simple as possible - click one button, and that's it.

I'm pretty sure my speed issue is because in reality, I'm running each query twice. Once to count the number of records added/updated, and once to actual add and update. Is there a faster way of obtaining the number of records than using DCount that still allows me to store them in a variable for a custom message box?

 

[ejstefl]

Not sure what exactly you want to accomplish, except to speed it up. You are running two queries that we do not see so they may be the problem.

You need to declare your variables, strAdded and strUpdated [suggest you change them from string vars to int].

Suggested changes [but do not see much of a speed increase - code not tested-may need tweaking]:

Private Sub cmdUpdate_Click()
strAdded = "0"
strUpdated = "0"

DoCmd.Hourglass True

If Not IsNull(DCount("CUSIP_ID", "dbo_ASSET Without Matching tblFunds"))
then strAdded = DCount("CUSIP_ID", "dbo_ASSET Without Matching tblFunds")

If Not IsNull(DCount("CUSIP", "tblFunds Without Matching dbo_ASSET")) Then strUpdated = DCount("CUSIP", "tblFunds Without Matching dbo_ASSET")

DoCmd.Hourglass False
DoCmd.SetWarnings (False)
DoCmd.OpenQuery ("qryAppendNewCUSIPS")
DoCmd.OpenQuery ("qryUpdateNewNames")
DoCmd.SetWarnings (True)

Me.Requery

strMessage = "You have successfully added " & strAdded & " records and updated " & strUpdated & " records."

MsgBox (strMessage)

End Sub

Thanks for the suggestions! I've incorporated them into my code. My hourglass problem is now gone, but I still have the issue of having to run each query twice (once to get a count of records, and once to do the update). Any further suggestions are welcome!!

Thanks,
Eric

 

[ejstefl]

Sorry - this probably wasn't clear before. qryAppendNewCUSIPS is based on the query dbo_ASSET Without Matching tblFunds.

qryUpdateNewNames is based on the query tblFunds Without Matching dbo_ASSET.

Basically, the first one looks for any records in a SQL table that are not in my local table, and then appends them to my local table.

The second one looks at any records in my local table that exist but have had a name change in the SQL table, and updates the new names to the existing record in my local table.

 

[cable]

not positive about this:

but would using ADO help? I know my VB app using ADO to talk to an Access db can return the number of records affected by a query. I dunno if ADO inside access can do the same thing??

 

[RoyVidar]

You can return records affected with both ADO and DAO.

Let's say you have a stored query (update or insert), then:

dim db as dao.database
dim qd as dao.querydef
set db = currentdb
set qd = db.querydefs("myquery")
qd.execute
messagebox qd.recordsaffected

Should the query contain parameters from forms, then you'd need to resolve those

dim db as dao.database
dim qd as dao.querydef
dim prm as dao.parameter
set db = currentdb
set qd = db.querydefs("myquery")
for each prm in qd.parameters
prm.value=eval(prm.name)
next prm
qd.execute
messagebox qd.recordsaffected

ADO version - note - if there are parameters from forms, this will only work in 2002+ versions. Access 2000 doesn't populate the parameters collection of the command object properly, unless it's retrieved thru ADOX. I'm not entirely sure, but I think ADO sometimes "gives up" due to the complexity of the query (usage of subqueries, access or user defined functions...)

dim cmd as adodb.command
dim prm as adodb.parameter
dim lngRecAff as long
set cn = currentproject.connection
set cmd = new adodb.command
with cmd
.activeconnection=currentproject.connection
.properties("Jet OLEDB:Stored Query") = true
.commandtext="myquery"
' if parameters
for each prm in .parameters
prm.value=eval(prm.name)
next prm
.execute lngRecAff
end with
messagebox lngRecAff
set prm=nothing
set cmd=nothing

- typed not tested...