dcount to get results from remote table?

D

Dave

Can anyone tell me if you can use the DCOUNT function to get the count of
records from a table in a remote database? How would you go about doing this?
 
D

Douglas J Steele

Without having created a linked table in your current database?

You'd need to instantiate a reference to the Access Application object,
assign a database to it using the OpenCurrentDatabase method and then use
the DCount:

Dim objAccess As Access.Application

Set objAccess = New Access.Application
objAccess.OpenCurrentDatabase "C:\MyFolder\MyFile.mdb"
MsgBox "There are " & objAccess.DCount("*", "TableName") & " rows in
table TableName"
objAccess.CloseCurrentDatabase
Set objAccess = Nothing


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Dave said:
Can anyone tell me if you can use the DCOUNT function to get the count of
records from a table in a remote database? How would you go about doing
Click to expand...
this?
 
D

dave

Douglas,

Thanks for the reply. Forgive me for asking a couple of newbie question
but:
When I put in your code, the code stops running until I hit open to the
remote database. Is there any way to automatically answer that question so
no user input is needed? If so your code is exactly what I need.

Here was the code I was playing with. I can get the remote query written I
just do not know how to exedute it and bring back a count

Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef
Dim stDocName As String
Set dbCurr =
OpenDatabase("O:\cm_prod_boms\m88\mmas_audit\LastAudit\LastRecordsAuditedBackup.mdb")


strSQL = "select count(*) from LastRecordsAudited"
Set qdfCurr = dbCurr.CreateQueryDef("qry_test", strSQL)
Value = DCount("*", "qry_test")
dbCurr.QueryDefs.Delete "qry_RecordsToAudit"
 
D

Douglas J Steele

Oh yeah. I wasn't using Access 2003 when I tested that code, and I forgot
about that.

Can you not simply create a linked table to the other database?

You could do that through code if you really didn't want the linked table
permanently there. Something like the following untested air-code:

Dim dbCurr As DAO.Database
Dim tdfNew As DAO.TableDef
Dim strTableName As String

strTableName = "linked_qry_test_" & Format(Now(),
"yyyy\-mm\-dd\-hh\-nn\-ss")

Set dbCurr = CurrentDb()
Set tdfNew = dbCurr.CreateTableDef("linked_qry_test")
tdfNew.Connect =
"Database=O:\cm_prod_boms\m88\mmas_audit\LastAudit\LastRecordsAuditedBackup.
mdb"
tdfCurr.SourceTableName = "qry_test"
dbCurr.TableDefs.Append tdfCurrent
dbCurr.TableDefs.Refresh

value = DCount("*", strTableName)

dbCurr.TableDefs.Delete strTableName
 
Top