DCount

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Group

Is there a way to use the DCount() function using a path statement.
Something like this numRecords1 = DCount(1, "I:\Data
Bases\DataFiles\Subdatabases Before Merging\Work in Progress WHIP.mdb
tblWorkInProgressData")

I need to count the number of records from tblWorkInProgressData .
Which is contained in the database I:\Data Bases\DataFiles\Subdatabases
Before Merging\Work in Progress WHIP.mdb.

Thanks
Mike Sun d man
 
I believe that you have to link to that table in that db and then you can
use the DCount.

You can link manually from the database window, or you can use the
TransferDatabase command in VB to link.
 
JP

I'm going through the process of UNLINKING all of my linked tables. I've
databases that take anywhere from 15sec to 5minutes to load. I've tried to
streamline things via the link from this group that people usually give for
troubleshooting Access running slowly. To me it is not worth it at all. Since
I have been poking around on theses newsgroups It seems that Access running
slow is usually synonymous with linked tables.
I've done some testing on this. Here is what I did. I created two databases,
in one I created a table with some data then I imported the table that was
created into database two. I also created another table in database two then
queried the data. When the query was ran it instantly came up with data.
However when I deleted the native table and linked it in from the other
database the query seemed to take forever – what I mean by this is that it
took maybe 5 to 10 seconds to come up. This seems like a small complaint but
when you have 3gig machine with 1 gig of ram running on a gigahertz network
seconds seem like forever. I also have about 10 users connecting to it all at
the same time this is where I end up having the 15sec to 5 min forms.
So what I’m doing now is checking to see if the data needs to be updated.
I’m currently using code that enters the now() function into a table with a
list of unlinked tables. The next time the database is opened it checks to
see how out of date the records are and if they are older than I deem
necessary then they run a make table query and overwrite the data. The
database that I’m working on now needs to have also a finer update parameter
for this I use the DCount() and compare the local records to the new records
and if there is a difference other that <>0 then the tables are updated. So
here is the function that I created.

Function RCtblWorkInProgressData(strPath, strFile, strTable)
Dim rst As New ADODB.Recordset
Dim cnn As New ADODB.Connection
Dim strOpeningStatement As String

strOpeningStatement = "Provider=Microsoft.Jet.OLEDB.4.0; Data " & _
"Source=" & strPath & "\" & strFile & "; Jet OLEDB:"

'MsgBox strOpeningStatement

cnn.Open strOpeningStatement
rst.Index = "PrimaryKey"
rst.Open strTable, cnn, adOpenStatic, adLockOptimistic, adCmdTableDirect
RCtblWorkInProgressData = rst.RecordCount

End Function

Here is how I use it.

numWorkInProgressData = RCtblWorkInProgressData("I:\Data
Bases\DataFiles\Subdatabases Before Merging", "Work in Progress WHIP.mdb",
"tblWorkInProgressData")

It seems to not effect speed very much.

Mike Sun d man
 
Instead of using DCount, write the corresponding SQL statement , using
an IN clause to specify the .mdb file where the table lives, e.g.

SELECT COUNT(1) AS Records
FROM FAQ
IN 'C:\Documents and Settings\All Users\Application
Data\AgentStore\NewsgroupAnswers2002.mdb';

Then open a recordset using that SQL; the first field in the recordset
has the number of records.
 
Back
Top