Here is some code that I have in excel. We use it to do a QA on about 70
cd's we receive each month. All the cd's have an Access database on them
with the same 12 table names that we do a record count on. Each database has
a different name.

The process works great, except for one cd we receive which is an access
databse in a zip file on the cd. I know the name of the database that is
zipped. It's the same every month. Is there some way to alter this code so
that it can also read the one zipped cd and run SQL code on it also?




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

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

Dim DBName As String

DBName = ListBankNames.Value

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=D:" + _
DBName + ".mdb; User Id=admin; Password="

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



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

Next table


End Sub
--
Billy Rogers

Dallas,TX

Currently Using Office 2000