Excellent

Hallo Excellenzen,
ich benutze eine Access-DB als Datentopf, aus dem ich per Excel-VBA Teildatenmengen in Excel übernehme und in Excel weiter verarbeite. Die Aktualisierung des Datentopfes erfolgt per VBA aus Access heraus. Um nicht jeden Anwender an die Acess-DB heranzulassen, möchte ich die entsprechenden Access-VBA-Prozeduren per VBA aus Excel heraus starten. Hat jemand Erfahrung wie man das anstellt?
Vorab schon mal Danke und Gruß
Dirk

Ömm ja - ich  ;-)

 

Hmm....konkreter bitte:

1) Du willst die VBA-Makros in Access aus Excel aufrufen ?

oder

2) die bisher in Access laufenden Makros nach Excel übernehmen und
von da aus aufrufen ?

Beides machbar  ::--))

 Mit freundlichsten Grüßen

Peter Murschall

 

Hallo Peter,
der von Dir unter 1) beschriebene Weg ist das, was ich mir vorstelle.
Gruß - Dirk

Hallo Dirk

das impliziert aber, daß der Anwender Access installiert haben muß somit sehr wohl auf den "Datentopf" zugreift - wenn auch indirekt, mittels Excel-VBA.
Willst Du das wirklich so haben ?

Aber als 1. Schritt in diese Richtung :

Sub DBZugriff_Late_Binding()

  Dim oAccess As Object
    Set oAccess = CreateObject("Access.Application")
      With oAccess
        .OpenCurrentDatabase filepath:="E:\Daten\AS400\Absatzplan 2002.MDB", exclusive:=False
        .DoCmd.OpenReport reportname:="Bericht1", View:=0   '= acviewnormal
        .CloseCurrentDatabase
      End With
    Set oAccess = Nothing

End Sub

oder:

Sub DBZugriff_Early_Binding()
'Verweis auf Microsoft Access Object Library muß gesetzt sein !!
'
'Bezeichnung: Microsoft Access 11.0 Object Library
'Name: Access
'Pfad: C:\Program Files\Microsoft Office\OFFICE11\MSACC.OLB
'GUID: {4AFFC9A0-5F99-101B-AF4E-00AA003F0F07}
'Standard -Verweis: Falsch

  Dim oAccess As Access.Application
   
    Set oAccess = New Access.Application
      With oAccess
        .OpenCurrentDatabase filepath:="E:\Daten\Absatzplan_2009.MDB", Exclusive:=False
        .DoCmd.OpenReport reportname:="Bericht1", View:=acViewnormal
        .CloseCurrentDatabase
      End With
    Set oAccess = Nothing

End Sub

Zu DoCmd siehe Access-VBA-Online Hilfe.

Viel Spaß beim Fröseln ::-)))))

Mit freundlichsten Grüßen

Peter

Hallo Peter,
danke für Deine Anregungen. Die haben mir auf die Sprünge geholfen. Irgendwie hatte ich einen Knoten im Hirn. (Die Zeit zum "Fröseln" war knapp, deshalb meine späte Reaktion.)

Meine Excel-Lösung sieht im Kern jetzt so aus:

Sub BatchProcess()
  Dim acAPP As New Access.Application
 
  Application.DisplayAlerts = False
  Set acAPP = CreateObject("Access.Application")
  With acAPP
    .OpenCurrentDatabase "D:\xxx\db1.mdb"
    .Visible = False
    .DoCmd.SetWarnings False
    .Run "Refresh_All_Tables"
    .CloseCurrentDatabase
  End With
  Application.DisplayAlerts = True
  Set acAPP = Nothing

End Sub

Die eigentliche Aktualisierung der DB erfolgt durch die Prozedur Refresh_All_Tables, die in einem VBA-Modul in der Access-Datenbank steht. Bedingt durch eine Laufzeit von ca. 5 Minuten, hagelt es die eine oder andere Warnung, die ich ausschalte, da ja alles im Batch laufen soll.

Die zugehörige Access-Prozedur sieht im Kern so aus:

Sub Refresh-All_Tables()

  Dim dbCon As New ADODB.Connection
  Dim db As Database
  Dim strConn As String
 
  'Datenverbindung zur Oracle-Datenbank aufbauen
  strConn = "Driver={Microsoft ODBC for Oracle};"
  strConn = strConn & "Server=DB11-ORCL.WORLD;"
  strConn = strConn & "UID=user_xyz;"
  strConn = strConn & "Pwd=xyz_geheim;"
  dbCon.ConnectionString = strConn
  dbCon.Open
 
  Set db = CurrentDb()
 
  'Zieltabelle leeren
  db.Execute "Delete * from [tblTarget]"
  'Zieltabelle füllen
  db.Execute "INSERT INTO [tblTarget] " & _
             "SELECT [tblSource].* " & _
             "FROM [tblSource];"
  'Datenverbindung zur Oracle-Datenbank schließen
  dbCon.Close

End Sub

Alles läuft wie gewünscht, bis auf einen "Schönheitsfehler. Obwohl ich im Connection-String das Passwort für den Zugriff auf die Oracle-DB mitgebe, wird beim allerersten Datenzugriff auf eine Oracle-Tabelle (das ist hier das Select-Statement) das Passwort nochmals abgefragt. Bei weiteren Selects oder einem erneuten Starten der Prozedur erfolgt keine weitere Passwortabfrage - so lange ich die Access-DB nicht schließe und wieder öffne. Da das Ganze als Batch laufen soll, ist jede angeforderte Eingabe ein Showstopper. Hier klemmt's also noch.

Hast Du etwas in Deinem Erfahrungsschatz, das mir weiter hilft?

Herzlichen Dank und Excel-lenten Gruß
Dirk

P.S.: Die Excel-lenzen mögen mir verzeihen, dass ich hier eine Access-Frage platziere

Raaaaahhh ! Ich hab's geahnt :

Mit Excel über Access auf Oracle - dat sinn mir die Rischtijen !!
Woher kriegt die Oracle-DB ihre Daten ? Evtll. von AS4000\DB2 ?? <sg><sg><sg>

Nunja, welche Oracle Version ?
Anbindung über ODBC ?
     Wenn ja, wie ?

Mir ist da so schwach was in Erinnerung, das ich das schonmal hatte....
und irgendwie bei Onkel Google nachgefragt hatte und dann bei Oracle bzw. M$ selber fündig geworden bin.
Leider habe ich jetzt aber  keine Zeit zum suchen, aber ich behalte das mal in der Liste und werde sporadisch danach suchen, wenn Dir das genügt ::-)

Mit freundlichsten Grüßen

Peter

PS:

Dattissene Oracle-Frage, verpackt in ACCESS und versteckt hinter Excel )))))

Hallo Peter,

nun ja, die Oracle-DB ist sowas ähnliches wie ein Datawarehouse und zieht ihre Daten aus den unterschiedlichsten operativen Systemen, z.T. in der Nacht, z.T. sekundenaktuell. Intranet basierende Berichte setzen darauf auf. Ich hänge mich an den Oracle-Datentopf und ziehe für Spezialberichte eine Untermenge der Daten ab, damit die Spezialberichte konsistent zu den übrigen Berichten sind. Die Berichte müssen in Excel vorliegen (Forderung der Anwender). Da die Datenmengen und die damit verbundenen "SVerweise" Excel überfordern, geht der Umweg über die Access-DB.

siehe: Sub Refresh-All_Tables()Sub Refresh-All_Tables()

Ja, ja, absolut. Ich suche auch weiter. Falls ich was finde, lasse ich es Dich wissen.

Danke für Mithilfe und herzlichen Gruß

Dirk

Ich hab's! Das läuft jetzt stabil.

Ich hatte in die Access-DB die Oracle-Tabellen als Verknüpfung eingebunden (ohne Passwort, deshalb die Nachfrage). Die ADO-Verbindung zur Oracle-DB war korrekt und funktionsfähig, nur leider habe ich sie nicht genutzt, sondern habe die Verbindung über die verknüpften Tabellen gewählt.

Bei der Gelegenheit habe ich auch mein ADO-DAO-Durcheinander aufgeräumt. Jetzt werden jede Nacht über Excel-VBA die Daten der Acess-DB aus einer Oracle-DB aktualisiert, Datenteilmengen in Excel exportiert und dynamische EXcel-Berichte und statische Powerpoint-Berichte erzeugt.

Nochmals Danke für Unterstützung und Anregungen. Das hat wie immer geholfen.
Gruß - Dirk

P.S.: Falls es interessiert, hier noch die Acess-VBA-Prozedur zur Aktualisierung der Acesstabellen. Nicht wundern über die feldweise Abarbeitung der Recordsets. Die Sytax für ein ordenliches INSERT INTO habe ich (noch) nicht hinbekommen.

Sub hmcADORefreshFromOracleDB(strSourceTable As String, _
strTargetTable As String, strWhereClause As String)

  Dim dbConRead As New ADODB.Connection
  Dim dbConWrite As New ADODB.Connection
  Dim rsRead As New ADODB.Recordset
  Dim rsWrite As New ADODB.Recordset
  Dim strConn As String
  Dim f As Field
 
  Set rsWrite = New ADODB.Recordset
  Set rsRead = New ADODB.Recordset
 
  strConn = "Driver={Microsoft ODBC for Oracle};"
  strConn = strConn & "Server=DB11-ORCL.WORLD;"
  strConn = strConn & "UID=xxx;"
  strConn = strConn & "Pwd=yyy;"
  dbConRead.ConnectionString = strConn
  dbConRead.Open
 
  dbConWrite = CurrentProject.Connection
  dbConWrite.Open
 
  'Zieltabelle leeren
  rsWrite.Open "Delete * from " & strTargetTable, dbConWrite, adOpenKeyset, adLockOptimistic
 
  'Quelltabelle öffnen und Pointer auf ersten Datensatz des Recorssets positionieren
  If strWhereClause = "" Then
    rsRead.Open "select * from " & strSourceTable, dbConRead, adOpenStatic, adLockOptimistic
  Else
    rsRead.Open "select * from " & strSourceTable & " where " & strWhereClause, dbConRead, adOpenStatic, adLockOptimistic
  End If
  rsRead.MoveFirst
 
  'Datensätze kopieren
  With rsWrite
    .Open "Select * from " & strTargetTable, dbConWrite, adOpenKeyset, adLockOptimistic
    'Alle Datensätze der Quelltabelle abarbeiten
    Do While Not rsRead.EOF
      .AddNew
      For Each f In rsRead.Fields
        .Fields(f.Name) = rsRead.Fields(f.Name)
      Next f
      .Update
    rsRead.MoveNext
    Loop
  End With
 
  dbConRead.Close
  dbConWrite.Close
  Set dbConRead = Nothing
  Set dbConWrite = Nothing
  Set rsRead = Nothing
  Set rsWrite = Nothing
 
End Sub