Inhoudsopgave
- Voorbereiding converteren MDB Access-database naar een SQL Server database
- Access exporteren naar SQL Server met Microsoft Office Access
- Data Source Name (DSN) aanmaken
- Access-database exporteren
- SQL Server database verifiëren in SQL Server Management Studio
- Access-database omzetten met Access To MSSQL
- Het Access To MSSQL “gebrek”
- Access-database importeren met SQL Server Management Studio’s SQL Server Import and Export Wizard
- ASP-connectiestring aanpassen
- Conclusie en afsluiting
In deze volgende How-To gaan we de Access-database van een oud ASP-loginscript omzetten naar een Microsoft SQL Server database. Je vraagt je nu misschien af: “Een oude Access-database omzetten? Waarom verwijder je deze niet?”. Dit FAQ-artikel gaat juíst om het converteren van de Access-database ? Vroeger werd Access veel gebruikt en daarom kom je het nu nog steeds veel tegen, met alle nadelen (voor websites) van dien. We geven je drie (3) methodes.
Voorbereiding converteren MDB Access-database naar een SQL Server database
In het artikel gebruik ik de .mdb-Access-database, zoals Vevida die vroeger aanbood als ASP-inlogscript. Als je zelf een eigen Access-database wilt omzetten, zorg er dan voor dat dit tenminste een Access 2003 database is. In dit eenvoudige voorbeeld gaat het om één database met één tabel. Wil je een database met meer tabellen exporteren, dan moet dat vanuit Access per tabel, iets wat nogal arbeidsintensief kan zijn.
Met Access To MSSQL en SQL Server Management Studio kun je wel een database met meerdere tabellen converteren. Die twee programma’s komen later in dit artikel aan bod.
We hebben een converteertool nodig, want we willen onze Access-database upgraden naar een Microsoft SQL Server database. Er zijn drie tools die we hiervoor kunnen gebruiken. De eerste is het programma Microsoft Office Access. De tweede is Access To MSSQL, een gratis shareware-tool van Bullzip.com. Helaas kent dit Access To MSSQL-programma een onhandig gebrek. Maar daarover later meer.
Zorg er ook voor dat je SQL Server Management Studio (SSMS) hebt geïnstalleerd. Dit is het derde programma. Houd ook je inloggegevens van de SQL Server database bij de hand. Je vindt informatie en een downloadlink in ons FAQ-artikel Microsoft SQL Server database onderhouden.
In dit artikel ga ik ervan uit dat je de onderstaande Microsoft Access Driver connectie-string gebruikt, zoals uitgelegd in SQL Server Compact Edition (SqlCe) en Access databasetoegang met ASP:
Set Conn = Server.CreateObject("ADODB.Connection") Conn.Open " DBQ=D:\www\FTP-inlognaam\database\login.mdb;" & _ "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}"Deze connectiestring moet je later nog aanpassen.
Onze eenvoudige gebruikersdatabase:
Zorg er dus voor dat je de Access-database lokaal op je ontwikkelstation hebt staan. Download de database en bijbehorende scripts desnoods nog even via FTP. Je vindt informatie in ons Inloggen op FTP-server FAQ-artikel.
Let op: als je de tabelnaam verandert, moet je die ook overnemen in jouw SQL queries en statements!
Access exporteren naar SQL Server met Microsoft Office Access
Heb je geen Microsoft Office Access? Ga dan naar het kopje over Access To MSSQL.
Vanuit Access moet je verbinding kunnen maken met jouw SQL Server server en database. Hiervoor moet een DSN of Data Source Name worden aangemaakt. Ook al kan dit tijdens het exporteren in Access, ik vind het gemakkelijker dit eerst even te doen. Het houdt deze how-to overzichtelijker.
Data Source Name (DSN) aanmaken
Voor het gemak maak je eerst een DSN of Data Source Name aan in Windows. Ook al kan dit vanuit het Office Access-programma zelf, dit gescheiden uitvoeren maakt het wat overzichtelijker en je hebt het dan maar alvast gedaan.
Via Windows Start, zoek naar ‘Data Sou‘ en start “ODBC Data Sources (32-bit)“.
Volg de stappen en vul steeds de gevraagde gegevens in. De belangrijkste stappen om rekening mee te houden zie je hieronder op deze afbeelding:
Zet éérst het bolletje With SQL Server authentication using a login ID and password entered by the user. Vink dan ook Connect to SQL Server to obtain default settings for the additional configuration options aan en vul jouw SQL Server database inlognaam en wachtwoord in.
Hierna zet je het vinkje weer uit! En klik op Next.
Vink Change the default database to: aan, vul jouw databasenaam en klik weer op Next.
Als de Data Source Name (DSN) succesvol is gemaakt, dan is het eindelijk tijd om in Access bezig te gaan en de database te converteren.
Access-database exporteren
Het exporteren van de database is vrij straightforward.
Selecteer de tabel (tabellen), klik met de rechter muisknop, kies Export en ODBC Database:
In het volgende venster kun je eventueel een nieuwe naam opgeven. Dit is handig als je meerdere Access-databases met meerdere tabellen wilt exporteren naar SQL Server. Je geeft elke database dan een prefix of voorvoegsel. Zo exporteer je eenvoudig meerdere Access-databases naar één MS SQL Server database.
Let op: als je de tabelnaam verandert, moet je die ook overnemen in jouw SQL queries en statements!
Kies de Data Source die we eerder hebben aangemaakt. Je vindt deze onder de tab Machine Data Source.
Vul je SQL Server database login ID en wachtwoord in en klik OK. Hierna volgt direct het laatste scherm met de bevestiging. Eventueel kun je ervoor kiezen de exporteer-stappen op te slaan.
Na deze in totaal vijf stappen ben je klaar en is jouw Access-database geconverteerd vanuit Microsoft Office Access naar SQL Server.
SQL Server database verifiëren in SQL Server Management Studio
Uiteraard wil je graag zeker weten dat de Access-database export goed is verlopen. Start hiervoor SQL Server Management Studio (SSMS) en verbind met jouw SQL Server database. Let hier ook weer op dat je de databasenaam ook opgeeft via Options….
Als je doorklikt van jouw SQL server top-node naar Database, jouw databasenaam (examplenl in dit geval) en dan Tables, dan zie je dbo.TblUsers staan. Klik er met de rechter muisknop op en kies Select Top 1000 Rows.
Met als resultaat drie rijen met gebruikersnamen en wachtwoorden. In dit simpele voorbeeld werd geen gebruik gemaakt van gehashte wachtwoorden.
Access-database omzetten met Access To MSSQL
Als je geen Office Access hebt om een database te exporteren, dan heeft Bullzip.com een gratis (shareware) tool Access To MSSQL. Je vindt de download en extra uitleg hier.
Om aan te tonen dat Access To MSSQL net zo goed werkt, zetten we dezelfde simpele database om.
Start Access To MSSQL en doorloop de Wizard-stappen van Access To MSSQL (zie schermafbeeldingen. Alleen schermafbeeldingen die belangrijk zijn, zijn geplaatst)
Klik op de drie punten (…) om de te exporteren Access-database op te zoeken.
Klik daarna op Next >
Geef het dumpbestand een herkenbare naam. Let ook op de standaardlocatie waar deze wordt opgeslagen. Let op: het bestand wordt een volgende keer zonder waarschuwing overschreven!
Selecteer de tabel (tabellen) die je wilt omzetten naar SQL Server.
Klik op Run Now
Een bevestiging wordt gegeven dat het converteren is geslaagd.
Nu het .sql-dumpbestand is gemaakt, maak je verbinding met jouw database via SSMS. Let op: omdat ik dezelfde database al eerder heb omgezet, heb ik al een tabel genaamd “TblUsers”. Daarom hernoem ik de tabel in het .sql-dumpbestand.
Open het bestand in je favoriete teksteditor (Visual Studio Code bijvoorbeeld). Zoek en vervang alle voorkomingen van TblUsers naar wat anders, voor het gemak noem ik het “TblUsers_vevida”.
Laad daarna het .sql-bestand in in SSMS, via File > Open > File…, of rechtstreeks CTRL+O.
Via de optie Parse kun je de SQL ‘parsen’, SSMS zal dan klagen dat er een fout in staat. Door middel van Execute voer de je de SQL statements uit en wordt de tabel aangemaakt.
Refresh dan even de objecten in de Object Explorer (rechter muisknop > Refresh)
Ook nu selecteren we dan weer de top 1000 records om te laten zien dat het gelukt is.
Het Access To MSSQL “gebrek”
Bij Vevida maken we gebruik van zogenoemde contained databases in onze SQL Servers. In een notendop houdt dit in dat de inloggegevens opgeslagen zijn in de database (zoals bij MySQL), waar dit vroeger op een niveau hoger in SQL Server was. Nu vindt authenticatie plaats op databaseniveau in tegenstelling tot serverniveau, en dat is een betere afscherming. Dit is waarom je een databasenaam moet meegeven als je met SQL Server Management Studio (SSMS) verbindt.
Helaas kan veel tooling hier nog niet mee overweg en kun je geen gebruik maken van bijvoorbeeld Microsofts eigen converteer-tool SQL Server Migration Assistant for Access. Ook Access To MSSQL van Bullzip.com kan niet direct verbinden met SQL Server. Hierom is gekozen voor het maken van een .sql-dumpbestand. Die kun je later eenvoudig uitvoeren op en importeren in de database via SSMS.
Access-database importeren met SQL Server Management Studio’s SQL Server Import and Export Wizard
Het gebruiken van de SQL Server Import and Export Wizard in SQL Server Management Studio (SSMS) is eigenlijk één van de makkelijker manieren. Net als Bullzip.com’s Access To MSSQL kun je deze tool gebruiken om in één keer alle tabellen te exporteren. Met de volgende twee kanttekeningen:
- Je moet SSMS starten als lokale administrator (beheerder), omdat anders de import stukloopt. Uiteraard is het draaien van programma’s als Administrator geen verstandige optie.
- Het .accdb bestandsformaat van Access wordt niet herkend. Alleen .mdb is mogelijk.
Als je bent ingelogd in jouw SQL Server database, klik met de rechter muisknop op jouw database, kies Tasks > Import Data….
Klik Next om verder te gaan na het welkomstscherm.
Geef aan dat je Microsoft Access (Microsoft Jet Database Engine) gebruikt als Data source.
en browse naar jouw database.
Klik op Next.
Kies SQL Server Native Client 11.0 als Destination.
Vink Use SQL Server Authentication aan en vul je database inloggegevens in. Klik dan op Next.
Laat Copy data from one or more tables or views aangevinkt staan en klik op Next.
Selecteer de tabellen en klik op Next.
Run immediately en klik op Finish >>
Er gaat van alles gebeuren
De import is klaar:
De T-SQL query
USE examplenl SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
Laat zien dat er nu 229 tabellen zijn.
ASP-connectiestring aanpassen
Hè hè, eindelijk (bijna!) klaar.
Nu de database succesvol is omgezet kun je de connectie-string wijzigen. Download via FTP de bestanden, als je die nog niet lokaal op je ontwikkelomgeving hebt staan. Verander de connectie-string (de hele regel met ‘Set Conn‘ uit het eerste stuk van dit artikel) naar het volgende blokje code:
Set Conn = Server.CreateObject("ADODB.Connection") ' Microsoft SQL Server ODBC Driver Connstr = Connstr & "DRIVER={SQL Server};" Connstr = Connstr & "DATABASE=examplenl;" Connstr = Connstr & "SERVER=sql.example.nl;" ' open de verbinding in het formaat: Connstr, "gebruikersnaam", "wachtwoord" Conn.Open Connstr, "examplenl", "P4ssword"Je vindt hier meer voorbeelden ASP en SQL Server ODBC connectie-strings.
Uiteraard moet je hier jouw eigen databasenaam, sqlserver-hostnaam, (database-) gebruikersnaam en wachtwoord ingeven. Upload alles naar je website en zie dat het werkt – hernoem je oude Access-database maar eens!
Let op: de kans is reëel dat je queries en statements moet aanpassen in jouw scripts, zodat ze voldoen aan SQL Server syntaxis. Pas hiervoor op, want dit valt buiten de scope van dit artikel!
Conclusie en afsluiting
Microsoft heeft altijd afgeraden om Access te gebruiken in een webomgeving. Door de eenvoud in het gebruik en ondersteuning van populaire scripttalen – waaronder classic ASP – werd Access veel gebruikt. Je kwam het databaseformaat tegen in login-scripts, gastenboeken, tellers en complete CMS-en werden ermee gemaakt.
Een groot nadeel van Access, naast het feit dat het één bestand is, is dat niet meerdere personen er tegelijkertijd in kunnen werken. In ons nieuwsartikel Het einde van Access-databases is nabij vind je dan ook veel foutmeldingen die wij in de loop der jaren voorbij zagen komen. Voor gebruikers is dit altijd een grote ergernis geweest.
Gelukkig heb je dit euvel niet met Microsoft SQL Server en MySQL. Een bijkomend voordeel is dat die dedicated (toegewijde) machines speciaal voor deze taak geïnstalleerd zijn en hierdoor veel sneller zijn. Het omzetten van Access naar SQL Server of
MySQL is dus óók goed voor de snelheid van jouw website, wat weer meer bezoekers en mogelijk meer omzet kan opleveren. Sweet! ?
Nog een paar tips:
- maak altijd back-ups van je .asp- en Access-databasebestanden. Download ze simpel twee keer via FTP; één keer naar een tijdelijke back-upmap en één keer naar de map vanwaaruit je werkt.
- na het maken van een extra back-up, zorg ervoor dat je Access-database in een goede conditie is:
- gebruik de Database Access Object (DAO) DBEngine.CompactDatabase methode om de database te comprimeren.
- converteer de database van MDB naar ACCDB. Als je dit weer online wilt testen, dan moet je niet alleen de .accdb-Access-database uploaden, óók moet je de connectie-string aanpassen.
Kom je er niet uit? Neem dan gerust contact op met onze klantenservice. We helpen je graag verder.