Simplisitic Access Database Retrieval Script

'Make Sure you Reference the Following in your Project
'Microsoft ActiveX Data Objects 2.8 Library
'Microsoft DAO 3.6 Object Library


Public Sub GetCn(ByRef dbcon As ADODB.Connection, ByRef dbrs As ADODB.Recordset, _
                 sqlstr As String, dbfile As String, usernm As String, pword As String, toggle As Integer)


    'TO INCREASE SPEED OF REPETITIVE QUERIES, CHANGE TOGGLE TO 1 AFTER 1ST QUERY SO YOU ARE NOT REOPENING CONNECTION EACH TIME
    If toggle = 0 Then
        Set dbcon = New ADODB.Connection
        dbcon.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbfile & ";", _
                   usernm, pword
    End If


    With dbcon
        .CursorLocation = adUseClient    'disconnect recordset
        Set dbrs = .Execute(sqlstr)
    End With


End Sub




Sub GetAccessData()
'USE-GET ACCESS DATA


'################################### DEC VARS ##########################
'GEN VARS
    Dim filenm As String    'DATABASE PATH AND NAME
    Dim ConnectionErr As Label    'CONNECTION ERROR LABEL
    Dim Bypass As Label     'LABEL TO JUMP ERROR BLOCK
    Dim TryAgain As Label  'LABEL TO LOOP BACK AND TRY AGAIN


'CONNECTION VARS---------------------------------------------------------
    Dim adoconn As ADODB.Connection
    Dim adors As ADODB.Recordset
    Dim sql As String
    '################################### END DEC VARS ######################




    '################################### SET VALS ##########################
    filenm = "C:\MyDatabase.MDB"    'SET DEFAULT DATABASE
    sql = "Select * from Whatever"    'PUT YOUR SQL HERE
    ErrorCount = 0    'INITIALIZE ERROR COUNTER
    '################################### END SET VALS ######################




    '################################### GET THE DATA ######################
    On Error GoTo ConnectionErr    'IN CASE PROBLEM CONNECTING GOTO ERROR BLOCK AND INCREMENT ERRORCOUNT


TryAgain:
    Call GetCn(adoconn, adors, sql, filenm, "", "", 0)  'REPLACE COMMAS WITH USERNAME AND PASSWORD IF DATABASE USES IT
    ErrorCount = 0    'RESET ERROR COUNT VAR BECAUSE DATA RETRIEVAL WAS SUCCESSFULL


    adors.MoveFirst
    If Not adors.BOF Then
        Cells(1, 1).Value = adors(0)    'BY DEFAULT PUTTING DATA IN CELL A1- COULD USE VARS HERE FOR CYCLIC DATA RETRIEVAL
    End If


    'CLEAN UP OPERATIONS
    adors.Close
    adoconn.Close
    Set adors = Nothing
    Set adoconn = Nothing
    GoTo Bypass    'JUMP OVER ERROR BLOCK
    '################################## END GET DATA######################




    '############################### ERROR BLOCK ######################
ConnectionErr:
    ErrorCount = ErrorCount + 1


    Set adors = Nothing
    Set adoconn = Nothing


    'THIS IS AN ERROR COUNTER, WILL RETRY MAX TIMES THEN OPERATION TERMINATES
    If ErrorCount >= 200 Then
        MsgBox "Max Error Count Exceeded.  Operation  will terminate", vbInformation, "UserMessage"
        End
    End If
    GoTo TryAgain:    'LOOP BACK AND TRY AGAIN
    '############################### END ERROR BLOCK ##################
Bypass:        'ERROR JUMP LABEL




End Sub