Access97 == SQL Server

[NLCOMP]

Bericht 1 van 9

NL Computer Forum ~ SQL & Programmeren

Van	:	Hugo Kornelis	Datum	:	11-10-2004
Aan	:	All	MsgID	:	1462.1
Onderwerp	:	Access97 == SQL Server	Forum	:	ws-nlcomputer

Hoi,

Ik gebruik Access 97 om een eenvoudig front-end te maken voor een SQL Server database. Wat ik tot nu toe gebruik, en waar ik goede resultaten mee boek, is de tabellen te koppelen (via ODBC) en dan formulieren te ontwerpen met de gekoppelde tabellen als recordbron.


Ook stored procedures die een resultset opleveren kan ik koppelen: ik maak een pass-through query met als opdracht "EXECUTE procnaam" en dan kan ik ook die uitvoer als basis gebruiken voor een formulier (dat ik dan uiteraard wel read-only maak).


Ik heb ook stored procedures die een actie uitvoeren in de database maar geen resultaat geven. Ook daarvoor maak ik een query (met de eigenschap "Geeft records" ingesteld op "Nee"). Deze voer ik uit door bij het klikken op een button een DoCmd.OpenQuery "querynaam" uit te voeren. Tot zover geen probleem.


Op dit moment probeer ik ook weer een button te maken die een actie uitvoert (via een stored procedure). Het probleem is alleen dat deze stored procedure een parameter als invoer nodig heeft. Ik kan deze waarde hard coderen, dan ziet de pass-through query er bijvoorbeeld zo uit: "EXECUTE procnaam 123" en dan werkt het goed. Maar in werkelijkheid is de waarde van de parameter niet altijd 123, dit moet variabel zijn.


De logische manier om dit te doen is een parameter op te geven in de query. Helaas lukt dit niet - Access staat bij pass-through queries geen parameters toe. Een andere manier (die ik in het verleden wel eens gebruikt heb) is om in de code achter de button eerst een nieuwe waarde te geven aan de eigenschap .SQL van de betreffende QueryDef. Dit werkt, maar ik vindt het eerlijk gezegd niet fraai.

Een beter methode lijkt mij om in dit geval gebruik te maken van de methode Connection.Execute. Om dat te kunnen doen moet ik natuurlijk wel zorgen dat ik een bruikbaar Conenction-object hb in mijn code. Kan iemand mij hier mee helpen (bijvoorbeeld een stukje voorbeeldcode)? Of zie ik wellicht zelfs een veel eenvoudigere oplossing over het hoofd?

Alle suggesties zijn welkom!

Groetjes, Hugo

Bericht 2 van 9

NL Computer Forum ~ SQL & Programmeren

Van	:	Michel Uphoff (Sysop)	Datum	:	11-10-2004
Aan	:	Hugo Kornelis	MsgID	:	1462.2
Onderwerp	:	Access97 == SQL Server	Forum	:	ws-nlcomputer

Dag Hugo,

Een inputbox waarin men de naam van de procedure in kan geven, of een keuzelijst met voorgedefinieerde namen volstaat niet?

Zou in VBA eenvoudig te maken moeten zijn.

Michel Uphoff
(Sysop)

http://tinyurl.com/m8fp


Bericht 3 van 9

NL Computer Forum ~ SQL & Programmeren

Van	:	Hugo Kornelis	Datum	:	12-10-2004
Aan	:	Michel Uphoff (Sysop)	MsgID	:	1462.3
Onderwerp	:	Access97 == SQL Server	Forum	:	ws-nlcomputer

Hoi Michel,

Helaas ... dat is niet wat ik bedoel. Kennelijk ondanks de lengte van het bericht toch niet goed uitgelegd. Ik zal het nog eens proberen, nu in een kort bericht.

Vanuit de Access applicatie moet, als de gebruiker op een knop klikt, een opdracht naar SQL Server worden gestuurd. Die opdracht is dan bijvoorbeeld zoiets als
EXECUTE ZetStatus 'Actief'
maar (afhankelijk van de informatie op het scherm, gebruikersinvoer, etc) de opdracht kan net zo goed zijn
EXECUTE ZetStatus 'Pauze'
en zo zijn er nog meer mogelijkheden.

Ander voorbeeld (ander scherm, ander knopje, zelfde probleem):
EXECUTE GeefKorting 776133, 5
waarbij 776133 een ordernummer is en 5 een kortingspercentage. En waarbij dus zowel het ordernummer als het kortingspercentage afkomstig zijn van het scherm.

Wat ik dus wil is vanuit Access/VBA de opdracht formeren (eenvoudig) en die dan naar SQL Server sturen. Daarbij wil ik gebruik maken van de dan al bestaande verbinding tussen Access en SQL Server (die gemaakt wordt op het moment dat de eerste gekoppelde tabel gebruikt wordt en daarna in stand blijft en ook voor andere gekoppelde wordt gebruikt), zodat de gebruiker niet nogmaals een inlogprompt krijgt.

Is het zo wat duidelijker wat ik bedoel?

Groetjes, Hugo

Bericht 4 van 9

NL Computer Forum ~ SQL & Programmeren

Van	:	John Kopmels (Sysop)	Datum	:	13-10-2004
Aan	:	Hugo Kornelis	MsgID	:	1462.4
Onderwerp	:	Access97 == SQL Server	Forum	:	ws-nlcomputer

>> Op dit moment probeer ik ook weer een button te maken die een actie uitvoert (via sp).
Het probleem is alleen dat deze stored procedure een parameter als invoer nodig heeft.
Ik kan deze waarde hard coderen, dan ziet de pass-through query er bijvoorbeeld zo uit: "
EXECUTE procnaam 123" en dan werkt het goed.
Maar in werkelijkheid is de waarde van de parameter niet altijd 123, dit moet variabel zijn.

De logische manier om dit te doen is een parameter op te geven in de query.
Helaas lukt dit niet - Access staat bij pass-through queries geen parameters toe.
Een andere manier (die ik in het verleden wel eens gebruikt heb) is om in de code achter
de button eerst een nieuwe waarde te geven aan de eigenschap .SQL van de betreffende QueryDef.
Dit werkt, maar ik vindt het eerlijk gezegd niet fraai.

Een beter methode lijkt mij om in dit geval gebruik te maken van de methode Connection.Execute.
Om dat te kunnen doen moet ik natuurlijk wel zorgen dat ik een bruikbaar Conenction-object hb
in mijn code. Kan iemand mij hier mee helpen (bijvoorbeeld een stukje voorbeeldcode)?
Of zie ik wellicht zelfs een veel eenvoudigere oplossing over het hoofd?

Met DAO kom ik ook niet veel verder dan een temp querydef en manipuleren van de sql

Code: [Selecteer]

Dim dbs As Database
Dim qdf As QueryDef
'
Set dbs = CurrentDb()
Set qdf = dbs.CreateQueryDef("")
'
qdf.Connect = sConnect ' een predifined connectstring
qdf.SQL = "SPnaam " & var1 & ", " & var2
qdf.ReturnsRecords = True ' of False


Mogelijk kom je met ADO verder, voorbeeldje

Code: [Selecteer]

Dim c As ADODB.Connection
Dim m As ADODB.Command
Dim sConn As String: sConn = "DSN=Pubs;"
'
Set c = New ADODB.Connection
  With c
    .ConnectionString = sConn 
    .CursorLocation = adUseClient
    .Open
  End With
  '
  Set m = New ADODB.Command
    With m
       .ActiveConnection = c 
       .CommandType = adCmdStoredProc
       .CommandText = "SPnaam"
       .Parameters(1) = var1 
       Set r = .Execute()
     End With
je kan ook een Connectie Public declareren en die de hele sessie openhouden

vanaf Access 2000 (ook weer in ADO) kan je met de CurrentProject.Connection werken

Voorbeeldje ADO met SQLOLEDB driver

Public cConnectie As String

Code: [Selecteer]

cConnectie = "Provider=SQLOLEDB.1" & _
";User ID='xxxxxxx'" & _
";Password='xxxxxx'" & _
";Initial Catalog='pubs'" & _
";Data Source='Servernaam'"
helemaal handig is een adp (vanaf access 2000 maar alleen aan te raden vanaf 2002)

Groetjes --John


Bericht 5 van 9

NL Computer Forum ~ SQL & Programmeren

Van	:	Michel Uphoff (Sysop)	Datum	:	13-10-2004
Aan	:	Hugo Kornelis	MsgID	:	1462.5
Onderwerp	:	Access97 == SQL Server	Forum	:	ws-nlcomputer

>> Is het zo wat duidelijker wat ik bedoel? <<

Hoi Hugo,

Jazeker, maar een oplossing weet ik dus niet. Ik houd mij niet met Access bezig.

Michel Uphoff
(Sysop)

http://tinyurl.com/m8fp


Bericht 6 van 9

NL Computer Forum ~ SQL & Programmeren

Van	:	Hugo Kornelis	Datum	:	14-10-2004
Aan	:	Michel Uphoff (Sysop)	MsgID	:	1462.6
Onderwerp	:	Access97 == SQL Server	Forum	:	ws-nlcomputer

Hoi Michel,

Jammer. In elk geval bedankt voor het meedenken!

Groetjes, Hugo

Bericht 7 van 9

NL Computer Forum ~ SQL & Programmeren

Van	:	Hugo Kornelis	Datum	:	14-10-2004
Aan	:	John Kopmels (Sysop)	MsgID	:	1462.7
Onderwerp	:	Access97 == SQL Server	Forum	:	ws-nlcomputer

Hoi John,

> Met DAO kom ik ook niet veel verder dan een temp querydef en manipuleren
> van de sql

Ja, dat werkt. Het was even zoeken naar de juiste connect string, tot ik probeerde of het voldoende was om de connectstring van een andere (bestaande) pass-through query of gekoppelde tabel te gebruiken. Dat lukte. In mijn code staat nu dus simpelweg:
qdf.Connect = dbs.QueryDefs("dbo_g_Conv").Connect
(waarbij dbo_g_Conv de gekoppelde tabel is waar het betreffende formulier op gebaseerd is).

Ik heb hier overigens nog wel een vraagje over: betekent het gebruik van een temp querydef dat mijn database nu niet in de loop van het gebruik continu za groeien (zoals volgens mij wel het geval is bij het via VBA wijzigen van de SQL van een reguliere query)?

Ik zal nog wel moeten uitzoeken of in dit geval inderdaad de bestaande connectie wordt gebruikt. Op dit moment werk ik via trusted connection, dus krijg ik sowieso geen inlogprompt. Later zal ik een testomgeving opzetten waar ik wel een inlogprompt zie. Maar ik verwacht dat dit goed zal gaan, want via SQL Profiler zie ik geen extra in- en uitlog gebeurtenissen verschijnen.

> Mogelijk kom je met ADO verder, voorbeeldje

Ik heb er even naar gekeken, maar ik krijg een compileerfout (een door de gebruiker gedefinieerd gegevenstype is niet gedefinieerd). Vast wel eenvoudig te verhelpen, maar dat loont voor mij niet de moeite - ik heb immers al een werkende oplossing. Bovendien kan ik me voorstellen dat een oplossing via ADO wel een extra inlog-actie (met mogelijke wachtwoord-prompt) oplevert, en dat wil ik nu juist voorkomen.

> je kan ook een Connectie Public declareren en die de hele sessie openhouden

Ik heb dus al een verbinding (voor de gekoppelde tabellen en voor enkele andere pass-through queries) die ik wil gebruiken.

> vanaf Access 2000 (ook weer in ADO) (....)

Helaas.....

> helemaal handig is een adp (vanaf access 2000 maar alleen aan te raden
> vanaf 2002)

Nogmaals: helaas......

Ik zit voorlopig nog wel even vast aan Access 97, vrees ik. Ik heb Office 2000 wel eens genstalleerd gehad, maar door allerhande problemen (vraag me niet welke, dat weet ik niet meer) besloten gauw terug te gaan naar Office 97. Dat werkt tenminste....

Groetjes, Hugo

Bericht 8 van 9

NL Computer Forum ~ SQL & Programmeren

Van	:	John Kopmels (Sysop)	Datum	:	14-10-2004
Aan	:	Hugo Kornelis	MsgID	:	1462.8
Onderwerp	:	Access97 == SQL Server	Forum	:	ws-nlcomputer

>> Met DAO kom ik ook niet veel verder dan een temp querydef en manipuleren van de sql

> Ja, dat werkt. Het was even zoeken naar de juiste connect string,
tot ik probeerde of het voldoende was om de connectstring van een
(bestaande) pass-through query of gekoppelde tabel te gebruiken.
Dat lukte. In mijn code staat nu dus simpelweg:
qdf.Connect = dbs.QueryDefs("dbo_g_Conv").Connect

Ik heb hier overigens nog wel een vraagje over:
betekent het gebruik van een temp querydef dat mijn database
nu niet in de loop van het gebruik continu za groeien

je zal regelmatig moeten comprimeren ja, overigens groet
ie ook bij het steeds aanpassen van een bestaande querydef

---------------------------------------------------------------------------

>> Mogelijk kom je met ADO verder, voorbeeldje

> Ik heb er even naar gekeken, maar ik krijg een compileerfout
(een door de gebruiker gedefinieerd gegevenstype is niet gedefinieerd).

Dat komt door het ontbreken van een ADO verwijzing (ctrl+G => Tools => References)
---------------------------------------------------------------------------

> Ik zit voorlopig nog wel even vast aan Access 97, vrees ik.
Ik heb Office 2000 wel eens genstalleerd gehad, maar door
allerhande problemen (vraag me niet welke, dat weet ik niet meer)
besloten gauw terug te gaan naar Office 97. Dat werkt tenminste....

Office (en met name ook Access) 97 is gewoon een van de beste versies
naast stabiel ook lekker snel te installeren en weinig flauwekul ...
overigens kan je in 97 gewoon met ado werken mits reference naar ADO

2000 zou ik ook overslaan, maar 2002 en 2003 zijn weer wel goed
ik moet ze beroepshalve wel installeren mede vanwege het adp verhaal

Nu moet ik zeggen dat in een adp de samenwerking met SQL server wel
erg mooi is, je kan er zelfs enige administratieve dingen mee doen

Zelf heb ik de versies 2 - 97 - 2000 - 2002 -2003 samen op elke machine staan
je moet ze alleen altijd van laag naar hoog installeren en slechts 1 versie
(Office 97 bij mij) in de default map, de rest staan op een andere partititie
en echt *nooit* enig probleem

Mocht je ooit eens willen expirimenteren zet dan alleen Access uit office 2002
eens op een andere partitie, let op dat je custom installatie kiest en kies
'oudere versies behouden', vooral met adp's zal je veel plezier hebben .. !

Groetjes --John


Bericht 9 van 9

NL Computer Forum ~ SQL & Programmeren

Van	:	Hugo Kornelis	Datum	:	14-10-2004
Aan	:	John Kopmels (Sysop)	MsgID	:	1462.9
Onderwerp	:	Access97 == SQL Server	Forum	:	ws-nlcomputer

Discussie bekeken : 3941 Discussie gestart door : Discussie gestart op : 11-10-2004 15:11