Access als DB (ADO) Parameter Fehler?

Forumthread: Access als DB (ADO) Parameter Fehler?

03.02.2019 20:51:17

Martin

Hallo,
ich bastel ein wenig an eine Möglichkeit Access als Datenbank zu nutzen und möchte die Ein- und Ausgaben über Excel UserForm machen weil das Optisch ansprechend ist. Ich habe eine Verbindung hinbekommen aber bekomme nun 2 Fehler.
1. Fehler: Für mindestens einen erforderlichen Parameter wurde kein Wert angegeben.
2. Fehler: Der Vorgang ist für ein geschlossenes Objekt nicht zugelassen.
Ich habe ein UserForm wo ich den Code bereits verwende dort bekomme ich den Fehler Fehler:
nun zu meinem Code.


Private Sub CommandButton_Login_OK_Click()
Dim oAdoConnection As New ADODB.Connection
Dim oAdoRecordset As New ADODB.Recordset
Dim sAdoConnectString As String, sPfad As String
Dim sQuery As String
Dim benutzer As String
Dim pw As String
Dim tabelle As String
benutzer = TextBox_user.Value
pw = TextBox_pw.Value
tabelle = "Mitarbeiter"
sPfad = "Database1.accdb"
On Error GoTo Fehler
sAdoConnectString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & sPfad
oAdoConnection.Open sAdoConnectString
MsgBox sQuery = "Select * From " & tabelle & " WHERE E-Nummer = " & benutzer & "
AND Passwort = " & pw
'sQuery = "Select * From " & tabelle & " WHERE E-Nummer = " & benutzer & " AND
Passwort = " & pw
sQuery = "Select * From " & tabelle & " WHERE E-Nummer = test AND Passwort =
test"
With oAdoRecordset
.Source = sQuery
.ActiveConnection = oAdoConnection
.Open
Worksheets(2).Cells(2, 1).Value = .Fields(1)
Worksheets(2).Cells(2, 2).Value = .Fields(2)
Worksheets(2).Cells(2, 3).Value = .Fields(3)
Worksheets(2).Cells(2, 4).Value = .Fields(5)
End With
Aufraeumen:
On Error Resume Next
oAdoRecordset.Close
oAdoConnection.Close
Set oAdoRecordset = Nothing
Set oAdoConnection = Nothing
Fehler:
MsgBox "Fehler: " & Err.Description
Resume Aufraeumen
End Sub

Ich hab jetzt schon ein paarmal drübergeschaut und alle Variablen auch mal direkt eingegeben aber ich finde nix.

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Access als DB (ADO) Parameter Fehler?

03.02.2019 21:46:10

Luschi

Hallo Martin,
in dem Code sind einige Fallstricke drin:
- sQuery = "Select * From " & tabelle & " WHERE [E-Nummer] = 'test' AND Passwort = 'test';"
oder
- 'sQuery = "Select * From " & tabelle & " WHERE [E-Nummer] = '" & benutzer & "' AND
Passwort = '" & pw & "';"
da "test" ein Text ist bzw. tabelle und Passwort Textwerte enthalten.
Warum gibt es eine Variable 'sPfad', wenn der Inhalt nur der Dateiname ist, dann nenne sie doch 'sDatei' oder schreibe den Pfad mit dazu, was immer von Vorteil ist.
Außerdem würde ich den Verbindungsaufbau auf 'late binding' umstellen und den Vba-Verweis rausschmeißen.
Über diese Art der Definition:
Dim oAdoConnection As New ADODB.Connection
hört man immer nur Klagen und sollte ihn so auflösen:
Dim oAdoConnection As ADODB.Connection
Set oAdoConnection = New ADODB.Connection
Aber wie gesagt, daß ist 'early binding' - wie 'late binding' aussieht, verrät Dir Tante Google.
Das gleiche gilt dann auch für
Dim oAdoRecordset As New ADODB.Recordset
Gruß von Luschi
aus klein-Paris
PS. das [E-Nummer] in eckigen Klammer stehen muß!!! liegt am enthaltenen Sonderzeichen '-' im Feldnamen.

AW: Access als DB (ADO) Parameter Fehler?

04.02.2019 01:40:52

Martin

Hallo Luschi,
vielen Dank für deine Hilfe. Das mit den [] war mit nicht bekannt. Ich suche mir die meisten _ Infos aus dem netz und versuche es zu verstehen. Deshalb ist mir der unterschied mit dem


Dim oAdoConnection As New ADODB.Connection

und dem



Dim oAdoConnection As ADODB.Connection

Set oAdoConnection = New ADODB.Connection

nicht ganz klar aber ich habe es geändert.

Warum gibt es eine Variable 'sPfad', wenn der Inhalt nur der Dateiname ist, dann nenne sie doch 'sDatei' oder schreibe den Pfad mit dazu, was immer von Vorteil ist.

zurzeit habe ich die Dateien (Excel & Access) im gleichen Ordner aber später wird Access in einem anderen Ordner liegen dann ist das leichter zu ändern.

AW: Access als DB (ADO) Parameter Fehler?

04.02.2019 08:08:49

Luschi

Hallo Martin,
wenn Excel- & Accessdatei im gleichen Verzeichnis sich befinden, dann schreibe es so:
sPfad = Thisworkbook.Path & "\" & "Database1.accdb"
Bevor ich solche Abfragen als String-Variable zusammensetze, erstelle ich sie zuerst als Abfrage in Access direkt, teste sie und schaue mir dann den SQL-Code der Access-Abfrage an.
Wenn Du das sorum machst, hätte Dir Access die []-Klammern automatisch gesetzt. Gerade für nicht sooft damit beschäftigte Anwender ist das eine gute Übung und zeigt zumindest, wie wie der String am Ende aussehen muß.
Das am Ende von mir eingefügte ';' ist SQL-Standard, gehört dazu, Access ist da aber etwas toleranter - es geht auch ohne dem Zeichen.
Schreibe doch mal, ob der Zugriff auf Access geklappt hat. und wie Du die Werte in Excel einfügst -
seit Excel 2003 gibt es die Recordset-Methode 'CopyFromRecordset'.
Gruß von Luschi
aus klein-Paris
PS: der Umstieg von von 'early binding' auf 'late binding' hat dann einen wichtigen Vorteil, wenn man damit rechnen muß, daß der Vba-Code auf PC's mit unterschiedlichen Excel-Versionen laufen muß.
Da stottert Excel manchmal, da ja der direkt gesetzte Vba-Verweis automatisch geändert werden muß.
Hat aber auch den Nachteil, daß es keine Hilfe beim Schreiben des Codes durch die 'IntelliSense' gibt - man muß dann wissen, was man tut!

AW: Access als DB (ADO) Parameter Fehler?

10.02.2019 18:01:59

Martin

Hallo Lutschi,
Zugriff auf Access hat soweit geklappt. Da ich aber mehrere UserForm´s nutze habe ich die BD Verbindung in eine eigene Funktion gesetzt und übertrage den SQL String als Variable sowie Ausgabe Informationen.
Zum ansprechen der Funktion:

sheet = 2
spalte = 1
reihe = 4
sql = "Select ID, [E-Nummer], Vorname, Nachname, Rechte From " & Tabelle & " WHERE [E-Nummer] =  _
'" & Benutzer & "' AND Passwort = '" & pw & "'"
ergebnis = DB.Connect(sql, sheet, spalte, reihe)

Die Funktion selber:

Option Explicit
Public Function Connect(sql As String, sheet As Long, spalte As Long, reihe As Long)
Dim oAdoConnection As ADODB.Connection
Set oAdoConnection = New ADODB.Connection
Dim oAdoRecordset As ADODB.Recordset
Set oAdoRecordset = New ADODB.Recordset
Dim sAdoConnectString As String, sPfad As String
Dim sQuery As String
sPfad = Worksheets(3).Range("B2").Value
On Error GoTo Fehler
sAdoConnectString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & sPfad
oAdoConnection.Open sAdoConnectString
sQuery = sql
With oAdoRecordset
.Source = sQuery
.ActiveConnection = oAdoConnection
.Open
Worksheets(sheet).Cells(reihe, spalte).CopyFromRecordset oAdoRecordset
End With
Aufraeumen:
On Error Resume Next
oAdoRecordset.Close
oAdoConnection.Close
Set oAdoRecordset = Nothing
Set oAdoConnection = Nothing
Exit Function
Fehler:
MsgBox "Fehler: " & Err.Description
Resume Aufraeumen
End Function

Ich hab versucht mit "CopyFromRecordset" ein Array zu füllen aber leider kein erfolg gehabt. so nutze ich Tabellen als "Speicher" da Excel bis auf die UserForm sowieso ausgeblendet ist.

Infobox / Tutorial

Access als DB (ADO) Parameter Fehler beheben

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Um Access als Datenbank mit ADO in Excel zu verwenden, kannst du folgende Schritte befolgen:

Verbindung zur Access-Datenbank herstellen:

Definiere deinen Verbindungsstring, um die Access-Datenbank zu erreichen.

Beispiel:

Dim oAdoConnection As ADODB.Connection
Set oAdoConnection = New ADODB.Connection
Dim sAdoConnectString As String
sAdoConnectString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=DeinPfad\Database1.accdb;"
oAdoConnection.Open sAdoConnectString

SQL-Abfrage erstellen:
- Stelle sicher, dass du die richtigen Parameter in deiner SQL-Abfrage verwendest. Achte dabei auf die Verwendung von Hochkommas bei Textwerten.
- Beispiel:
```
Dim sQuery As String
sQuery = "SELECT * FROM Tabelle WHERE [E-Nummer] = '" & benutzer & "' AND Passwort = '" & pw & "';"
```

Recordset öffnen:

Verwende ein Recordset, um die Ergebnisse der Abfrage zu speichern.

Beispiel:

Dim oAdoRecordset As ADODB.Recordset
Set oAdoRecordset = New ADODB.Recordset
oAdoRecordset.Open sQuery, oAdoConnection

Daten in Excel übertragen:
- Nutze die CopyFromRecordset Methode, um die Daten in Excel zu übertragen.
- Beispiel:
```
Worksheets(1).Cells(1, 1).CopyFromRecordset oAdoRecordset
```

Aufräumen:

Schließe die Verbindungen und setze die Objekte auf Nothing.

Beispiel:

oAdoRecordset.Close
oAdoConnection.Close
Set oAdoRecordset = Nothing
Set oAdoConnection = Nothing

Häufige Fehler und Lösungen

Fehler: "Für mindestens einen erforderlichen Parameter wurde kein Wert angegeben."
- Überprüfe, ob alle verwendeten Variablen korrekt gesetzt sind und ob du die SQL-Abfrage richtig formatiert hast.
Fehler: "Der Vorgang ist für ein geschlossenes Objekt nicht zugelassen."
- Stelle sicher, dass das Recordset geöffnet wurde, bevor du versuchst, auf dessen Daten zuzugreifen. Überprüfe auch, ob die Verbindung zur Datenbank korrekt hergestellt wurde.

Alternative Methoden

Wenn du Schwierigkeiten mit der ADO-Implementierung hast, könntest du auch die DAO (Data Access Objects) in Betracht ziehen, um auf Access-Datenbanken zuzugreifen. Diese Methode kann manchmal einfacher sein, insbesondere wenn du bereits mit Access vertraut bist.

Praktische Beispiele

Hier ist ein praktisches Beispiel, wie du die oben genannten Schritte umsetzen kannst:

Sub ZugriffAufAccess()
    Dim oAdoConnection As ADODB.Connection
    Dim oAdoRecordset As ADODB.Recordset
    Dim sAdoConnectString As String
    Dim sQuery As String
    Dim benutzer As String
    Dim pw As String

    benutzer = "test_user"
    pw = "test_pass"

    Set oAdoConnection = New ADODB.Connection
    sAdoConnectString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=DeinPfad\Database1.accdb;"
    oAdoConnection.Open sAdoConnectString

    sQuery = "SELECT * FROM Mitarbeiter WHERE [E-Nummer] = '" & benutzer & "' AND Passwort = '" & pw & "';"

    Set oAdoRecordset = New ADODB.Recordset
    oAdoRecordset.Open sQuery, oAdoConnection

    If Not oAdoRecordset.EOF Then
        Worksheets(1).Cells(1, 1).CopyFromRecordset oAdoRecordset
    End If

    oAdoRecordset.Close
    oAdoConnection.Close
    Set oAdoRecordset = Nothing
    Set oAdoConnection = Nothing
End Sub

Tipps für Profis

Nutze late binding für ADO-Objekte, um Komplikationen mit Verweisen zu vermeiden. Dies ist besonders nützlich, wenn verschiedene Excel-Versionen verwendet werden.
Teste deine SQL-Abfragen direkt in Access, bevor du sie in deinem VBA-Code verwendest. So kannst du sicherstellen, dass sie korrekt sind und keine Syntaxfehler enthalten.
Achte darauf, dass du immer die eckigen Klammern [ ] um Feldnamen verwendest, die Sonderzeichen enthalten, um SQL-Fehler zu vermeiden.

FAQ: Häufige Fragen

1. Frage: Was bedeutet "der Vorgang ist für ein geschlossenes Objekt nicht zugelassen"? Antwort: Dieser Fehler tritt auf, wenn du versuchst, auf ein Recordset zuzugreifen, das nicht geöffnet ist. Stelle sicher, dass dein Recordset korrekt geöffnet wurde.

2. Frage: Wie kann ich Parameter in meiner SQL-Abfrage verwenden? Antwort: Du kannst Parameter direkt in deiner SQL-Abfrage einfügen, indem du sie in einfache Anführungszeichen setzt, z.B. WHERE [E-Nummer] = '" & benutzer & "'".

Beliebteste Forumthreads (12 Monate)

Access als DB (ADO) Parameter Fehler?

Forumthread: Access als DB (ADO) Parameter Fehler?

4 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Access als DB (ADO) Parameter Fehler beheben

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread