B
BillyRogers
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(xlCellTypeLastCell)
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
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(xlCellTypeLastCell)
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