Databasequery

[Geoffcel]

Beste lezer,

Mijn vraag heeft betrekking tot Databasequery die ik heb gemaakt.

Situatie schets:
Ik heb bestand A met heel veel adres gegevens. Vervolgens heb ik meerdere andere bestanden die gelinkt zijn met een Databasequery aan bestand A.
De regels die opgehaald moeten worden, worden per bestand met nummers aangegeven (wijk nummers). Deze nummers heb ik dus toegevoegd onder Criteriumveld met de juiste waarde.

Mijn vraag:
Kan ik de waarde onder criteriumveld variabel maken. En het liefst linken aan een Excelblad. Dus dat ik bijvoorbeeld onder blad 2 zes criteria neer zet en dat deze mee worden genomen in de Databasequery onder criteriumveld.

Zou echt :thumb: als iemand een oplossing heeft.

Met vriendelijke groet,
Geoffcel

 

[xmlfan]

Moet kunnen maar ik weet niet zeker of we aan hetzelfde denken bij begrippen als Databasequery en criteriumveld. Graag een voorbeeldje? Een adressenbestand met 20 adressen (anoniem) en één selectiebestand moet volstaan.

 

[Geoffcel]

Moet kunnen maar ik weet niet zeker of we aan hetzelfde denken bij begrippen als Databasequery en criteriumveld. Graag een voorbeeldje? Een adressenbestand met 20 adressen (anoniem) en één selectiebestand moet volstaan.

Het is een beetje lastig om bestanden toe te voegen omdat het om 2 bestanden gaat die gekoppeld zijn d.m.v. Databasequery (Excel>Data>Externe gegevens importeren>Nieuwe database query.

In bestand A bevinden zich alle adressen en wijknummers. In diverse planningen worden clientgegevens opgehaald dmv wijknummer. (Zeg maar 1 en 2) Die ik toegevoegd heb aan de creteria in de Query.

Ik zie dat de Query uit SQL-intructies bestaat en de vraag is dus eigenlijk kan ik die SQL-instructies variabel maken.

Stel dat wijknummer 1 en 2, 4 en 6 worden in bestand A dan moeten de Querys van de planningen worden aangepast. Dan zou het handig zijn voor de gebruiker dat die gene dan alleen maar 1 en 2 hoef te veranderen in een excelblad. en niet de SQL-instructie hoeft te veranderen

Alvast bedankt,
GeoffCel

 

[xmlfan]

Ja, je kunt de sql-query variabel maken. Geef even een stukje van je adressenbestand, en een voorbeeld van een planningbestand waarin je duidelijk maakt wat het resultaat moet zijn. Dat door het uploaden de connection verbroken wordt is geen probleem.

 

[Geoffcel]

Ja, je kunt de sql-query variabel maken. Geef even een stukje van je adressenbestand, en een voorbeeld van een planningbestand waarin je duidelijk maakt wat het resultaat moet zijn. Dat door het uploaden de connection verbroken wordt is geen probleem.

Bijgaand treft u mijn fictieve clientenbestand met fictief planningsbestand.
In de planning vindt u de query met als criteria 17100 deze wil ik dus variabel krijgen.

Alvast bedankt,

Geoffrey
Bekijk bijlage Planning.xls
Bekijk bijlage Bestand A.xls

 

[xmlfan]

Plaats beide bestanden in dezelfde map.
BestandA.xls: is enigszins aangepast, gegevens beginnen nu in A1. Mag wel in een andere cel maar dan moeten de overgeslagen rijen en kolommen wel leeg blijven.
Planning.xl: open het bestand en klik op Vernieuwen. Verbinding wordt nu gelegd via de Jet OLE DB Provider en niet meer via odbc. Als dat niet goed gaat kun je nog terugvallen op odbc en de alternatieve connectionstring, zie de vba-code. De verbinding is niet gelegd via de interface maar via vba, dat geeft de gewenste flexibiliteit.
Bij het ophalen van de gegevens wordt een sql-query uitgevoerd:

SELECT * FROM [Blad1$]
WHERE  Afdelingnr = " & Range("Afdelingnr")

Range("Afdelingnr") is de benoemde cel D2.

Bekijk bijlage BestandA.xls
Bekijk bijlage Planning.xls

 

[Geoffcel]

Plaats beide bestanden in dezelfde map.
BestandA.xls: is enigszins aangepast, gegevens beginnen nu in A1. Mag wel in een andere cel maar dan moeten de overgeslagen rijen en kolommen wel leeg blijven.
Planning.xl: open het bestand en klik op Vernieuwen. Verbinding wordt nu gelegd via de Jet OLE DB Provider en niet meer via odbc. Als dat niet goed gaat kun je nog terugvallen op odbc en de alternatieve connectionstring, zie de vba-code. De verbinding is niet gelegd via de interface maar via vba, dat geeft de gewenste flexibiliteit.
Bij het ophalen van de gegevens wordt een sql-query uitgevoerd:

SELECT * FROM [Blad1$]
WHERE  Afdelingnr = " & Range("Afdelingnr")

Range("Afdelingnr") is de benoemde cel D2.

Bekijk bijlage 125323
Bekijk bijlage 125325

Beste Frans,

Macro XLQueryTableRefresh() loopt stuk op de eerste regel
Lijkt erop dat de table niet gevonden word.

Op mijn eigen pc kan ik de query bewerken dmv rechtermuisknop maar dat lukt me niet als ik via de server van mijn werk dit probeer te doen.

Echter is dit inderdaad wel de bedoeling.

Groet,
Geoffrey

 

[xmlfan]

Als je Excel 2002 of lager hebt gaat dit niet werken.
Heb je 2003 probeer dan het volgende:
- verwijder op Planning blad1 alle rijen vanaf 4;
- voer de macro QueryTableAdd uit (eenmalig).
Als het stuk loopt: op welke regel stop de vbe precies en welke fout geeft hij?

Op mijn eigen pc kan ik de query bewerken dmv rechtermuisknop maar dat lukt me niet als ik via de server van mijn werk dit probeer te doen.

Dit snap ik niet.

 

[Geoffcel]

De foutmelding die ik krijg op mijn computer is.

Ongeldige Procedure-aanroep of ongeldig argument

Vervolgens klik ik op fout opsporen en wordt VBA geopend.

  With ActiveSheet.ListObjects.Add( _
            SourceType:=xlSrcExternal, _
            Source:=fnGetConnection, _
            Destination:=Range("$B$4")).QueryTable ' <<< set target cell

Dit wordt helemaal in geel aangegeven.

Inderdaad op de server gebruiken ze nog Excel 2002 :shocked:

 

[Geoffcel]

Ik heb nu de onderstaande code gemaakt met macro opnemen en heb zelf wat aangepast. Het werkt alleen maar met 1 kostenplaats in zou er graag meerdere willen gebruiken. Max 20. is hier nog een oplossing voor?

Sub TestmaakDatabase()

    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DBQ=O:\PlanningTZ\ZZ_Systemfiles\01. Cliënten\2011 test dumpen Elmo\clienten.xls;DefaultDir=O:\PlanningTZ\ZZ_Systemfiles\01. Cl" _
        ), Array( _
        "iënten\2011 test dumpen Elmo;Driver={Microsoft Excel Driver (*.xls)};DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;MaxScanRows=" _
        ), Array( _
        "8;PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=3;UserCommitSync=Yes;")) _
        , Destination:=Range("D7"))
        .CommandText = Array( _
        "SELECT `Klanten$`.`Cliëntnr-productnr`, `Klanten$`.Besluitnummer, `Klanten$`.Productnr, `Klanten$`.Productomschrijving, `Klanten$`.Afdelingnr, `Klanten$`.Afdeling, `Klanten$`.Voorletters, `Klanten$`.N" _
        , _
        "aam, `Klanten$`.`BSN cliënt`, `Klanten$`.`Geboorte datum`, `Klanten$`.Cliëntnr, `Klanten$`.Straatnaam, `Klanten$`.Toev, `Klanten$`.Nr, `Klanten$`.`Huisnummer en toevoeging`, `Klanten$`.Postcode, `Klan" _
        , _
        "ten$`.Plaats, `Klanten$`.Telefoonnummer, `Klanten$`.`Datum ingang`, `Klanten$`.`Datum einde`, `Klanten$`.`Uren per week`, `Klanten$`.Geslacht, `Klanten$`.`Mw/Dhr/Fam`, `Klanten$`.Contactpersoon, `Klan" _
        , _
        "ten$`.`Tel contactpersoon`" & Chr(13) & "" & Chr(10) & "FROM `Klanten$` `Klanten$`" & Chr(13) & "" & Chr(10) & _
        "WHERE  Afdelingnr = " & Range("Afdelingnr") _
        )
        .Name = "Query van ELMO_CLIENTEN_TEST_5"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[xmlfan]

Goed idee om de macrorecorder om advies te vragen. Nu moet het ook op je server met xl2002 en odbc werken, zie bijlage. Te gebruiken icm de eerder geposte bestanda.xls.

 

[Geoffcel]

Hoi Frans,

Heel erg bedankt,

Groeten