count of records in a table in external database

  • Thread starter Thread starter Ben8765
  • Start date Start date
B

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
 
Ben8765 said:
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?
Click to expand...



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.
 
Ben, Not really sure where you were going with the AppendTableRecords procedure.
I created a Acc2003 routine using DAO that may help you find the number of records in TableX in the currentdb and an external database.
You have to supply the Table name and the full path to the external database.

Hope it's helpful to you. Be careful of line wrapping!!

Code: 
'---------------------------------------------------------------------------------------
    ' Procedure : JGetRecCountinTableInTwoDB
    ' Author	: Jack
    ' Created   : 11/27/2009
    ' Purpose   : To get the record count of a named table in
    '			1)the current database, and
    '			2)a named database.
    '---------------------------------------------------------------------------------------
    ' Last Modified:
    '
    ' Inputs: N/A
    ' Dependency: N/A
    '------------------------------------------------------------------------------
    '
    Sub JGetRecCountinTableInTwoDB()
    Dim dbthis As DAO.Database  'current database
    Dim dbThat As DAO.Database  'external database
    Dim rs As DAO.Recordset
    Dim tbl As DAO.TableDef
    Dim btablefound As Boolean
    Dim sTableName As String
       On Error GoTo JGetRecCountinTableInTwoDB_Error
    
     sTableName = "Main" '***Your table name goes here ***************************************
     '*********Current database ****************
    Set dbthis = CurrentDb
    For Each tbl In dbthis.TableDefs
    	If tbl.Name = sTableName Then
    	btablefound = True
    	 Set rs = dbthis.OpenRecordset(sTableName)
    	  rs.MoveLast
    	  MsgBox "Table " & sTableName & " Has " & rs.RecordCount & " records in the current database"
    	  rs.Close
    	  Exit For
    	Else
    	btablefound = False
    	End If
    Next tbl
    	If btablefound = True Then
    	Else
    	 MsgBox "Table " & sTableName & "  does not exist in the current database"
    	End If
    
     
    '*********External Database *******************************
  '
  '								 You must supply full path and name of external database
    Set dbThat = DBEngine.OpenDatabase("C:\Documents and Settings\NewAdmin\My Documents\MyForumsAndPwd.mdb")
    
    For Each tbl In dbThat.TableDefs
    	If tbl.Name = sTableName Then
    	btablefound = True
    	 Set rs = dbThat.OpenRecordset(sTableName)
    	  rs.MoveLast
    	  MsgBox "Table " & sTableName & " Has " & rs.RecordCount & " records in the external database"
    	  rs.Close
    	  dbThat.Close
    	  Exit For
    	Else
    	btablefound = False
    	End If
    Next tbl
    	If btablefound = True Then
    	Else
    	 MsgBox "Table " & sTableName & "  does not exist in the external database"
    	End If
    
       On Error GoTo 0
       Exit Sub
    
    JGetRecCountinTableInTwoDB_Error:
    
    	MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure JGetRecCountinTableInTwoDB of Module TopNInGroup"
    End Sub
 
Last edited:
Thanks a lot for your reply. I will try this tomorrow.

-ben

Dirk Goldgar said:
Ben8765 said:
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?
Click to expand...



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)
Click to expand...
 
Back
Top