Automate QA record count on CD's

[BillyRogers]

We recieve about 40 cd's each month on a certain date each of which contains
an access database, with data for a different customer on each cd. I have to
QA the cds by placing each cd in my computer, openening 4 specific tables and
checking the number of rows against a paper list i have.

does anyone have any ideas of how I can automate the counting of the rows?
the table names are the same for each cd (the database names are different
and the row counts are not identical.)

thanks
--
Billy Rogers

Dallas,TX

Currently Using Office 2000

 

[Jim F]

Personnaly I would do this in excel. I would create a "template" that has a
button control on it. In the on click event of the BTN control I would run
the code below (of Course you will need to modify it slightly to suit your
needs...i.e. change the connection string and the table names).

The code loads the table names into an array and then prompts for the DB
name. It then loops throught each table, getting the count for each, and
then places the results on the next availble row in the excel sheet. Once
you have gone through all DBs you can save the sheet and information for
future reference (A paper trail).

CODE:

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

myTables = Array("Table1", "Table2", "Table3", "Table4")

Dim DBName As String

DBName = InputBox("Data Base Name?")

For Each table In myTables

SQlcmd = "Select Count(*) as [Count] From " & table

rs.Open Source:=SQlcmd,
ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\" +
DBName + ".mdb; User Id=admin; Password="

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

ActiveCell.FormulaR1C1 = DBName + " " + table + " " +
rs.Fields("Count").Value

Next table
End Sub

 

[Klatuu]

We recieve about 40 cd's each month on a certain date each of which contains
an access database, with data for a different customer on each cd. I have to
QA the cds by placing each cd in my computer, openening 4 specific tables and
checking the number of rows against a paper list i have.

does anyone have any ideas of how I can automate the counting of the rows?
the table names are the same for each cd (the database names are different
and the row counts are not identical.)

thanks
--
Billy Rogers

Dallas,TX

Currently Using Office 2000

 

[BillyRogers]

Thanks Jim,

I can't quite get it to work.

When i run the macro it stops here and highlights the word open and an error
message says "compile error: method or data member not found". The input box
didn't pop up.



Private Sub CommandButton1_Click()

Dim rs As Recordset
Dim SQlcmd As String
Dim myTables As Variant
Dim table As Variant

myTables = Array("Control Data", "FEE History", "financial history",
"financial history 2")

Dim DBName As String

DBName = InputBox("Data Base Name?")

For Each table In myTables

SQlcmd = "Select Count(*) as [Count] From " & table

rs.Open Source:=SQlcmd, _ ' **this is where it highlights 'Open' &stops
w/err msg
ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:\" + _
DBName + ".mdb; User Id=admin; Password="

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

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

Next table
End Sub




rs.Open Source:=SQlcmd,
ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:\" + _
DBName +".mdb; User Id=admin; Password="
--
Billy Rogers

Dallas,TX

Currently Using Office 2000

Personnaly I would do this in excel. I would create a "template" that has a
button control on it. In the on click event of the BTN control I would run
the code below (of Course you will need to modify it slightly to suit your
needs...i.e. change the connection string and the table names).

The code loads the table names into an array and then prompts for the DB
name. It then loops throught each table, getting the count for each, and
then places the results on the next availble row in the excel sheet. Once
you have gone through all DBs you can save the sheet and information for
future reference (A paper trail).

CODE:

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

myTables = Array("Table1", "Table2", "Table3", "Table4")

Dim DBName As String

DBName = InputBox("Data Base Name?")

For Each table In myTables

SQlcmd = "Select Count(*) as [Count] From " & table

rs.Open Source:=SQlcmd,
ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\" +
DBName + ".mdb; User Id=admin; Password="

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

ActiveCell.FormulaR1C1 = DBName + " " + table + " " +
rs.Fields("Count").Value

Next table
End Sub

We recieve about 40 cd's each month on a certain date each of which contains
an access database, with data for a different customer on each cd. I have to
QA the cds by placing each cd in my computer, openening 4 specific tables and
checking the number of rows against a paper list i have.

does anyone have any ideas of how I can automate the counting of the rows?
the table names are the same for each cd (the database names are different
and the row counts are not identical.)

thanks
--
Billy Rogers

Dallas,TX

Currently Using Office 2000

 

[Jim F]

You may have to add a refference to Microsoft Data Access Object 2.8. (MDAC).

Thanks Jim,

I can't quite get it to work.

When i run the macro it stops here and highlights the word open and an error
message says "compile error: method or data member not found". The input box
didn't pop up.



Private Sub CommandButton1_Click()

Dim rs As Recordset
Dim SQlcmd As String
Dim myTables As Variant
Dim table As Variant

myTables = Array("Control Data", "FEE History", "financial history",
"financial history 2")

Dim DBName As String

DBName = InputBox("Data Base Name?")

For Each table In myTables

SQlcmd = "Select Count(*) as [Count] From " & table

rs.Open Source:=SQlcmd, _ ' **this is where it highlights 'Open' &stops
w/err msg
ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:\" + _
DBName + ".mdb; User Id=admin; Password="

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

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

Next table
End Sub




rs.Open Source:=SQlcmd,
ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:\" + _
DBName +".mdb; User Id=admin; Password="
--
Billy Rogers

Dallas,TX

Currently Using Office 2000

Personnaly I would do this in excel. I would create a "template" that has a
button control on it. In the on click event of the BTN control I would run
the code below (of Course you will need to modify it slightly to suit your
needs...i.e. change the connection string and the table names).

The code loads the table names into an array and then prompts for the DB
name. It then loops throught each table, getting the count for each, and
then places the results on the next availble row in the excel sheet. Once
you have gone through all DBs you can save the sheet and information for
future reference (A paper trail).

CODE:

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

myTables = Array("Table1", "Table2", "Table3", "Table4")

Dim DBName As String

DBName = InputBox("Data Base Name?")

For Each table In myTables

SQlcmd = "Select Count(*) as [Count] From " & table

rs.Open Source:=SQlcmd,
ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\" +
DBName + ".mdb; User Id=admin; Password="

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

ActiveCell.FormulaR1C1 = DBName + " " + table + " " +
rs.Fields("Count").Value

Next table
End Sub

We recieve about 40 cd's each month on a certain date each of which contains
an access database, with data for a different customer on each cd. I have to
QA the cds by placing each cd in my computer, openening 4 specific tables and
checking the number of rows against a paper list i have.

does anyone have any ideas of how I can automate the counting of the rows?
the table names are the same for each cd (the database names are different
and the row counts are not identical.)

thanks
--
Billy Rogers

Dallas,TX

Currently Using Office 2000

 

[Jim F]

Try This (notice I added a line setting the rs to new):

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

myTables = Array("Table1", "Table2", "Table3", "Table4")

Dim DBName As String

DBName = InputBox("Data Base Name?")

For Each table In myTables

SQlcmd = "Select Count(*) as [Count] From " & table

rs.Open Source:=SQlcmd,
ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\" +
DBName + ".mdb; User Id=admin; Password="

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

ActiveCell.FormulaR1C1 = DBName + " " + table + " " +
rs.Fields("Count").Value

Next table
End Sub

Thanks Jim,

I can't quite get it to work.

When i run the macro it stops here and highlights the word open and an error
message says "compile error: method or data member not found". The input box
didn't pop up.



Private Sub CommandButton1_Click()

Dim rs As Recordset
Dim SQlcmd As String
Dim myTables As Variant
Dim table As Variant

myTables = Array("Control Data", "FEE History", "financial history",
"financial history 2")

Dim DBName As String

DBName = InputBox("Data Base Name?")

For Each table In myTables

SQlcmd = "Select Count(*) as [Count] From " & table

rs.Open Source:=SQlcmd, _ ' **this is where it highlights 'Open' &stops
w/err msg
ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:\" + _
DBName + ".mdb; User Id=admin; Password="

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

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

Next table
End Sub




rs.Open Source:=SQlcmd,
ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:\" + _
DBName +".mdb; User Id=admin; Password="
--
Billy Rogers

Dallas,TX

Currently Using Office 2000

Personnaly I would do this in excel. I would create a "template" that has a
button control on it. In the on click event of the BTN control I would run
the code below (of Course you will need to modify it slightly to suit your
needs...i.e. change the connection string and the table names).

The code loads the table names into an array and then prompts for the DB
name. It then loops throught each table, getting the count for each, and
then places the results on the next availble row in the excel sheet. Once
you have gone through all DBs you can save the sheet and information for
future reference (A paper trail).

CODE:

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

myTables = Array("Table1", "Table2", "Table3", "Table4")

Dim DBName As String

DBName = InputBox("Data Base Name?")

For Each table In myTables

SQlcmd = "Select Count(*) as [Count] From " & table

rs.Open Source:=SQlcmd,
ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\" +
DBName + ".mdb; User Id=admin; Password="

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

ActiveCell.FormulaR1C1 = DBName + " " + table + " " +
rs.Fields("Count").Value

Next table
End Sub

We recieve about 40 cd's each month on a certain date each of which contains
an access database, with data for a different customer on each cd. I have to
QA the cds by placing each cd in my computer, openening 4 specific tables and
checking the number of rows against a paper list i have.

does anyone have any ideas of how I can automate the counting of the rows?
the table names are the same for each cd (the database names are different
and the row counts are not identical.)

thanks
--
Billy Rogers

Dallas,TX

Currently Using Office 2000

 

[Jim F]

SQlcmd = "Select Count(*) as [Count] From " & table

Set rs = New ADODB.Recordset

Thanks Jim,

I can't quite get it to work.

When i run the macro it stops here and highlights the word open and an error
message says "compile error: method or data member not found". The input box
didn't pop up.



Private Sub CommandButton1_Click()

Dim rs As Recordset
Dim SQlcmd As String
Dim myTables As Variant
Dim table As Variant

myTables = Array("Control Data", "FEE History", "financial history",
"financial history 2")

Dim DBName As String

DBName = InputBox("Data Base Name?")

For Each table In myTables

SQlcmd = "Select Count(*) as [Count] From " & table

rs.Open Source:=SQlcmd, _ ' **this is where it highlights 'Open' &stops
w/err msg
ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:\" + _
DBName + ".mdb; User Id=admin; Password="

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

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

Next table
End Sub




rs.Open Source:=SQlcmd,
ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:\" + _
DBName +".mdb; User Id=admin; Password="
--
Billy Rogers

Dallas,TX

Currently Using Office 2000

Personnaly I would do this in excel. I would create a "template" that has a
button control on it. In the on click event of the BTN control I would run
the code below (of Course you will need to modify it slightly to suit your
needs...i.e. change the connection string and the table names).

The code loads the table names into an array and then prompts for the DB
name. It then loops throught each table, getting the count for each, and
then places the results on the next availble row in the excel sheet. Once
you have gone through all DBs you can save the sheet and information for
future reference (A paper trail).

CODE:

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

myTables = Array("Table1", "Table2", "Table3", "Table4")

Dim DBName As String

DBName = InputBox("Data Base Name?")

For Each table In myTables

SQlcmd = "Select Count(*) as [Count] From " & table

rs.Open Source:=SQlcmd,
ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\" +
DBName + ".mdb; User Id=admin; Password="

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

ActiveCell.FormulaR1C1 = DBName + " " + table + " " +
rs.Fields("Count").Value

Next table
End Sub

We recieve about 40 cd's each month on a certain date each of which contains
an access database, with data for a different customer on each cd. I have to
QA the cds by placing each cd in my computer, openening 4 specific tables and
checking the number of rows against a paper list i have.

does anyone have any ideas of how I can automate the counting of the rows?
the table names are the same for each cd (the database names are different
and the row counts are not identical.)

thanks
--
Billy Rogers

Dallas,TX

Currently Using Office 2000

 

[Jim F]

sAlso...just in case you get a mix match error....change the contentated line
to use ampersands (&) intstead of plus (=) symbols.

Thanks Jim,

I can't quite get it to work.

When i run the macro it stops here and highlights the word open and an error
message says "compile error: method or data member not found". The input box
didn't pop up.



Private Sub CommandButton1_Click()

Dim rs As Recordset
Dim SQlcmd As String
Dim myTables As Variant
Dim table As Variant

myTables = Array("Control Data", "FEE History", "financial history",
"financial history 2")

Dim DBName As String

DBName = InputBox("Data Base Name?")

For Each table In myTables

SQlcmd = "Select Count(*) as [Count] From " & table

rs.Open Source:=SQlcmd, _ ' **this is where it highlights 'Open' &stops
w/err msg
ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:\" + _
DBName + ".mdb; User Id=admin; Password="

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

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

Next table
End Sub




rs.Open Source:=SQlcmd,
ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:\" + _
DBName +".mdb; User Id=admin; Password="
--
Billy Rogers

Dallas,TX

Currently Using Office 2000

Personnaly I would do this in excel. I would create a "template" that has a
button control on it. In the on click event of the BTN control I would run
the code below (of Course you will need to modify it slightly to suit your
needs...i.e. change the connection string and the table names).

The code loads the table names into an array and then prompts for the DB
name. It then loops throught each table, getting the count for each, and
then places the results on the next availble row in the excel sheet. Once
you have gone through all DBs you can save the sheet and information for
future reference (A paper trail).

CODE:

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

myTables = Array("Table1", "Table2", "Table3", "Table4")

Dim DBName As String

DBName = InputBox("Data Base Name?")

For Each table In myTables

SQlcmd = "Select Count(*) as [Count] From " & table

rs.Open Source:=SQlcmd,
ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\" +
DBName + ".mdb; User Id=admin; Password="

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

ActiveCell.FormulaR1C1 = DBName + " " + table + " " +
rs.Fields("Count").Value

Next table
End Sub

We recieve about 40 cd's each month on a certain date each of which contains
an access database, with data for a different customer on each cd. I have to
QA the cds by placing each cd in my computer, openening 4 specific tables and
checking the number of rows against a paper list i have.

does anyone have any ideas of how I can automate the counting of the rows?
the table names are the same for each cd (the database names are different
and the row counts are not identical.)

thanks
--
Billy Rogers

Dallas,TX

Currently Using Office 2000

 

[BillyRogers]

Jim Thanks a lot. I'm getting closer. It stops on the ActiveCell line. I
commented it out and put in a message box so I know it's getting the value.

MsgBox rs.Fields("Count").Value

'ActiveCell.FormulaR1C1 = DBName + " " + table + " " + _
'rs.Fields("Count").Value

--
Billy Rogers

Dallas,TX

Currently Using Office 2000

SQlcmd = "Select Count(*) as [Count] From " & table

Set rs = New ADODB.Recordset

Thanks Jim,

I can't quite get it to work.

When i run the macro it stops here and highlights the word open and an error
message says "compile error: method or data member not found". The input box
didn't pop up.



Private Sub CommandButton1_Click()

Dim rs As Recordset
Dim SQlcmd As String
Dim myTables As Variant
Dim table As Variant

myTables = Array("Control Data", "FEE History", "financial history",
"financial history 2")

Dim DBName As String

DBName = InputBox("Data Base Name?")

For Each table In myTables

SQlcmd = "Select Count(*) as [Count] From " & table

rs.Open Source:=SQlcmd, _ ' **this is where it highlights 'Open' &stops
w/err msg
ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:\" + _
DBName + ".mdb; User Id=admin; Password="

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

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

Next table
End Sub




rs.Open Source:=SQlcmd,
ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:\" + _
DBName +".mdb; User Id=admin; Password="
--
Billy Rogers

Dallas,TX

Currently Using Office 2000

Personnaly I would do this in excel. I would create a "template" that has a
button control on it. In the on click event of the BTN control I would run
the code below (of Course you will need to modify it slightly to suit your
needs...i.e. change the connection string and the table names).

The code loads the table names into an array and then prompts for the DB
name. It then loops throught each table, getting the count for each, and
then places the results on the next availble row in the excel sheet. Once
you have gone through all DBs you can save the sheet and information for
future reference (A paper trail).

CODE:

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

myTables = Array("Table1", "Table2", "Table3", "Table4")

Dim DBName As String

DBName = InputBox("Data Base Name?")

For Each table In myTables

SQlcmd = "Select Count(*) as [Count] From " & table

rs.Open Source:=SQlcmd,
ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\" +
DBName + ".mdb; User Id=admin; Password="

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

ActiveCell.FormulaR1C1 = DBName + " " + table + " " +
rs.Fields("Count").Value

Next table
End Sub


:

We recieve about 40 cd's each month on a certain date each of which contains
an access database, with data for a different customer on each cd. I have to
QA the cds by placing each cd in my computer, openening 4 specific tables and
checking the number of rows against a paper list i have.

does anyone have any ideas of how I can automate the counting of the rows?
the table names are the same for each cd (the database names are different
and the row counts are not identical.)

thanks
--
Billy Rogers

Dallas,TX

Currently Using Office 2000

 

[BillyRogers]

Jim F,

You are AWESOME!!!!!!!!!!!!!!!!!!!!!!!

I owe you a huge thanks. You have really helped me out alot.

I appreciate it.
--
Billy Rogers

Dallas,TX

Currently Using Office 2000

sAlso...just in case you get a mix match error....change the contentated line
to use ampersands (&) intstead of plus (=) symbols.

Thanks Jim,

I can't quite get it to work.

When i run the macro it stops here and highlights the word open and an error
message says "compile error: method or data member not found". The input box
didn't pop up.



Private Sub CommandButton1_Click()

Dim rs As Recordset
Dim SQlcmd As String
Dim myTables As Variant
Dim table As Variant

myTables = Array("Control Data", "FEE History", "financial history",
"financial history 2")

Dim DBName As String

DBName = InputBox("Data Base Name?")

For Each table In myTables

SQlcmd = "Select Count(*) as [Count] From " & table

rs.Open Source:=SQlcmd, _ ' **this is where it highlights 'Open' &stops
w/err msg
ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:\" + _
DBName + ".mdb; User Id=admin; Password="

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

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

Next table
End Sub




rs.Open Source:=SQlcmd,
ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:\" + _
DBName +".mdb; User Id=admin; Password="
--
Billy Rogers

Dallas,TX

Currently Using Office 2000

Personnaly I would do this in excel. I would create a "template" that has a
button control on it. In the on click event of the BTN control I would run
the code below (of Course you will need to modify it slightly to suit your
needs...i.e. change the connection string and the table names).

The code loads the table names into an array and then prompts for the DB
name. It then loops throught each table, getting the count for each, and
then places the results on the next availble row in the excel sheet. Once
you have gone through all DBs you can save the sheet and information for
future reference (A paper trail).

CODE:

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

myTables = Array("Table1", "Table2", "Table3", "Table4")

Dim DBName As String

DBName = InputBox("Data Base Name?")

For Each table In myTables

SQlcmd = "Select Count(*) as [Count] From " & table

rs.Open Source:=SQlcmd,
ActiveConnection:="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\" +
DBName + ".mdb; User Id=admin; Password="

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

ActiveCell.FormulaR1C1 = DBName + " " + table + " " +
rs.Fields("Count").Value

Next table
End Sub


:

We recieve about 40 cd's each month on a certain date each of which contains
an access database, with data for a different customer on each cd. I have to
QA the cds by placing each cd in my computer, openening 4 specific tables and
checking the number of rows against a paper list i have.

does anyone have any ideas of how I can automate the counting of the rows?
the table names are the same for each cd (the database names are different
and the row counts are not identical.)

thanks
--
Billy Rogers

Dallas,TX

Currently Using Office 2000