Read zip file on disc

[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

[Tim Williams]

You'll have to unzip the DB first. As far as I know there's no way to query a zipped Access file.

See here for some code for unzipping from VBA:
http://www.rondebruin.nl/windowsxpzip.htm

Tim


"BillyRogers" <[email protected]> wrote in message news:[email protected]...
> 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

[BillyRogers]

We are using WinRar to zip the files and our operating system is windows
2000. Should this still work?
--
Billy Rogers

Dallas,TX

Currently Using Office 2000


"Tim Williams" wrote:

> You'll have to unzip the DB first. As far as I know there's no way to query a zipped Access file.
>
> See here for some code for unzipping from VBA:
> http://www.rondebruin.nl/windowsxpzip.htm
>
> Tim
>
>
> "BillyRogers" <[email protected]> wrote in message news:[email protected]...
> > 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
>
>
>

[Tim Williams]

I think the shell approach may be XP only, but there's other code on Ron's site which deals with non-XP..

--
Tim Williams
Palo Alto, CA


"BillyRogers" <[email protected]> wrote in message news:[email protected]...
> We are using WinRar to zip the files and our operating system is windows
> 2000. Should this still work?
> --
> Billy Rogers
>
> Dallas,TX
>
> Currently Using Office 2000
>
>
> "Tim Williams" wrote:
>
> > You'll have to unzip the DB first. As far as I know there's no way to query a zipped Access file.
> >
> > See here for some code for unzipping from VBA:
> > http://www.rondebruin.nl/windowsxpzip.htm
> >
> > Tim
> >
> >
> > "BillyRogers" <[email protected]> wrote in message
news:[email protected]...
> > > 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
> >
> >
> >