14. marts 2016 - 10:44Der er
10 kommentarer og 1 løsning
ODBC link navn i forespørgsel
Hej.
Jeg bruger 4 forskellige ODBC forbindelser til at hente data fra 4 forskellige Navision regnskaber (en pr. land).
Jeg har en makro der kan skifte mellem ODBC forbindelserne, men jeg skal fortsat lave 4 forskellige Querys for at kunne tilføje "Regnskabsland", så jeg ved, hvilket regnskab data kommer fra.
Min Pass-Through ser således ud:
SELECT * FROM Finanspost where løbenr_ > 6300000
Hvordan får jeg tilføjet en kolonne med ODBC-navnet, eller en hvis-sætning der siger, at den skal returnere "Sverige" ved denne ODBC-streng:
I dont know how you choose which "Regnskabsland" you intend using, but lets say you have a radio button group, here you would get a specific value from whatever you select. Then you can use this value in a select case
For Each qdf In CurrentDb.QueryDefs If qdf.Type = dbQSQLPassThrough Then qdf.Connect = "ODBC;DSN=DynamicGroup88;DBQ=\\SBS\RedirectedFolders\MIVE\My Documents;CODEPAGE=1252;" End If Next
Now I am using the "Denmark" ODBC connection.
Then I run this pass-through Query by SQL:
SELECT Løbenr_ as Løbenr_, (Finanskontonr_) AS Kt_Nr_, (Finanskontonavn) AS Navn_, (Bogføringsdato) AS Dato_, Bilagsnr_, (Beskrivelse) AS Beskrivelse_, ("Global dimension 1-kode") AS Afd_, Beløb AS Beløb_, Momsbeløb AS Moms_, ("Bruger-id") AS ID_, kildespor AS Kilde_, 'Grp88_DK' AS Company_ FROM Finanspost where Løbenr_ > 6300000
The output is perfect as you see I have typed 'Grp88_DK' AS Company_ and I would like the Query to find out which country from the ODBC string.
Otherwise I need 4 different pass-through queries where I only change the Company name manually - I like the SQL to find out which ODBC I use and return that in a column.
My Query using the pass-through Query is much quicker if I put in the Where statment in the pass-through query
My Navision has 6,5 mio. records and I only need to add new records since last run of the Query.
I would like the pass-through Query to find the last top 10000 looking at the "Løbenr_" column every time --> this will be dynamic as the last top 10000 will change when new records are interen in my ERP system.
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.