Hi BillyRogers,

You can use the OpenSchema method of the Connection object to return a
resultset of the indexes a table has. It would be easier to manage if you
had a Connection object set up - then you could open the recordset and the
schema from that same connection.

Dim cn As ADODB.Connection
Dim rs2 As ADODB.Recordset

Set cn = New ADODB.Connection

With cn
.ConnectionString = "<your connection string here"
.Open
End With

Set rs2 = cn.OpenSchema(adSchemaIndexes, _
Array(Empty, Empty, Empty, Empty, table)) '/ assumes table var
contains table name
rs.Find ("PRIMARY_KEY = True")

If Not (rs.EOF Or rs.BOF) Then
MsgBox "table contains primary key"
Else
MsgBox "no primary key"
End If

rs2.Close
Set rs2 = Nothing

To open your other Recordset, you can use the Execute method of the
Connection object. And you don't need to set rs to a new Recordset - that
will be done via the return value of the Execute method:

Set rs = cn.Execute("SELECT COUNT(*) AS [Count] FROM [" & table &
"]")

This way, you can reuse the connection. Hopefully this makes some sense and
works for you.

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

BillyRogers wrote:
Here a QA program that I have in Excel. the user clicks a button
then this program loops through all the mdb files in a folder on our
network. Each database has the same 12 tables but the databases all
have different names. The program does a query on each table---just a
simple record cound and then prints the database name, table name and
record cound in the spreadsheet. it works really well and has
reduced a 6 hour job to about 20 minutes. there's just one more
thing I need to add. I need to check each table for a primary key.
If there is one I would like to add it to this line as a variable.

ActiveCell.FormulaR1C1 = DBName & ";" & table & ";" & _
rs.Fields("Count").Value

thanks,


Sub CommandButton1_Click()
Dim rs As Recordset
Dim SQlcmd As String
Dim myTables As Variant
Dim table As Variant
Dim DBName As String
Dim rows As Integer

Call testfile

On Error Resume Next

Set LastCellInA = Sheet3.Range("A:A").SpecialCells(xlCellTypeLastCel l)
UsedRowsInA = LastCellInA.Row



myTables = Array("[FDMS Billing Fees]", _
"[FDMS Card Entitlements]", _
"[FDMS Card Specific Amex]", _
"[FDMS FEE History]", _
"[FDMS financial history]", _
"[FDMS financial history 2]", _
"[FDMS Link New Xref]", _
"[FDMS Merchant ABA/DDA New]", _
"[FDMS Merchant Funding Category DDAs]", _
"[FDMS Merchant Control Data]", _
"[tblFDMSInternationalGeneral]", _
"[tbl_FDMS_PhaseII_Additional_info]")



'DBName = ListBankNames.Value


For rows = 1 To UsedRowsInA

DBName = Sheet3.Cells(rows, 1)


For Each table In myTables

SQlcmd = "Select Count(*) as [Count] From " & table

Set rs = New ADODB.Recordset

rs.Open Source:=SQlcmd, _
ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4. 0; Data
Source=N:\Data Warehouse\Dallas\MASSCD\AccessDatabases\" + _
DBName + "; User Id=admin; Password="

Range("A65000").End(xlUp).Offset(1, 0).Activate




ActiveCell.FormulaR1C1 = DBName & ";" & table & ";" & _
rs.Fields("Count").Value

Next table

Next rows

Application.DisplayAlerts = False

Call TextToColumns

Application.DisplayAlerts = True
End Sub


Public Sub testfile()
Dim fso, fo, fl, f
Dim r
r = 1
Set fso = CreateObject("Scripting.filesystemobject")
Set fo = fso.getfolder("N:\Data
Warehouse\Dallas\MASSCD\AccessDatabases\") Set fl = fo.Files
For Each f In fl
If Right(f.Name, 3) = "mdb" Then
Sheet3.Cells(r, 1) = f.Name
r = r + 1
End If
Next
End Sub