PDA

View Full Version : VBA to Query Access



AndrewKent
11-20-2008, 02:03 AM
Hi there,

I seem to be having a little bit of a problem with my code. I am able to return a COUNT of the number of records from tblIndex where Created_By equals Andrew Kent, however I want to modify my code so that it will count anything beginning with A.

Better yet, modified so that it counts anything beginning with the value or a cell. Please see below...


Sub CountRecord()
' =========================================================================== ==================
'
' =========================================================================== ==================
StartTimer ' This code is required for testing purposes only
Dim DBName, DBLocation, FilePath As String
Dim DBConnection As ADODB.Connection

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Set DBConnection = New ADODB.Connection
DBName = Worksheets("Calculation Matrix").Range("CalculationMatrix_DatabaseName").Value
DBLocation = Worksheets("Calculation Matrix").Range("CalculationMatrix_DatabaseLocation").Value
FilePath = DBLocation & DBName
With DBConnection
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open FilePath
End With

Call CountIndex2(DBConnection)
DBConnection.Close
Set DBConnection = Nothing

Worksheets("Index").Activate
Range("A1").Select

EndTimer ' This code is required for testing purposes only
End Sub

Sub CountIndex2(DBConnection As ADODB.Connection)
' =========================================================================== ==================
' =========================================================================== ==================
Dim DBRecordset As ADODB.Recordset
Dim Query As String
Dim MagicNumber As Long

Query = "SELECT tblIndex.Created_By FROM tblIndex WHERE tblIndex.Created_By Like 'Andrew Kent'"
'" & Worksheets("Calculation Matrix").Range("CalculationMatrix_Search").Value
Set DBRecordset = New ADODB.Recordset
DBRecordset.CursorLocation = adUseServer
DBRecordset.Open Source:=Query, ActiveConnection:=DBConnection, CursorType:=adOpenStatic, LockType:=adLockOptimistic, Options:=adCmdText
MagicNumber = DBRecordset.RecordCount

MsgBox (MagicNumber)
DBRecordset.Close
Set DBRecordset = Nothing
End Sub

Cheers,

Andy

Bob Phillips
11-20-2008, 02:19 AM
Query = "SELECT tblIndex.Created_By FROM tblIndex WHERE tblIndex.Created_By Like '" Range("A1").Value & "%'"

AndrewKent
11-20-2008, 02:20 AM
perfect! thanks!