Hallo Leute!
ICh versuche eine Connection aus einem VBA Makro (innerhalb einer Word 2007 Datei) mit einem ADO Recordset auf zu bauen. Allerdings scheitere ich kläglich und bekomme immer die FehlerMeldung 446 Objekt unterstützt die benannten ARgumente nicht. Wo ist der Fehler ?!?
hier der Quellcode:
Sub Test()
...
...
...
'Variablen für db connection with ADO
Dim ADO_db_connect As New ADODB.Connection
Dim ADO_db_command As New ADODB.Command
Dim ADO_db_recordset As New ADODB.Recordset
Dim recordset_affectedRows As Long
Dim str_connection As String
Dim strProvider As String
Dim strDataSource As String
Dim records_affected As Long
'sql statements prepare
Dim sql_1 As String ' Verzeichnis der Rückläufe
sql_1 = "SELECT R.Nr, R.REF, R.Land, R.Ort, R.ANR & ' ' & R.AP as Ansprechpartner, R.F, R.Antwort, R.Memo, R.G1, R.G2, R.G as 'A' FROM R WHERE R.[TN-Nr] = ' + TN_Nr + ' And R.[ZU-Nr] = ' + ZU_Nr + ' ORDER BY R.Nr Asc"
'OLE DB Treiber
'str_connection = "Provider='Microsoft.Jet.OLEDB.4.0';' &_ 'Data source='C:\KZU_Projekt\KZU_DB_neu.accdb'"
strProvider = "Provider=Microsoft.Jet.OLEDB.4.0; "
strDataSource = "Data source=C:\KZU_Projekt\KZU_DB_neu.accdb"
'Microsoft MS Access 2007 Treiber
'strProvider = "Provider='Microsoft.ACE.OLEDB.12.0'; "
'strDataSource = "Data source='C:\KZU_Projekt\KZU_DB_neu.accdb'"
str_connection = strProvider + strDataSource
On Error GoTo AdoError ' Full Error Handling which traverses Connection object
On Error GoTo AdoErrorLite ' Don't assume that we have a connection object
'Lösung 1
ADO_db_connect.ActiveConnection = str_connection 'hier bricht er ab und geht in den Error Handler
ADO_db_connect.Open
ADO_db_command.CommandText = sql_1
ADO_db_command.Prepared = True
Set ADO_db_recordset = ADO_db_command.Execute(records_affected, , Options = adCmdTable)
'Lösung 2
'ADO_db_connect.ActiveConnection = str_connection
'ADO_db_command.CommandText = sql_1
'ADO_db_command.Prepared = True
'ADO_db_recordset.Open ADO_db_command.CommandText, str_connection
ADO_db_recordset.Close
ADO_db_connect.Close
ADO_db_connect.ActiveConnection = ""
'Prozess Word killen fehlt nocht !!!
word_apl.Application.ActiveDocument.Close
'leeren der DB Objects connection,command & recordset
Done:
Set ADO_db_recordset = Nothing
Set ADO_db_command = Nothing
Set ADO_db_connect = Nothing
ADO_db_connect.Errors.Clear
'Prozess Word killen fehlt nocht !!!
Set word_apl = Nothing
Exit Sub
'
' Error Händler
'
' ADO DB Connection (=AdoError)& Ado DB Recordset (=AdoErrorLite)
'
' Error Handling Variables
Dim errLoop As Error
Dim strTmp As String
Dim j As Integer
Dim Errs1 As Errors
'
' Connection Object Methods
AdoError:
j = 1
On Error Resume Next
' Enumerate Errors collection and display properties of
' each Error object (if Errors Collection is filled out)
Set Errs1 = ADO_db_connect.Errors
For Each errLoop In Errs1
With errLoop
strTmp = strTmp & vbCrLf & "ADO Error # " & j & ":"
strTmp = strTmp & vbCrLf & " ADO Error # " & .Number
strTmp = strTmp & vbCrLf & " Description " & .Description
strTmp = strTmp & vbCrLf & " Source " & .Source
j = j + 1
End With
Next
' Recordset Object Methods
AdoErrorLite:
' Get VB Error Object's information
strTmp = strTmp & vbCrLf & "VB Error # " & Str(Err.Number)
strTmp = strTmp & vbCrLf & " Generated by " & Err.Source
strTmp = strTmp & vbCrLf & " Description " & Err.Description
MsgBox strTmp
' Clean up gracefully without risking infinite loop in error handler
On Error GoTo 0
GoTo Done
End Sub
Jede Hilfe ist echt hilfreich ... ich habe in anderen Foren und in der Microsoft Hilfe den Provider welchen ich im Moment gerade auskommentiert habe geraten bekommen. Mit dem bekome ich aber genau den selben Fehler...
Vielen Dank im Voraus!
MfG
HelD007 |