output record count as a table?

[Guest]

I have written some VB code attached to a button on a form that imports 2
tables from a another database, and appends any new values to equivalent
tables in the current database. Within the same code, I would like to create
a table that lists the number
of records that were successfully added to each table.

I thought I could use a call to DCount at the beginning of my code to create
variables that contain the record count of each table before the code runs,
and then use DCount again at the end to count the records now in each table.
Then I could calculate the difference. However I am pretty new to VB and am
unsure how to put the resulting calculated values in a table (or even assign
them to any kind of object?)… also, if there is a much more efficient way to
do this let me know, because the DCount seems a bit slow when it runs…

My tables are “LogData†(with Primary key a combination of the fields
“LoggerID†& “LogUTCâ€) and “Readings†(with Primary key a combination of the
fields “LoggerID†& “ReadingFromâ€).

My code is currently as follows:

Private Sub command1_Click()
Dim LogDataCountStart As Long
LogDataCountStart = DCount("LoggerID & LogUTC", "LogData")

Dim ReadingsCountStart As Long
ReadingsCountStart = DCount("LoggerID & ReadingFrom", "Readings")

…. Rest of code that runs the SQL for the append queries that actually adds
the data, I have tested this and it works fine….

Dim LogDataCountEnd As Long
LogDataCountEnd = DCount("LoggerID & LogUTC", "LogData")

Dim ReadingsCountEnd As Long
ReadingsCountEnd = DCount("LoggerID & ReadingFrom", "Readings")

Dim NewLogData As Long
NewLogData = LogDataCountStart - LogDataCountEnd
Dim NewReadings As Long
NewReadings = ReadingsCountStart – ReadingsCountEnd

End Sub

 

[Steve Schapel]

Fishcakes,

How many records are we talking about here? You should not be able to
detect DCount "running" with the human eye? Mind you, there is no need
to concatenate the fields together like that. This would be fine (and
more efficient)...
LogDataCountStart = DCount("*","LogData")

On the whole, I think the general concept is fine.

So, you didn't really say what you want to end up with, but assuming you
have a table with these fields...
WhenAdded
TableName
NewRecords
.... then the code to follow on at the end of your example might be
something like this...

CurrentDb.Execute "INSERT INTO YourTable ( WhenAdded, TableName,
NewRecords )" & _
" VALUES ( " & Now & ", 'LogData', " & NewLogData & "
)", dbFailOnError
CurrentDb.Execute "INSERT INTO YourTable ( WhenAdded, TableName,
NewRecords )" & _
" VALUES ( " & Now & ", 'Readings', " & NewReadings & "
)", dbFailOnError

 

[Guest]

Hi Steve,
this looks great! I set up the table with the field names you suggested and
added the code... However, I am now getting an error message that seems to
relate somehow to the Now function...

The error message is:
Runtime error ‘3075’: Syntax error (missing operator) in query expression
‘31/05/2006 9:56:12 AM’.

The code (in case there is a simple typo I have missed that is obvious to
everyone else in the know) is:
CurrentDb.Execute "INSERT INTO rpt_thermo_import_data " & _
"( WhenAdded, TableName,NewRecords )" & _
" VALUES ( " & Now & ", 'LogData', " & NewLogData & ")", dbFailOnError
CurrentDb.Execute "INSERT INTO rpt_thermo_import_data " & _
"( WhenAdded, TableName,NewRecords )" & _
" VALUES ( " & Now & ", 'Readings', " & NewReadings & ")",
dbFailOnError

"rpt_thermo_import_data" is the name of the table I set up to hold the
import results, with the field names exactly as you suggested.... When added
is a general date field, TableName is text and NewRecords is a long Integer...

 

[Steve Schapel]

Fishcakes,

My apologies, I forgot the date delimiter # in the code I gave you. Try
it like this...

CurrentDb.Execute "INSERT INTO rpt_thermo_import_data " & _
"( WhenAdded, TableName, NewRecords )" & _
" VALUES ( #" & Now & "#, 'LogData', " & NewLogData & " )"

 

[Guest]

Hi Steve,
thankyou sooooo much! it works great now! (I thought it might be that i
needed some #'s, but I didn't know where to put them exactly, and the help
file said that the Syntax for the Now function, was just "Now" ....not very
enlightening for a beginner).