DCount In Otherdb

D

DS

Is this correct? I need to run a DCount where the Info is in another db.
Thanks
DS

Me.TxtSubFX = Nz(DCount("ChkFXSep", "tblChecks IN
'\\Backoffice\Warehouse\History.mdb'", "ChkFXSep = -1 And ChkServer = " &
Me.TxtServerID & "And ChkBizDay BETWEEN Forms!frmReportDates!TxtStart AND
Forms!frmReportDates!TxtEnd"), 0)
 
D

Douglas J. Steele

AFAIK, you cannot use IN (nor [[;Database=C:\Folder\File.mdb].Table, which I
prefer in SQL).

One option, of course, is to create a linked table in your database.

Another is to use Automation:

Dim appAccess As Access.Application

Set appAccess = New Access.Application
appAccess.OpenCurrentDatabase " \\Backoffice\Warehouse\History.mdb "
Me.TxtSubFX = Nz(appAccess.DCount("ChkFXSep", "tblChecks", _
"ChkFXSep = -1 And ChkServer = " & Me.TxtServerID & _
"And ChkBizDay BETWEEN " & _
Format(Forms!frmReportDates!TxtStart, "\#yyyy\-mm\-dd\#") & " AND " & _
Format(Forms!frmReportDates!TxtEnd, "\#yyyy\-mm\-dd\#"), 0)
appAccess.CloseCurrentDatabase
Set accAccess = Nothing
 
D

Dirk Goldgar

Douglas J. Steele said:
AFAIK, you cannot use IN (nor [[;Database=C:\Folder\File.mdb].Table, which
I prefer in SQL).

One option, of course, is to create a linked table in your database.

Another is to use Automation:

Dim appAccess As Access.Application

Set appAccess = New Access.Application
appAccess.OpenCurrentDatabase " \\Backoffice\Warehouse\History.mdb "
Me.TxtSubFX = Nz(appAccess.DCount("ChkFXSep", "tblChecks", _
"ChkFXSep = -1 And ChkServer = " & Me.TxtServerID & _
"And ChkBizDay BETWEEN " & _
Format(Forms!frmReportDates!TxtStart, "\#yyyy\-mm\-dd\#") & " AND " & _
Format(Forms!frmReportDates!TxtEnd, "\#yyyy\-mm\-dd\#"), 0)
appAccess.CloseCurrentDatabase
Set accAccess = Nothing
Click to expand...


Wouldn't it be simpler to just open a recordset on the other database?
Along these lines:

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset( _
"SELECT Count(*) FROM " & _
"[MS Access;Database=\\Backoffice\Warehouse\History.mdb]" & _
".tblChecks " & _
"WHERE ChkFXSep = -1 And ChkServer = " & Me.TxtServerID & _
" And ChkBizDay BETWEEN " & _
Format(Forms!frmReportDates!TxtStart, "\#yyyy\-mm\-dd\#") & _
" AND " & _
Format(Forms!frmReportDates!TxtEnd, "\#yyyy\-mm\-dd\#"), _
dbOpenSnapshot)

Me.TxtSubFX = rs(0)

rs.Close
 
D

Douglas J. Steele

Dirk Goldgar said:
Douglas J. Steele said:
AFAIK, you cannot use IN (nor [[;Database=C:\Folder\File.mdb].Table,
which I prefer in SQL).

One option, of course, is to create a linked table in your database.

Another is to use Automation:

Dim appAccess As Access.Application

Set appAccess = New Access.Application
appAccess.OpenCurrentDatabase " \\Backoffice\Warehouse\History.mdb "
Me.TxtSubFX = Nz(appAccess.DCount("ChkFXSep", "tblChecks", _
"ChkFXSep = -1 And ChkServer = " & Me.TxtServerID & _
"And ChkBizDay BETWEEN " & _
Format(Forms!frmReportDates!TxtStart, "\#yyyy\-mm\-dd\#") & " AND " &
_
Format(Forms!frmReportDates!TxtEnd, "\#yyyy\-mm\-dd\#"), 0)
appAccess.CloseCurrentDatabase
Set accAccess = Nothing
Click to expand...


Wouldn't it be simpler to just open a recordset on the other database?
Along these lines:

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset( _
"SELECT Count(*) FROM " & _
"[MS Access;Database=\\Backoffice\Warehouse\History.mdb]" & _
".tblChecks " & _
"WHERE ChkFXSep = -1 And ChkServer = " & Me.TxtServerID & _
" And ChkBizDay BETWEEN " & _
Format(Forms!frmReportDates!TxtStart, "\#yyyy\-mm\-dd\#") & _
" AND " & _
Format(Forms!frmReportDates!TxtEnd, "\#yyyy\-mm\-dd\#"), _
dbOpenSnapshot)

Me.TxtSubFX = rs(0)

rs.Close
Click to expand...

Probably, but I wanted to show off! <g>

DS: Dirk's approach likely will be more efficient. In fact, if you're doing
this in a lot of places, you'd probably be better off writing your own
EDCount function (External DCount).
 
D

DS

Thanks Dirk. That looks good. One problem though just came up. I'm using
a regular DCount to get the records from the current db. And your code to
get the records from the other db containing the records that are over one
year old. But, what do I do when I have to get get records from both db's
and consolidate it down to one answer?
Thanks
DS
 
D

Dirk Goldgar

DS said:
Thanks Dirk. That looks good. One problem though just came up. I'm
using a regular DCount to get the records from the current db. And your
code to get the records from the other db containing the records that are
over one year old. But, what do I do when I have to get get records from
both db's and consolidate it down to one answer?
Click to expand...


Can you just add the result of the local DCount to the result of the
external database query? That would seem simplest, but maybe I don't
understand what you're after.

If you need to, I think you could use a UNION query that merges selected
records from the external DB with records from the local DB, and counts the
resuting records. Something like this, maybe:

'---- EXAMPLE ONLY! I DON'T KNOW ENOUGH ----

Set rs = CurrentDb.OpenRecordset( _
"SELECT Count(*) FROM (" & _
"SELECT ChkFXSep FROM " & _
"[MS Access;Database=\\Backoffice\Warehouse\History.mdb]" & _
".tblChecks " & _
"WHERE ChkFXSep = -1 And ChkServer = " & Me.TxtServerID & _
" And ChkBizDay BETWEEN " & _
Format(Forms!frmReportDates!TxtStart, "\#yyyy\-mm\-dd\#") & _
" AND " & _
Format(Forms!frmReportDates!TxtEnd, "\#yyyy\-mm\-dd\#") & _
" UNION ALL " & _
"SELECT ChkFXSep FROM tblChecks " & _
"WHERE ChkFXSep = -1 And ChkServer = " & Me.TxtServerID & _
" And ChkBizDay BETWEEN " & _
Format(Forms!frmReportDates!TxtStart, "\#yyyy\-mm\-dd\#") & _
" AND " & _
Format(Forms!frmReportDates!TxtEnd, "\#yyyy\-mm\-dd\#") & _
")", _
dbOpenSnapshot)

'---- END EXAMPLE ----

That's assuming that the table is named "tblChecks" in both databases, and
you want to count records meeting the same criteria from both tables.
 
D

DS

This loks like what I need. This is pretty much what I'm doing for the
report itself. The only confusion I have is how do I get the result into a
text field? Is it the Setrs......I think that means set recordset. I'm
going to play with it.
Thanks
DS
 
D

DS

OK, this is what I have....it seems to be working.
'CHECK SECTION
'Me.TxtVoids
If DateValue(Forms!frmReportDates!TxtStart) < DateAdd("yyyy", -1, Date)
And _
DateValue(Forms!frmReportDates!TxtEnd) < DateAdd("yyyy", -1, Date)
Then
'OLD
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT Count(*) FROM " & _
"[MS
Access;Database=\\Backoffice\Warehouse\History.mdb].tblVoidDetails " & _
"WHERE CDBizDay BETWEEN Format(Forms!frmReportDates!TxtStart,
\#yyyy\-mm\-dd\#)" & _
"AND Format(Forms!frmReportDates!TxtEnd,
\#yyyy\-mm\-dd\#),dbOpenSnapshot")
Me.TxtVoids = rs(0)
rs.Close
ElseIf DateValue(Forms!frmReportDates!TxtStart) < DateAdd("yyyy", -1,
Date) And _
DateValue(Forms!frmReportDates!TxtEnd) >= DateAdd("yyyy", -1, Date)
Then
'OLD AND NEW
Set rs = CurrentDb.OpenRecordset("SELECT Count(*) FROM " & _
"[MS
Access;Database=\\Backoffice\Warehouse\History.mdb].tblVoidDetails " & _
"WHERE CDBizDay BETWEEN Format(Forms!frmReportDates!TxtStart,
\#yyyy\-mm\-dd\#) " & _
"AND Format(Forms!frmReportDates!TxtEnd, \#yyyy\-mm\-dd\#) " & _
"UNION ALL " & _
"SELECT * FROM tblVoidDetails " & _
"WHERE CDBizDay BETWEEN
Format(Forms!frmReportDates!TxtStart,\#yyyy\-mm\-dd\#) " & _
"WHERE CDBizDay BETWEEN Format(Forms!frmReportDates!TxtStart,
\#yyyy\-mm\-dd\#)" & _
"AND Format(Forms!frmReportDates!TxtEnd,
\#yyyy\-mm\-dd\#),dbOpenSnapshot")
Me.TxtVoids = rs(0)
rs.Close
ElseIf DateValue(Forms!frmReportDates!TxtStart) >= DateAdd("yyyy", -1,
Date) And _
DateValue(Forms!frmReportDates!TxtEnd) >= DateAdd("yyyy", -1, Date)
Then
'New
Me.TxtVoids = Nz(DCount("CDCheckID", "tblVoidDetails", "CDBizDay
BETWEEN Forms!frmReportDates!TxtStart AND Forms!frmReportDates!TxtEnd"), 0)
End If

My only question is....do I need the Format part for the dates in the
recordset search?
Thanks
DS
 
D

DS

Set rs = CurrentDb.OpenRecordset("SELECT Count(*) FROM " & _
"[MS
Access;Database='\\Backoffice\Warehouse\History.mdb'].tblVoidDetails " & _
"WHERE CDBizDay BETWEEN Forms!frmReportDates!TxtStart " & _
"AND Forms!frmReportDates!TxtEnd " & _
"UNION ALL " & _
"SELECT * FROM tblVoidDetails " & _
"WHERE CDBizDay BETWEEN Forms!frmReportDates!TxtStart " & _
"AND Forms!frmReportDates!TxtEnd,dbOpenSnapshot")
Me.TxtVoids = rs(0)
rs.Close

This is giving me .."Not a Valid Path error. The Path name seems ok and the
file is there. Any suggestion what may be causing this?
Thanks
DS
 
D

DS

The History Database is on the C: drive in the Warehouse directory. Maybe
the problem is that I have a partioned harddrive on my computer. A "C"
drive and a "D" drive. but this worked before so I'm not sure what is going
on.
Thanks
DS
 
D

Dirk Goldgar

DS said:
Set rs = CurrentDb.OpenRecordset("SELECT Count(*) FROM " & _
"[MS
Access;Database='\\Backoffice\Warehouse\History.mdb'].tblVoidDetails " & _
"WHERE CDBizDay BETWEEN Forms!frmReportDates!TxtStart " & _
"AND Forms!frmReportDates!TxtEnd " & _
"UNION ALL " & _
"SELECT * FROM tblVoidDetails " & _
"WHERE CDBizDay BETWEEN Forms!frmReportDates!TxtStart " & _
"AND Forms!frmReportDates!TxtEnd,dbOpenSnapshot")
Me.TxtVoids = rs(0)
rs.Close

This is giving me .."Not a Valid Path error. The Path name seems ok and
the file is there. Any suggestion what may be causing this?
Click to expand...


I don't think the single-quotes you have placed around the path to the
History database are required, and maybe they are messing things up. When I
tested, I had no quotes. Also, you've made the dbOpenSnapshot argument part
of the SQL string, which is should not be. And I don't think your
references to the form controls are going to be recognized by the DAO
OpenRecordset method, which doesn't know about Access object. While we're
revamping it, let's make the common criterion on CBizDay a little cleaner.
Try this:

Dim strCriteria As String

strCriteria = _
"WHERE CDBizDay BETWEEN " &
Format(Forms!frmReportDates!TxtStart, "\#mm/dd/yyyy\#") & _
" AND " & _
Format(Forms!frmReportDates!TxtEnd, "\#mm/dd/yyyy\#")

Set rs = CurrentDb.OpenRecordset("SELECT Count(*) FROM " & _
"[MS Access;Database=\\Backoffice\Warehouse\History.mdb] & _
".tblVoidDetails " & strCriteria & _
" UNION ALL " & _
"SELECT * FROM tblVoidDetails " & strCriteria, _
dbOpenSnapshot)
 
D

DS

Does it ever end :)
This is giving me a syntax error. Any help appreciated.
Thanks
DS

Set rs = CurrentDb.OpenRecordset("SELECT Count(*) FROM " & _
"[MS
Access;Database=\\Backoffice\C$\Warehouse\History.mdb].tblVoidDetails " & _
"WHERE CDBizDay BETWEEN Forms!frmReportDates!TxtStart " & _
"AND Forms!frmReportDates!TxtEnd " & _
"UNION ALL " & _
"SELECT * FROM tblVoidDetails " & _
"WHERE CDBizDay BETWEEN Forms!frmReportDates!TxtStart " & _
"AND Forms!frmReportDates!TxtEnd),dbOpenSnapshot")
Me.TxtVoids = rs(0)
rs.Close
 
D

DS

Dirk,
First I tried it and I got the message that the column count wasn't equal.
Then I moved the code around and now I get a wrong criteria order!

Dim strCriteria As String
Dim rs As DAO.Recordset

strCriteria = "WHERE CDBizDay BETWEEN " &
Format(Forms!frmReportDates!TxtStart, "\#mm/dd/yyyy\#") And
Format(Forms!frmReportDates!TxtEnd, "\#mm/dd/yyyy\#")

Set rs = CurrentDb.OpenRecordset("SELECT Count(*) FROM [MS
Access;Database=\\Backoffice\C$\Warehouse\History.mdb].tblVoidDetails " &
strCriteria & _
"UNION ALL " & _
"SELECT * FROM tblVoidDetails " & strCriteria, dbOpenSnapshot)

Thanks
DS

I think we are close!
 
D

Douglas J. Steele

Your first subselect returns a single Long Integer field. Does
tblVoidDetails only have a single Long Integer field in it?

When you union queries together, each subselect must have the same number of
fields, and corresponding fields (by position) must be the same data type.
(The names don't have to agree)

As well, when using OpenRecordset, your references to form controls must be
outside of the quotes:

"WHERE CDBizDay BETWEEN " & _
Format(Forms!frmReportDates!TxtStart, "\#yyyy\-mm\-dd\#") & _
"AND " & Format(Forms!frmReportDates!TxtEnd, "\#yyyy-mm-dd\#"),
dbOpenSnapshot)

Note that you'd also mistakenly included a closing parenthesis on your SQL
statement, as well as including the dbOpenSnapshot parameter inside the
quotes.
 
D

DS

Thanks Douglas,
Yes I am looking for a Long Integer.

This almost works...it's returning only 1 db result though.

Dim strCriteria As String
Dim rs As DAO.Recordset

strCriteria = _
"WHERE CDBizDay BETWEEN " & Format(Forms!frmReportDates!TxtStart,
"\#mm/dd/yyyy\#") & _
"AND " & Format(Forms!frmReportDates!TxtEnd, "\#mm/dd/yyyy\#")

Set rs = CurrentDb.OpenRecordset("SELECT Count(*) FROM " & _
"[MS Access;Database=\\Backoffice\C$\Warehouse\History.mdb].tblVoidDetails "
& strCriteria & _
" UNION ALL " & _
"SELECT Count(*) FROM tblVoidDetails " & strCriteria, dbOpenSnapshot)
Me.TxtVoids = rs(0)
rs.Close

I don't think I need Format, do I?

This returns the correct results when run as a Query.

SELECT Count(*) FROM
[MS Access;Database=\\Backoffice\C$\Warehouse\History.mdb].tblVoidDetails
WHERE CDBizDay BETWEEN Forms!frmReportDates!TxtStart And
Forms!frmReportDates!TxtEnd
UNION ALL SELECT Count(*) FROM tblVoidDetails
WHERE CDBizDay BETWEEN Forms!frmReportDates!TxtStart And
Forms!frmReportDates!TxtEnd;

Thanks
DS
 
D

DS

This is for the Old records only....This is only returning 0 even though
there are 3 records.

'OLD
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT Count(*) " & _
"FROM [MS
Access;Database=\\Backoffice\C$\Warehouse\History.mdb].tblVoidDetails " & _
"WHERE tblVoidDetails.CDBizDay Between " & Forms!frmReportDates!TxtStart & "
And " & Forms!frmReportDates!TxtEnd, dbOpenSnapshot)
Me.TxtVoids = rs(0)
rs.Close
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Syntax Error 4
From External Database 5
DCount Syntax 2
Are Nulls Covered? 2
Criteria Mismatch 4
DCount Error 6
Not returning a value 7
UNION QUERY (Sorting) 2

Top