VBA database verbinding

De eerste stap is om de procedure te voorzien van de initiële code. Dit gebeurd met behulp van de bibliotheek ADODB waarnaar apart verwezen moet worden: VBA Editor | Extra | Verwijzingen | Microsoft ActiveX Data Objects 2.8 Library.

Option Explicit
 
Private Sub leesDatabase()

    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
      
    With cn
        .ConnectionString = ""
        .Open
    End With
        
    Set rs = Nothing
    Set cn = Nothing
     
End Sub

Het bovenstaande voorbeeld is gebaseerd op Excel.

Om verbinding te maken met een Access database kan de optie [Database Connection String Assistent] gebruikt worden. Hiermee kan de connectionstring worden gegenereerd. Daarna kunnen de methoden van de recordset worden toegevoegd.

Option Explicit
 
Private Sub leesDatabase()

    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
      
    With cn
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.16.0;Data Source=Databank.accdb;Persist Security Info=False"
        .Open
    End With
    
    With rs
        .CursorLocation = adUseClient
        .Open "SELECT * FROM contactpersonen", cn
        If .RecordCount = 0 Then Exit Sub
    End With
        
    Set rs = Nothing
    Set cn = Nothing
     
End Sub

De onderstaande wijzigingen zorgen o.a. voor het uitlezen en plaatsen van de voor- en achternamen.

Option Explicit
 
Private Sub leesDatabase()

    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
      
    With cn
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.16.0;Data Source=Databank.accdb;Persist Security Info=False"
        .Open
    End With
    
    With rs
        .CursorLocation = adUseClient
        .Open "SELECT * FROM contactpersonen", cn
        If .RecordCount = 0 Then Exit Sub
        
        .MoveFirst
         
        For r = 1 To .RecordCount
         
            Cells(r, 1) = .Fields("Voornaam").Value
            Cells(r, 2) = .Fields("Achternaam").Value
            
            .MoveNext
         
        Next r
        
    End With
        
    Set rs = Nothing
    Set cn = Nothing
     
End Sub

Om een MS SQL Server database in te lezen kan als test de database Databank aangemaakt worden. Vervolgens zal het onderstaande script uitgevoerd moeten worden om de database inhoud te geven.

CREATE TABLE contactpersonen (
  Nummer int NOT NULL,
  Voornaam varchar(255) NOT NULL,
  Achternaam varchar(255) NOT NULL
) 

INSERT INTO contactpersonen (Nummer, Voornaam, Achternaam) VALUES
(1,'Reinate', 'van Putten'),
(2,'Alle', 'Foolen'),
(3,'Sarah', 'Rozeboom'),
(4,'Shannah', 'van Genderen'),
(5,'Gerrit', 'Rodenburg'),
(6,'Rosalin', 'Kamperman'),
(7,'Johan', 'Jagers'),
(8,'Rahul', 'Dikker'),
(9,'Bart-Jan', 'Rozenberg'),
(10,'Ferenc', 'Schreur');

Tot slot hoeft alleen maar de connectionstring aangepast te worden. Dit kan ook weer makkelijk via de [Database Connection String Assistent].

    With cn
        .ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Databank;Data Source=NAAMPC\SQLEXPRESS"
        .Open
    End With

Voor dit artikel zijn de pakketen MS SQL Server 2017 Express en SSMS gebruikt.

Een werkblad van Excel kan ook gezien worden als database table. Wederom kan ook hier weer via de [Database Connection String Assistent] de betreffende connectionstring worden gegenereerd. Merk ook het verschil op in de adressering van de table (werkblad) in de SQL.

 With cn
        .ConnectionString = "Provider=MSDASQL.1;DBQ=C:\databank.xlsx;DefaultDir=C:\;Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;ReadOnly=0;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"
        .Open
    End With
 
    With rs
        .CursorLocation = adUseClient
        .Open "SELECT * FROM [contactpersonen$]", cn
        If .RecordCount = 0 Then Exit Sub
 
        .MoveFirst
         
        For r = 1 To .RecordCount
         
            Cells(r, 1) = .Fields("Voornaam").Value
            Cells(r, 2) = .Fields("Achternaam").Value
            
            .MoveNext
         
        Next r
     
    End With

Om gebruik te kunnen maken van een MariaDB op een externe server dienen de credentials bekend te zijn. De credentials bestaan o.a uit een serveradres, poortnummer, gebruikersnaam en wachtwoord. Voor dit artikel is de database Databank reeds aangemaakt met Databank ook als gebruikersnaam. In het verlengde hiervan is het onderstaande script uitgevoerd de database inhoud te geven.

CREATE TABLE `contactpersonen` (
  `Nummer` int(11) NOT NULL,
  `Voornaam` varchar(255) NOT NULL,
  `Achternaam` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `contactpersonen` (`Nummer`, `Voornaam`, `Achternaam`) VALUES
(1,'Reinate', 'van Putten'),
(2,'Alle', 'Foolen'),
(3,'Sarah', 'Rozeboom'),
(4,'Shannah', 'van Genderen'),
(5,'Gerrit', 'Rodenburg'),
(6,'Rosalin', 'Kamperman'),
(7,'Johan', 'Jagers'),
(8,'Rahul', 'Dikker'),
(9,'Bart-Jan', 'Rozenberg'),
(10,'Ferenc', 'Schreur');

Ook hier weer dient de connectionstring aangepast te worden. Dit kan wederom ook weer makkelijk via de [Database Connection String Assistent]. Echter zal eerst wel de MariaDB connector/ODBC geïnstalleerd moeten worden.

    With cn
        .ConnectionString = "Provider=MSDASQL.1;DESCRIPTION=MyMariaDb;DRIVER={MariaDB ODBC 3.1 Driver};TCPIP=1;SERVER=SERVERADRES;UID=Databank;PWD={wachtwoord};DATABASE=Databank;PORT=3307"
        .Open
    End With