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
--
Billy Rogers

Dallas,TX

Currently Using Office 2000