Read zip file on disc
 

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

Read zip file on disc
 

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" wrote in message ...
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

Read zip file on disc
 

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" wrote in message ...
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

Read zip file on disc
 

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" wrote in message ...
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" wrote in message
...
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