B
BillyRogers
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
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