count of records in a table in external database

[Ben8765]

Hi,

I would like to get the record count of a table in an external database
(using the name of the table in the loop of the current database).

What I've tried (doesn't work):

SourceDbCount = DCount("*", "[GOOD_DATA_SAR_Recovery_Tracking_db.mdb].[" &
rs!Name & "]")



The procedure (**** is where the problem line is):

------------------------------

Public Sub AppendTableRecords()

Dim strSelectAllTables As String

strSelectAllTables = "SELECT NAME FROM MSysObjects WHERE Type=1 AND Name not
like 'MSys%';"

Dim rs As New ADODB.Recordset
rs.Open strSelectAllTables, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

DoCmd.SetWarnings False

While Not rs.EOF


DestinationDbCount = DCount("*", rs!Name)

'****
SourceDbCount = DCount("*", "[GOOD_DATA_SAR_Recovery_Tracking_db.mdb].[" &
rs!Name & "]")
'****

CountDifference = SourceDbCount - DestinationDbCount


rs.MoveNext
Wend
DoCmd.SetWarnings True

rs.Close

Set rs = Nothing

Close #iFileNo

End Sub

---------------------------

How can I get the record count of a table in an external db using 'rs!Name'
as the external database's table name?

-Ben

 

[Dirk Goldgar]

Hi,

I would like to get the record count of a table in an external database
(using the name of the table in the loop of the current database).

What I've tried (doesn't work):

SourceDbCount = DCount("*", "[GOOD_DATA_SAR_Recovery_Tracking_db.mdb].[" &
rs!Name & "]")



The procedure (**** is where the problem line is):

------------------------------

Public Sub AppendTableRecords()

Dim strSelectAllTables As String

strSelectAllTables = "SELECT NAME FROM MSysObjects WHERE Type=1 AND Name
not
like 'MSys%';"

Dim rs As New ADODB.Recordset
rs.Open strSelectAllTables, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

DoCmd.SetWarnings False

While Not rs.EOF


DestinationDbCount = DCount("*", rs!Name)

'****
SourceDbCount = DCount("*", "[GOOD_DATA_SAR_Recovery_Tracking_db.mdb].[" &
rs!Name & "]")
'****

CountDifference = SourceDbCount - DestinationDbCount


rs.MoveNext
Wend
DoCmd.SetWarnings True

rs.Close

Set rs = Nothing

Close #iFileNo

End Sub

---------------------------

How can I get the record count of a table in an external db using
'rs!Name'
as the external database's table name?

Dim dbExt As DAO.Database

Set dbExt = DBEngine.OpenDatabase( _
"GOOD_DATA_SAR_Recovery_Tracking_db.mdb")

' ...

SourceDbCount = dbExt.TableDefs(rs!Name).RecordCount

' ...

dbExt.Close

If you're doing this in a loop, you're goiing to want to open the external
database outside the loop, so as not to repeat that overhead in every
iteration.

I believe that the RecordCount property should be accurate for a TableDef.
If it isn't, you would have to open a recordset from dbExt on a SELECT
Count(*) query, and get the count from the recordset.

 

[Ben8765]

Thanks a lot for your reply. I will try this tomorrow.

-ben

Hi,

I would like to get the record count of a table in an external database
(using the name of the table in the loop of the current database).

What I've tried (doesn't work):

SourceDbCount = DCount("*", "[GOOD_DATA_SAR_Recovery_Tracking_db.mdb].[" &
rs!Name & "]")



The procedure (**** is where the problem line is):

------------------------------

Public Sub AppendTableRecords()

Dim strSelectAllTables As String

strSelectAllTables = "SELECT NAME FROM MSysObjects WHERE Type=1 AND Name
not
like 'MSys%';"

Dim rs As New ADODB.Recordset
rs.Open strSelectAllTables, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

DoCmd.SetWarnings False

While Not rs.EOF


DestinationDbCount = DCount("*", rs!Name)

'****
SourceDbCount = DCount("*", "[GOOD_DATA_SAR_Recovery_Tracking_db.mdb].[" &
rs!Name & "]")
'****

CountDifference = SourceDbCount - DestinationDbCount


rs.MoveNext
Wend
DoCmd.SetWarnings True

rs.Close

Set rs = Nothing

Close #iFileNo

End Sub

---------------------------

How can I get the record count of a table in an external db using
'rs!Name'
as the external database's table name?

Dim dbExt As DAO.Database

Set dbExt = DBEngine.OpenDatabase( _
"GOOD_DATA_SAR_Recovery_Tracking_db.mdb")

' ...

SourceDbCount = dbExt.TableDefs(rs!Name).RecordCount

' ...

dbExt.Close

If you're doing this in a loop, you're goiing to want to open the external
database outside the loop, so as not to repeat that overhead in every
iteration.

I believe that the RecordCount property should be accurate for a TableDef.
If it isn't, you would have to open a recordset from dbExt on a SELECT
Count(*) query, and get the count from the recordset.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)