Solved: count number of record of an MDB

[sal21]

... is pososble to count a number of record (or a line, all line are filled) in E:\PRPOVA.MDB table TOTALE and insert the result in a cell of a sheet for example in A2?

[WillR]

See the solution posted here

http://www.ozgrid.com/forum/showthread.php?t=25917

If you are going to cross-post without posting the links & let me waste my time then I am not going to help you again.

Here is the code that works

[vba]Option Explicit

Sub GetRecordCount()
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stDB As String, stSQL As String
Dim stConn As String
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim i As Integer

'Instantiate the ADO-objects.
Set cnt = New ADODB.Connection
Set rst = New ADODB.Recordset

Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets(1)

'Path to the database.
stDB = "C:\Will\Prova.mdb"

'Create the connectionstring.
stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & stDB & ";"

'The raw SQL-statement to be executed.
stSQL = "SELECT DATA_CONT FROM TOTALE"

With cnt
.Open (stConn) 'Open the connection.
.CursorLocation = adUseClient 'Necessary to disconnect the recordset.
End With

With rst
.Open stSQL, cnt 'Create the recordset.
Set .ActiveConnection = Nothing 'Disconnect the recordset.
i = rst.RecordCount
End With

With wsSheet
.Cells(2, 1).Value = i
End With
'clean up

rst.Close
Set rst = Nothing
cnt.Close
Set cnt = Nothing
Set wbBook = Nothing
Set wsSheet = Nothing

End Sub [/vba]

That's alot of pizzas you owe me.

[sal21]

OK! But the cell not is filed with a result value! (boh!)

[sal21]

With wsSheet
HA!!!
I have sostituite with:

With ActiveSheet

and it work, why?...

[WillR]

Well i tested it on your database

I got 1351 in cell A2

[WillR]

With wsSheet
HA!!!
I have sostituite with:

With ActiveSheet

and it work, why?...

It probably did work, but the value is on Worksheets(1), whichever that one is in your workbook. Have you looked?

[XL-Dennis]

Hi CM Will,

sal21 is a wellknown power user of all free online Excel-resources and keep many people busy on forums like MrExcel, OzGrid, VBAX, Tek-tips, Programming Talks, Access-programming, EE, Office Experts, CodingForums, DBForums, UtterAccess, MS Newgroups and propably additional +100 forums.

Make a search via Google and You will see that sal21 is the #1 X-poster of all kind.

My guess is that the guy is too busy with asking questions all over the world wide web then learning from the answers he got and the whorse part is that he only offer the same ***** virtual pizza.

I believe I can leave the decision to You how to deal with the pizza-man

[sal21]

Hi CM Will,

sal21 is a wellknown power user of all free online Excel-resources and keep many people busy on forums like MrExcel, OzGrid, VBAX, Tek-tips, Programming Talks, Access-programming, EE, Office Experts, CodingForums, DBForums, UtterAccess, MS Newgroups and propably additional +100 forums.

Make a search via Google and You will see that sal21 is the #1 X-poster of all kind.

My guess is that the guy is too busy with asking questions all over the world wide web then learning from the answers he got and the whorse part is that he only offer the same ***** virtual pizza.

I believe I can leave the decision to You how to deal with the pizza-man

Hi Dennis, you are a good spy!
Good lucky for the future mission...
when you have begin, you did not make like me?
or you been born direct a wizard?
For the future not to interest to you more than me.
Tks.!
Sorry for my english but i hope to undertstand me.
By...

[Brandtrock]

sal21

Nobody begrudges your quest for knowledge. I have learned much from OzGrid and VBAX in particular. I frequently visit other forums that I don't post on. I simply absorb knowledge from them.

Will and Dennis are both very giving of their time and efforts at both of the above mentioned forums. I volunteer solutions when I am able but it appears to me that you are not really interested in the fact that you have gotten literally thousands of dollars (or pounds or lira or euros or whatever currency you may choose) of advice FOR ABSOLUTELY NOTHING!!!

Please respect the time and effort of the volunteers here, at OzGrid, and any of the multitude of other forums you go to by

1. Not asking the same question in several forums without waiting a reasonable time to get a reply
2. Explicitly thanking the person(s) who help you when it takes several posts to get a solution
3. Indicating that you have cross posted so efforts to make a solution are not duplicated unnecessarily

I join Will in electing not to assist you any further as long as you abuse the free help offered here and elsewhere.

[CBrine]

I stopped responding to Sal21's messages, after I spent 2 hours coming up a with a VBA solution, regarding coloring a vacation calendar on an unsolved post here at vbx, only to learn that he had posted a second thread regarding the same issue, with a couple of small changes and was working away with some else as well. I will not respond to people that waste my time.

Sorry Sal, you've lost my help.

PS- Doesn't this look familiar. Same topic over at Mr Excel.
http://www.mrexcel.com/board2/viewtopic.php?t=113440

[sal21]

I stopped responding to Sal21's messages, after I spent 2 hours coming up a with a VBA solution, regarding coloring a vacation calendar on an unsolved post here at vbx, only to learn that he had posted a second thread regarding the same issue, with a couple of small changes and was working away with some else as well. I will not respond to people that waste my time.

Sorry Sal, you've lost my help.

PS- Doesn't this look familiar. Same topic over at Mr Excel.
http://www.mrexcel.com/board2/viewtopic.php?t=113440

For problem in my family in the Office in ecc...in these days they are under stress
like all gentleman you want to accept my excuses...
For Dennis, Willr, Cbrine, Brandtrock
and all...
And if you want for future help me OK, if not i have learned the lesson
Sincerly Sal

[SJ McAbney]

Aw! Sal's not as active over on Access World - posts are all from the Excel forum.

[Zack Barresse]

Okay, so Sal now knows to leave a link to any and all cross-board posting made on duplicate thread topics. If there is no further comment - I will wait a reasonable amount of time - I shall lock this thread and call it Solved.

To all who have replied thus far (here and other forums): Thank you for your efforts, they are truly appreciated!!

Sal: You live you learn. No worries, let's just all learn from this.

[Ken Puls]

A quick comment:

Posted 11-05-2004 07:22 AM (PST)

i have learned the lesson

This link, (at Experts Exchange) as caught by Dennis, was posted at 11/10/2004 06:36 PST

And this post at this site (a letter for letter copy of the above link) was created at 11-10-2004, 07:18 AM PST. In that post, it was Dennis who provided the link, not sal21.

I question if the lesson has truly been learned...

[WillR]

I am not bothering to help Sal any longer. There's only so much you can take.

The guy takes the **** big time. The stark reality is that what he really needs is an Acces/Excel consultant for the family firm and they need to pay $$. Rather than do that, he posts any problem he can't be bothered to or is not prepared to try to solve on as many free forums as he can & takes the first/best solution.

The guy does not appear to want to learn anything, just get a free solution, as pretty much every question is the same sort of issue.

I wash my hands of him. Sorry if this upsets or surprises anyone. I am usually a pretty friendly helpful guy i know.... but I recently set up a private forum on my website just so I could help him with a couple of specific things he asked me about, by discussing some Access/Excel techniques and solutions extensively, but he still took the **** & posted his questions all over the place.