DCount In Otherdb

[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)

 

[Stefan Hoffmann]

hi,

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

You're allowed to test it. What does it return?


mfG
--> stefan <--

 

[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

 

[DS]

It returns 0.
Thanks
DS

 

[DS]

Thanks Douglas,
I think I'll use the Automation. This looks like what I need.
DS

 

[Dirk Goldgar]

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

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

 

[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

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

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).