VBA
Arbetar med ADO

Anmärkningar

Ansluta till en datakälla

Det första steget i åtkomst till en datakälla via ADO är att skapa ett ADO- Connection . Detta görs vanligtvis med en anslutningssträng för att specificera datakällparametrarna, även om det också är möjligt att öppna en DSN-anslutning genom att överföra DSN, användar-ID och lösenord till metoden .Open .

Observera att en DSN inte krävs för att ansluta till en datakälla via ADO - alla datakällor som har en ODBC-leverantör kan anslutas till med lämplig anslutningssträng. Medan specifika anslutningssträngar för olika leverantörer ligger utanför detta ämnes räckvidd, är ConnectionStrings.com en utmärkt referens för att hitta den lämpliga strängen för din leverantör.

Const SomeDSN As String = "DSN=SomeDSN;Uid=UserName;Pwd=MyPassword;"

Public Sub Example()
    Dim database As ADODB.Connection
    Set database = OpenDatabaseConnection(SomeDSN)
    If Not database Is Nothing Then
        '... Do work.
        database.Close          'Make sure to close all database connections.
    End If
End Sub

Public Function OpenDatabaseConnection(ConnString As String) As ADODB.Connection
    On Error GoTo Handler
    Dim database As ADODB.Connection
    Set database = New ADODB.Connection
    
    With database
        .ConnectionString = ConnString
        .ConnectionTimeout = 10             'Value is given in seconds.
        .Open
    End With
     
    OpenDatabaseConnection = database
     
    Exit Function
Handler:
    Debug.Print "Database connection failed. Check your connection string."
End Function

Observera att databaslösenordet ingår i anslutningssträngen i exemplet ovan endast för tydlighets skull. Bästa praxis innebär att man inte lagrar databaslösenord i kod. Detta kan åstadkommas genom att ta lösenordet via användarinmatning eller använda Windows-autentisering.

Hämtar poster med en fråga

Frågor kan utföras på två sätt, som båda returnerar ett ADO Recordset objekt som är en samling av returnerade rader. Observera att båda exemplen nedan använder OpenDatabaseConnection funktionen från att göra en anslutning till ett OpenDatabaseConnection . Kom ihåg att syntaxen för SQL som skickas till datakällan är leverantörspecifik.

Den första metoden är att skicka SQL-satset direkt till Connection-objektet och är den enklaste metoden för att utföra enkla frågor:

Public Sub DisplayDistinctItems()
    On Error GoTo Handler
    Dim database As ADODB.Connection
    Set database = OpenDatabaseConnection(SomeDSN)
    
    If Not database Is Nothing Then
        Dim records As ADODB.Recordset
        Set records = database.Execute("SELECT DISTINCT Item FROM Table")
        'Loop through the returned Recordset.
        Do While Not records.EOF      'EOF is false when there are more records.
            'Individual fields are indexed either by name or 0 based ordinal.
            'Note that this is using the default .Fields member of the Recordset.
            Debug.Print records("Item")
            'Move to the next record.
            records.MoveNext
        Loop
    End If
CleanExit:
    If Not records Is Nothing Then records.Close
    If Not database Is Nothing And database.State = adStateOpen Then
        database.Close
    End If
    Exit Sub
Handler:
    Debug.Print "Error " & Err.Number & ": " & Err.Description
    Resume CleanExit
End Sub

Den andra metoden är att skapa ett ADO- Command för frågan du vill köra. Detta kräver lite mer kod, men är nödvändig för att använda parametriserade frågor:

Public Sub DisplayDistinctItems()
    On Error GoTo Handler
    Dim database As ADODB.Connection
    Set database = OpenDatabaseConnection(SomeDSN)
    
    If Not database Is Nothing Then
        Dim query As ADODB.Command
        Set query = New ADODB.Command
        'Build the command to pass to the data source.
        With query
            .ActiveConnection = database
            .CommandText = "SELECT DISTINCT Item FROM Table"
            .CommandType = adCmdText
        End With
        Dim records As ADODB.Recordset
        'Execute the command to retrieve the recordset.
        Set records = query.Execute()

        Do While Not records.EOF
            Debug.Print records("Item")
            records.MoveNext
        Loop
    End If
CleanExit:
    If Not records Is Nothing Then records.Close
    If Not database Is Nothing And database.State = adStateOpen Then
        database.Close
    End If
    Exit Sub
Handler:
    Debug.Print "Error " & Err.Number & ": " & Err.Description
    Resume CleanExit
End Sub

Observera att kommandon som skickas till datakällan är sårbara för SQL-injektion , antingen avsiktligt eller oavsiktligt. I allmänhet bör frågor inte skapas genom att sammanfoga användarinmatning av något slag. Istället bör de parametreras (se Skapa parametrerade kommandon ).

Utför icke-skalfunktioner

ADO-anslutningar kan användas för att utföra nästan alla databasfunktioner som leverantören stöder via SQL. I det här fallet är det inte alltid nödvändigt att använda Recordset returneras av Execute funktionen, även om det kan vara användbart för att få nyckeltilldelningar efter INSERT-uttalanden med @@ Identity eller liknande SQL-kommandon. Observera att exemplet nedan använder OpenDatabaseConnection funktionen från att göra en anslutning till ett OpenDatabaseConnection .

Public Sub UpdateTheFoos()
    On Error GoTo Handler
    Dim database As ADODB.Connection
    Set database = OpenDatabaseConnection(SomeDSN)
    
    If Not database Is Nothing Then
        Dim update As ADODB.Command
        Set update = New ADODB.Command
        'Build the command to pass to the data source.
        With update
            .ActiveConnection = database
            .CommandText = "UPDATE Table SET Foo = 42 WHERE Bar IS NULL"
            .CommandType = adCmdText
            .Execute        'We don't need the return from the DB, so ignore it.
        End With
    End If
CleanExit:
    If Not database Is Nothing And database.State = adStateOpen Then
        database.Close
    End If
    Exit Sub
Handler:
    Debug.Print "Error " & Err.Number & ": " & Err.Description
    Resume CleanExit
End Sub

Observera att kommandon som skickas till datakällan är sårbara för SQL-injektion , antingen avsiktligt eller oavsiktligt. I allmänhet ska SQL-uttalanden inte skapas genom att sammanfoga användarinmatning av något slag. Istället bör de parametreras (se Skapa parametrerade kommandon ).

Skapa parametrerade kommandon

Varje gång SQL som utförs via en ADO-anslutning måste innehålla användarinmatning, anses det vara bästa praxis att parametrera den för att minimera risken för SQL-injektion. Denna metod är också mer läsbar än långa sammanlänkningar och underlättar mer robust och underhållbar kod (dvs genom att använda en funktion som returnerar en rad Parameter ).

I standard ODBC-syntax ges parametrar ? "platshållare" i frågeteksten och sedan läggs parametrar till Command i samma ordning som de visas i frågan.

Observera att exemplet nedan använder OpenDatabaseConnection funktionen från att skapa en anslutning till en datakälla för korthet.

Public Sub UpdateTheFoos()
    On Error GoTo Handler
    Dim database As ADODB.Connection
    Set database = OpenDatabaseConnection(SomeDSN)
    
    If Not database Is Nothing Then
        Dim update As ADODB.Command
        Set update = New ADODB.Command
        'Build the command to pass to the data source.
        With update
            .ActiveConnection = database
            .CommandText = "UPDATE Table SET Foo = ? WHERE Bar = ?"
            .CommandType = adCmdText
            
            'Create the parameters.
            Dim fooValue As ADODB.Parameter
            Set fooValue = .CreateParameter("FooValue", adNumeric, adParamInput)
            fooValue.Value = 42
            
            Dim condition As ADODB.Parameter
            Set condition = .CreateParameter("Condition", adBSTR, adParamInput)
            condition.Value = "Bar"
            
            'Add the parameters to the Command
            .Parameters.Append fooValue
            .Parameters.Append condition
            .Execute
        End With
    End If
CleanExit:
    If Not database Is Nothing And database.State = adStateOpen Then
        database.Close
    End If
    Exit Sub
Handler:
    Debug.Print "Error " & Err.Number & ": " & Err.Description
    Resume CleanExit
End Sub

Obs: Exemplet ovan visar ett parametriserat UPDATE-uttalande, men alla SQL-satser kan ges parametrar.