16. ACCESS en SQL-server

Met de optie Externe gegevens ophalen, tabellen koppelen kan men SQL-tabellen in een access MDB beschikbaar maken.

De beschikbare tabellen kunnen net als lokale tabellen behandeld worden, dus invoer, query's, formulieren en rapporten kunnen gebruikt worden.

SQLtabellen kunnen ook in een access MDB geimporteerd worden. De procedure gebeurt op een gelijkaardige manier, doch men kiest voor importeren.

Er zijn echter problemen met het exporteren van accesstabellen naar een SQLserver. Om toch via access SQLtabellen te bewerken maakt men gebruik van PASS THROUGH- query's.    Een paar voorbeeld.

16.1 PASS THROUGH- query's.

Creatie van een tabel met een primary key

tabel: cursus
velden: code (autonummering), omschrijving (varchar 30), aantal (int)
primary key: op code

CREATE TABLE cursus ( code integer IDENTITY (1, 1) NOT NULL PRIMARY KEY ,
    omschrijving varchar (30) NULL ,   aantal integer NULL )

Tijdens het uitvoeren van de query wordt gevraagd naar de Machine Data Source , de aanmeldings-id en het wachtwoord.
Een succesvolle creatie wordt beëindigd met volgende melding:

De ingevoerde aanmeldings-id wordt de eigenaar van de tabel en heeft de rechten om de tabel te bewerken. Door het volgend commando als Pass-through query door te sturen kan men de permissies op de tabel instellen.

GRANT references, select, insert, delete, update on cursus to leraar

Een veld aan een tabel toevoegen

tabel: cursus
velden:  prijs (valuta)

ALTER TABLE cursus ADD prijs money

Een volledige tabel wissen

DROP TABLE cursus

Bij elke bewerking met een PASS THROUGH- query wordt  naar de Machine Data Source , de aanmeldings-id en het wachtwoord gevraagd.   Door te werken met modules kan men dit probleem vermijden.

16.2 MODULES

Creatie van een tabel met een primary key

tabel: cursus
velden: code (autonummering), omschrijving (varchar 30), aantal (int)
primary key: op code

Dim db As Database, q As QueryDef

Dim SQL As String
Set db = CurrentDb()
On Error Resume Next
DoCmd.DeleteObject acQuery, "nieuwe_tabel"
On Error goto 0

SQL = "CREATE TABLE cursus ("
SQL = SQL + " code integer IDENTITY (1, 1) NOT NULL PRIMARY KEY ,"
SQL = SQL + " omschrijving varchar (30) NULL ,"
SQL = SQL + " aantal integer NULL )"
Set q = db.CreateQueryDef("nieuwe_tabel")
q.Connect = "ODBC;DATABASE=school;UID=sa;PWD=;DSN=school"
q.SQL = SQL
q.Close
DoCmd.OpenQuery "nieuwe_tabel"

Een veld aan een tabel toevoegen

tabel: cursus
velden:  prijs (valuta)

Dim db As Database, q As QueryDef

Dim SQL As String
Set db = CurrentDb()
On Error Resume Next
DoCmd.DeleteObject acQuery, "wijzig_tabel"
On Error goto 0

SQL = "ALTER TABLE cursus ADD prijs money"

Set q = db.CreateQueryDef("wijzig_tabel")
q.Connect = "ODBC;DATABASE=school;UID=sa;PWD=;DSN=school"
q.SQL = SQL
q.Close
DoCmd.OpenQuery "wijzig_tabel"

Een volledige tabel wissen

tabel: cursus

Dim db As Database, q As QueryDef

Dim SQL As String
Set db = CurrentDb()
On Error Resume Next
DoCmd.DeleteObject acQuery, "wijzig_tabel"
On Error Goto 0

SQL = "DROP TABLE cursus "

Set q = db.CreateQueryDef("wijzig_tabel")
q.Connect = "ODBC;DATABASE=school;UID=sa;PWD=;DSN=school"
q.SQL = SQL
q.Close
DoCmd.OpenQuery "wijzig_tabel"

Gegevens toevoegen in een tabel

Dim ws As Workspace, con As Connection

strPostcode = InputBox("postcode")
strGemeente = InputBox("gemeente")


' Een ODBC-werkruimte maken en een verbinding
' met een SQL Server-database openen.
Set ws = CreateWorkspace("ODBCwerkruimte", "", "", dbUseODBC)
Set con = ws.OpenConnection("school", dbDriverNoPrompt, False, "ODBC;DATABASE=school;UID=leraar;PWD=leraar;DSN=school")

' Recordset van het type dynamisch openen.
Set r = con.OpenRecordset("gemeenten", dbOpenDynamic, , 1)

r.AddNew
r![postcode] = strPostcode
r![gemeente] = strGemeente
r.Update

r.Close