12. marts 2016 - 21:56Der er
11 kommentarer og 1 løsning
Skift ODBC streng i makro
Hej.
Jeg har 4 forskellige regnskaber i NAV som jeg tilgår via ODBC i MS Access.
Jeg hiver diverse tabeller ud i Access via pass-through forespørgsler og laver så yderligere forespørgsler derfra, for at gøre data klar til Pivot tabeller i Excel.
Jeg er helt ny i pass-through, og jeg vil gerne have én Access database, hvor jeg kan lave alle mine pass-through til alle 4 regnskaber.
Hidtil har jeg kun kunnet bruge en ODBC adgang pr. database, så jeg har altså 4 databaser nu - en per regnskab.
Jeg har i dag lavet 2 pass-through forespørgsler med disse ODBC tilslutningsstrege:
De fungere fint nok, men jeg er nød til at lukke databasen ned, hvis jeg vil kører den anden - altså jeg kan ikke kører begge pass-through uden at lukke access mellem forespørgslerne.
Hjælp, hvordan får jeg access til at acceptere skifte ODBC-tilslutningsstreng via vba, således jeg kan kører udtræk fra alle mine regnskaber i samme makro/VBA?
Det skal naturligvis være automatisk det hele. Jeg kalder alle mine forspørgsler fra et VBA-modul, så kan jeg implementere et ODBCstreng skifte her, så vil det være perfekt.
Min pass-through ser således ud: SELECT * FROM Finanspost
Its possible to alter ODBC connection string for your objects so that you only need one query, assuming that the fields etc are the same in each table.
you can find a number of examples on the web on how to re-link your ODBC tables and queries.
Great I did hope you will answer as you help a lot of people in here.
I am not that good at VBA in Access so can you help me writing the correct sub or public funcktion which I believe I must enter into a module in visual basic?
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 End Sub
Though I need to restart MSAcces every time I want to change the ODBC to another.
I have this Pass-Through SQL:
SELECT * FROM Finanspost where løbenr_ > 330000
I need to add a column with the Country name as I use the same query know but how do I add a column name to a pass-through?
This dos not work:
SELECT *, 'Denmark' AS Country FROM Finanspost where løbenr_ > 330000
And finally how do I make my WHERE statement dynamic? (Very needed due to a database of more than 300000 rows)
I want only new rows added to my local database but
SELECT TOP 10 * FROM FINANSPOST dos not work and I also tryed LIMIT 10 !!??
I would be a great help if you can help me out as I struggled for several years with to many queries for each 4 Countries.
Hi micaud, sorry for not getting back sooner, lots of other tasks keeping me busy :-)
It should not be necessary to restart Access after changing ODBC connection string. And although I've never had the need to use NAV I dont think that would make any difference.
To get the connection string into the Modulke I woul duse a Function rather than a SUB.
Public Function RelinkTables(ByVal conStr As String) As Boolean
If I change the code below from "Group88" to "Sverige" then it will nok connect to "Sverige" before I restart Access.
Public Function Grp88_DK() Dim qdf As QueryDef
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
I'm guessing that its a passthrough query your trying to get working?
The syntax in the SQL must be NAV compliant and not Access, maybe try changing " to '
TOP X will give you as many records as it can find within the criteria. So it can return less than X. It may also return records which you already have in your local table. So it would be an idea to use > SomeNumber rather than TOP X
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.
