Hallo Experte,
ich möchte per Access-VBA daten aus einer Oracle-DB in eine Access-DB laden. Die Prozedur sieht wie folgt aus:
Sub Refresh-All_Tables()
Dim dbCon As New ADODB.Connection
Dim db As Database
Dim strConn As String
Dim tblSource as String
Dim tblTarget 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()
tblSource = "xxx"
tblTarget = "yyy"
'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.
Auch eine Warteschleife zwischen dbcon.Open und dem Select bringt nichts. Kann mir jemand mit einem Tipp helfen? Vorab schon mal vielen Dank - Dirk
Hallo,
ich habe das Problem noch nie gesehen. Ich poste Dir hier kurz wie ich die Verbindung aufbaue (zu Oracle XE) mit OLE
Public conn As ADODB.Connection
Private Function OracleVerbindungAufbauen(ByVal strUsername, ByVal strPassword) As Boolean
On Error GoTo Fehlermeldung
Dim strDBsystem As String
strDBsystem = "OraOLEDB.Oracle"
Set conn = New ADODB.Connection
With conn
'.IsolationLevel = adXactReadCommitted
'.IsolationLevel = adXactIsolated
.IsolationLevel = adXactSerializable
'.Mode = adModeShareExclusive
.Mode = adModeShareDenyWrite
'.Mode = adModeShareDenyRead
.CursorLocation = adUseServer
End With
conn.Open "Provider=" & strDBsystem & ";User ID=" & strUsername & ";Password=" & strPassword & ";"
'MsgBox conn.CommandTimeout
OracleVerbindungAufbauen = True
Exit Function
Fehlermeldung:
OracleVerbindungAufbauen = False
MsgBox "Error# " & conn.Errors(0).NativeError & ": " & conn.Errors(0).Description & " ", vbCritical, "Datenverbindung aufbauen "
End Function
Hallo,
m. E. macht der Code nicht das, was Du Dir versprichst.
--Es wird eine (extra) ADODB-Verbindung auf die Oracle-DB hergestellt.
--es wird ein Verweis auf die Aktuell geöffnete Access-DB hergestellt (--> Currentdb)
-- es werden zwei Stringvaribalen gesetzt.
-- es wird eine Tabelle mit Namen "tblTarget" (nicht "yyy") in der akt. DB geleert
-- es werden in Tabelle "tblTarget" (nicht "yyy") die Datensätze aus Tabelle "tblSource" (nicht "xxx") in der akt. DB eingefügt.
-- die (extra) ADODB-Connection wird geschlossen
Insofern wird die extra-Connection gar nicht benutzt und hat keinen Einfluß auf die SQL-Statements. Vermutlich hast Du die Tabellen per ODBC-Treiber schon eingebunden/verknüpft und dabei das PW nicht angegeben. Die Stringvariablen bleiben auch außen vor.
Danke an Wurliwurm und Franz für die superschnellen Antworten. Der Stand ist nun wie folgt:
1.Ich habe den Wurliwurm-Code bis auf eine Änderung nachgebaut. Die Änderung: Anstelle von Provider habe ich Driver und Server verwendet, da ich nicht weiß, was ich als Provider eingeben muss. Ergebnis: Das Problem besteht weiter
2. Mit den Stringvariablen hat Franz Recht. Beim Codereduzieren habe ich Mist gemacht >:(. Im Original arbeite ich mit Variablen.
3. Franz' Kommentar mit der Extra-ADODB-Verbindung habe ich noch nicht verstanden. Wo kann ich denn die andere ADODB-Verbindung hergestellt haben? Das ist mir überhaupt nicht klar. Aber jetzt kommt's: Wenn ich dbcon.open und dbcon.close weglasse, dann hat das keine Auswirkung. Das Problem besteht unverändert. Das löst noch nicht mein Problem, aber der Code-Schnipsel ist wirklich überflüssig. Aber wo baue ich die Oracle-Verbindung ohne Passwort auf? ???
Gruß - Dirk
Hier wird die Orcale-Verbindung hergestellt!!!!
Ich habe die Oracle Tabellen/Views als Verküpfungen in Access eingebunden. Dadurch besteht die Oracle-Verbindung. Richtig ist auch, dass hier kein Passwort hinterlegt ist. Hat jemand noch 'ne Idee, wie man hier das Passwort hinterlegen oder irgendwie als Parameter mit übergeben kann?
Danke und Gruß aus dem sonnigen Spessart - Dirk
Hallo Dirk,
du mußt genau zwischen DAO und ADO und zwischen ODBC und OLE unterscheiden.
db.execute ist ein DAO-Befehl der nur mit Access-Datenbanken geht. Wie oben beschrieben machst Du zwar eine Verbindung auf aber nützt sie gar nicht, sondern machst einen lokalen DAO-Befehl.
Franz hat vermutet, daß du mit ODBC eine Tabelle verknüpft hast, aber mein Aufbau (was OLE ist, schlanker und ohne Overhead) gibt das Passwort mit.
Schau dir mal die OBDC-Verbindungen an, ob da welche konfiguriert sind. Und frag den Datenbankadministrator nach dem Provider (hast du überhaupt ein Oracle-Frontend ("Listener") auf deinem Rechner?). Es ist schwer eine Ferndiagnose zu stellen.
Hallo,
versuch es mal nach dieser Art:
Sub Refresh-All_Tables()
Dim dbCon As New ADODB.Connection
Dim strConn As String
Dim tblSource as String
Dim tblTarget 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
tblSource = "xxx"
tblTarget = "yyy"
'Zieltabelle leeren
dbCon.Execute "Delete * from [" & tblTarget & "]"
'Zieltabelle füllen
dbCon.Execute "INSERT INTO [" & tblTarget & "] " & _
"SELECT [" & tblSource & "].* " & _
"FROM [" & tblSource & "];"
'Datenverbindung zur Oracle-Datenbank schließen
dbCon.Close
End Sub
PS: wenn die Tabellen per ODBC verlinkt werden, kannst Du dabei ein PW mit angeben.
Dann wird dieser Code vermutlich auch laufen:
Sub Refresh-All_Tables()
Dim strConn As String
Dim tblSource as String
Dim tblTarget as String
Dim db as DAO.Database
set db=Currentdb
tblSource = "xxx"
tblTarget = "yyy"
'Zieltabelle leeren
db.Execute "Delete * from [" & tblTarget & "]"
'Zieltabelle füllen
db.Execute "INSERT INTO [" & tblTarget & "] " & _
"SELECT [" & tblSource & "].* " & _
"FROM [" & tblSource & "];"
End Sub
Nochmals Dank an Franz und Wurliwurm. Ihr habt mir die richtigen Anregungen gegeben. Ich hatte mit ADO und DAO grandios gepfuscht (in Unkenntnis!). Meine Lösung sieht jetzt wie folgt aus:
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
Alles läuft einwandfrei. Nach dem Leeren der Zieltabelle werden die Records Feld für Feld in die Zieltabelle geschrieben. Ich könnte mir vorstellen, dass die Prozedur deutlich schneller wird, wenn man statt dieser Konstruktion ein INSERT INTO verwendet. Doch leider habe ich Probleme mit der Syntax. Vielleicht habt Ihr ja noch eine Idee. Ansonsten lasse ich das so. Es läuft ja.
Nochmals Danke und Gruß
Dirk