List Tables AND Table Row Count from Remote Database into List Box 1

[Accel45]

I am using this to get a list of Tables from a Remote Database and place the list of Tables into a Listbox.
This is working.

Dim rRemoteDB As String
rRemoteDB = "C:\Test\RemoteDB.accdb"
Me![List40].RowSource = "SELECT [Name] FROM MSysObjects IN '" & rRemoteDB & "'" _
& " WHERE [Type]=1 AND NOT ([Name] Like '~*' OR [Name] Like 'MSys*') ORDER BY 1"

What I would like to do is add a second column to the Listbox that contains a count of the Rows In each of the Remote Database Tables.

This is my (best) Failed Attempt:

Dim rRemoteDB As String
rRemoteDB = "C:\Test\RemoteDB.accdb"
Me![List40].RowSource = "SELECT [Name]," & "DCount(" & Chr(34) & "*" & Chr(34) & ",[Name])" & "AS Rows FROM MSysObjects IN '" & rRemoteDB & "'" & _
'" WHERE ([Type]=1) AND NOT ([Name] Like '~*' OR [Name] Like 'MSys*') ORDER BY 1"

My Failed Attempt;
Returns the Remote Table Names;
Does not return a Row Count for Tables in the Remote Database;
Returns a Row Count If a Table by the same Name is in the Local Database otherwise, the second column is Error.

Any help making this work would be appreciated

Accel45

 

[Andrzejek]

Just a guess here:
[tt]
"SELECT [Name], [blue](SELECT COUNT(*) From [Name]) AS RowCount[/blue] FROM MSysObjects IN '" & rRemoteDB & "'" _
& " WHERE [Type]=1 AND NOT ([Name] Like '~*' OR [Name] Like 'MSys*') ORDER BY 1"
[/tt]
or
[tt]
"SELECT [Name], [blue](SELECT COUNT(*) From [Name] IN '" & rRemoteDB & "'") AS RowCount[/blue] FROM MSysObjects IN '" & rRemoteDB & "'" _
& " WHERE [Type]=1 AND NOT ([Name] Like '~*' OR [Name] Like 'MSys*') ORDER BY 1"
[/tt]


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Accel45]

Andy,

Thank you for taking time to help, but neither option worked.
Neither option brought in anything (Tables nor Row Counts) from the Remote Database.

Accel45

 

[Andrzejek]

I know you can get the Names of the tables from Remote Database.

Can you get just the Count of Rows of any (one) table once you have a table's Name?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Accel45]

I cobbled together the code below which is currently getting done what I need.
However, if a Table is in the Remote Database that I have not placed in the code it will be missed.
Also, creating all the necessary text boxes to simulate a Listbox is a pain.

Looking for ideas...
Accel45

Code:
Function CtRemoteTableRows()
Dim rAccount As String
rAccount = "tblAccount"

Dim rCategory As String
rCategory = "tblCategory"

Dim rDoorNotes As String
rDoorNotes = "tblDoorNotes"

Dim rExportLocation As String
rExportLocation = "tblExportLocation"

Dim rHideFrame As String
rHideFrame = "tblHideFrame"

Dim rHistory As String
rHistory = "tblHistory"

Dim rSettings As String
rSettings = "tblSettings"

Dim rRemoteDB As String
rRemoteDB = Me.DBUpDate

Dim objAccess As Access.Application
Set objAccess = New Access.Application
objAccess.OpenCurrentDatabase rRemoteDB

On Error Resume Next
Me.txtAccount = rAccount
Me.CtAccount = objAccess.DCount("*", rAccount)
On Error GoTo 0

On Error Resume Next
Me.txtCategory = rCategory
Me.CtCategory = objAccess.DCount("*", rCategory)
On Error GoTo 0

On Error Resume Next
Me.txtDoorNotes = rDoorNotes
Me.CtDoorNotes = objAccess.DCount("*", rDoorNotes)
On Error GoTo 0

On Error Resume Next
Me.txtExportLocation = rExportLocation
Me.CtExportLocation = objAccess.DCount("*", rExportLocation)
On Error GoTo 0

On Error Resume Next
Me.txtHideFrame = rHideFrame
Me.CtHideFrame = objAccess.DCount("*", rHideFrame)
On Error GoTo 0

On Error Resume Next
Me.txtHistory = rHistory
Me.CtHistory = objAccess.DCount("*", rHistory)
On Error GoTo 0

On Error Resume Next
Me.txtSettings = rSettings
Me.CtSettings = objAccess.DCount("*", rSettings)
On Error GoTo 0

objAccess.CloseCurrentDatabase
Set objAccess = Nothing

End Function

 

[Andrzejek]

WOW! Too much hard-coding, in my opinion.

Since you get all table names, and you just need to knoe row count from each table...

I am a 'recordset guy', so I would do this:

[green]'pseudo code[/green]
rstTabName.Open "SELECT [Name] FROM MSysObjects IN '" & rRemoteDB & "'" _
& " WHERE [Type]=1 AND NOT ([Name] Like '~*' OR [Name] Like 'MSys*') ORDER BY 1"

Do While Not rstTabName.EOF[green]
    'Get record count from each table[/green]
    rstCount.Open [blue]"Select Count(*) As RowCount From " & rstTabName!Name.value[/blue]

    Debug.Print rstTabName!Name.value & " has " & rstCount!RowCount.Value & " records."

    rstTabName..MoveNext
Loop

You would have to modify the BLUE Sql to get the record count for a particular table

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Accel45]

Andy,

Here is my latest attempt.
While it does work, I know it is not correct because it moves like an old Chevy Vega, slow and jery.

Dim rRemoteDB As String
rRemoteDB = Me.DBUpDate
Dim tdf As TableDef
'Set dbs = DBEngine.OpenDatabase("U:\Merge\A MAIN TABLES\DateCompareFORTEST.accdb")
Set dbs = DBEngine.OpenDatabase(rRemoteDB)
For Each tdf In dbs.TableDefs

With tdf
If .Name Like "MSys*" Or .Name Like "~*" Then
Else
Dim rAccount As String
rAccount = .Name
Dim objAccess As Access.Application
Set objAccess = New Access.Application
objAccess.OpenCurrentDatabase rRemoteDB

rstCount = objAccess.DCount("*", rAccount)
'List21.AddItem .Name
List21.AddItem .Name & ";" & rstCount
End If

End With
Next tdf
Set tdf = Nothing
Set dbs = CurrentDb

objAccess.CloseCurrentDatabase
Set objAccess = Nothing

Accel45

 

[Andrzejek]

If you have 100 tables, you execute these lines 100 times:
[tt]
Dim rAccount As String
Dim objAccess As Access.Application
Set objAccess = New Access.Application
objAccess.OpenCurrentDatabase rRemoteDB
[/tt]
This is just my guess here, but try to re-arrange your code a little to something like this:

Dim rRemoteDB As String
Dim tdf As TableDef
Dim rAccount As String
Dim objAccess As Access.Application

rRemoteDB = Me.DBUpDate

'Set dbs = DBEngine.OpenDatabase("U:\Merge\A MAIN TABLES\DateCompareFORTEST.accdb")
Set dbs = DBEngine.OpenDatabase(rRemoteDB)

Set objAccess = New Access.Application
objAccess.OpenCurrentDatabase rRemoteDB

For Each tdf In dbs.TableDefs
  With tdf
    If Not(.Name Like "MSys*" Or .Name Like "~*") Then
      rAccount = .Name
      rstCount = objAccess.DCount("*", rAccount)
      'List21.AddItem .Name
      List21.AddItem .Name & ";" & rstCount
    End If
  End With
Next tdf

Set tdf = Nothing
Set dbs = CurrentDb

objAccess.CloseCurrentDatabase
Set objAccess = Nothing

I do all my declaration at the top, never in the middle of the procedure.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Accel45]

Andy,

Very smooth, thank you for your help.

Acce45

 

[Andrzejek]

You are welcome
Does it work any faster?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Accel45]

Yes, Much faster!