08. februar 2008 - 12:56Der er
6 kommentarer og 1 løsning
Kalde access makro fra excel - konflikt med Pivot
Hej eksperter,
Jeg har en makro i Access som laver en masse databehandling - og det virker helt efter hensigten.
For at få et godt overblik, har jeg benyttet Excel til at vise en række Pivot-tabeller baseret på resultatet af min Access makro (en enkelt tabel).
Når jeg skal opdatere data skal makroen køres - dette skal gøres ret ofte. Resultatet aflæses ved at se på Pivot-tabellerne i Excel.
Problemet er, at på grund af dataforbindelsen som Pivot-tabellerne automatisk etablerer når Excel-filen åbnes, bliver nødt til at lukke Excel, åbne Access, kalde makroen, lukke Access, åbne Excel, for at se resultatet.
Jeg har brug for en Sub i VBA, som kaldes via en knap i Excel. Sub'en skal køre makroen i Access.
Følgende kode er afprøvet, men fejler, fordi Access-filen er låst af de dataforbindelser som Pivot-tabellerne anvender: [CODE] Dim A As Object Application.DisplayAlerts = False Set A = CreateObject("Access.Application") A.Visible = False A.OpenCurrentDatabase ("Path of your database") A.DoCmd.RunMacro "Name of your macro" Application.DisplayAlerts = True [/CODE]
Jeg forestiller mig, det er nødvendigt at midlertidigt lukke for Pivot-tabellernes dataforbindelser for at 'åbne' ovenstående kodes adgang til Access-filen, men hvordan gøres det?
Is this all the code you have? You should also quite Access when you r finished
A.Quit
[CODE] Dim A As Object Application.DisplayAlerts = False Set A = CreateObject("Access.Application") A.Visible = False A.OpenCurrentDatabase ("Path of your database") A.DoCmd.RunMacro "Name of your macro" Application.DisplayAlerts = True [/CODE]
Aye, true... however, Access is never opened since it failes here: A.OpenCurrentDatabase("...") with the following error (7866):
Microsoft Office Access can't open the database because it is missing, or is exclusively opened by another user, or it is not an ADP file.
As discribed above, my guess is that the Pivot tables - through the dataconnections created by data->connections - have locked my access to the database.
First, have you tried opening the dB from Excel when there is no other connection? Just to make sure you are actually able to open it.
Next, mak esur ethat the dB is set so that it is possible to open it in multi user mode (not exclusive) otherwis eyou wont be able to because dB is locked by the first user. You could test this be first opening the dB and then open Excel.
I know very littl eabout Excel but do you make the connection to Access fro mExcel i ncod eor is it done automatically by Excel?
[QUOTE]Next, mak esur ethat the dB is set so that it is possible to open it in multi user mode (not exclusive) otherwis eyou wont be able to because dB is locked by the first user. You could test this be first opening the dB and then open Excel.[/QUOTE]
Actually, I am not able to open Access when having the Excel sheet open. The question is how I change this. The connection is set like this: Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=D:\Sample.accdb;Mode=Share Deny Write;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False
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.
