| Open.Recordset problems [message #212784] |
Mon, 08 January 2007 05:55  |
rustico64
Messages: 3
Registered: January 2007
Location: Switzerland
|
Junior Member |
|
|
(Sorry for bad english)
Hello Forum Users
We have changed from a IBM DB2 to Oracle DB an now i have problems with a VBA Connect to Oracle. Whit this script we extract some data into a recordset to display in a Text-Message on screen.
But by the position: Set rsTABLE = conDB.OpenRecordset( _
sSQLSelect, dbOpenDynamic)
the script stop whit a error message: (sorry in German) 
"Laufzeitfehler '3146'
ODBC-Aufruf fehlgeschlagen"
What are wrong in the select statement?
Here the Script.
Sub executeSQL1()
Dim sMsg As String
Dim dbWS As Workspace
Dim conDB As Connection
Dim rsTABLE As Recordset
Dim sConnect As String
Dim sSQLSelect As String
Dim Artikelnummer As String
Dim Resultat
If ActiveSheet.Range("T3").Value <> "Jahr" Then
Resultat = MsgBox("Soll die aktuelle Markierung als Artikelnummer übernommen werden?", _
vbYesNo, "Frage Artikelnummer aus Markierung")
If Resultat = vbNo Then
Artikelnummer = InputBox("Bitte Artikelnummer eingeben:")
Else
Artikelnummer = Selection
End If
Else
Artikelnummer = Cells(13, 21).Value
End If
Set dbWS = CreateWorkspace("TempWorkspace", "Excel", "", dbUseODBC)
sConnect = "ODBC;DSN=******;UID=******;PWD=******;DBQ=******;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;" _
& "BTD=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;"
Set conDB = dbWS.OpenConnection("get", dbDriverNoPrompt, dbReadOnly, sConnect)
sSQLSelect = "Select F4101.IMITM, CHAR(F4101.IMDSC1) AS DSC1, CHAR(F4101.IMSTKT) AS STKT," _
& "CHAR(F4101.IMMPST) AS MPST, CHAR(F4101.IMAPSC) AS APSC, CHAR(F4101.IMPRP0) AS PRP0," _
& "CHAR(F4101.IMSRP6) AS SRP6 " _
& "FROM PRODDTA.F4101 WHERE F4101.IMITM =" & "Integer(" & Artikelnummer & ")"
--Here are the Problem 
Set rsTABLE = conDB.OpenRecordset( _
sSQLSelect, dbOpenDynamic)
With rsTABLE
If .RecordCount > 0 Then
sMsg = .Fields(0).Value & " - " & .Fields(1).Value & vbCrLf & vbLf
sMsg = sMsg & "STKT:" & vbTab & .Fields(2).Value & vbCrLf
sMsg = sMsg & "MPST:" & vbTab & .Fields(3).Value & vbCrLf
sMsg = sMsg & "APSC:" & vbTab & .Fields(4).Value & vbCrLf
sMsg = sMsg & "PRP0:" & vbTab & .Fields(5).Value & vbCrLf
sMsg = sMsg & "SRP6:" & vbTab & .Fields(6).Value
MsgBox sMsg, vbOKOnly + vbInformation, "Artikelnummer: " & .Fields(0)
End If
.Close
End With
conDB.Close
End
End Sub
Thank You for view my problem and help to solve.
Regards
Martin
[Updated on: Mon, 08 January 2007 08:06] Report message to a moderator |
|
|
|
| Re: Open.Recordset problems [message #212791 is a reply to message #212784] |
Mon, 08 January 2007 06:46  |
rustico64
Messages: 3
Registered: January 2007
Location: Switzerland
|
Junior Member |
|
|
Hello again
problem is solved by a other Forum (www.Herber.de it is in German and normaly just for Excel Problems), here is the Solve.
So Simple but usefull.
Set all CHAR() to TO_CHAR()
sSQLSelect = "Select F4101.IMITM, TO_CHAR(F4101.IMDSC1) AS DSC1, TO_CHAR(F4101.IMSTKT) AS STKT," _
& "TO_CHAR(F4101.IMMPST) AS MPST, TO_CHAR(F4101.IMAPSC) AS APSC, TO_CHAR(F4101.IMPRP0) AS PRP0," _
& "TO_CHAR(F4101.IMSRP6) AS SRP6 " _
& "FROM PRODDTA.F4101 WHERE F4101.IMITM =" & Artikelnummer
Hope someone can use this solve
Regards
Martin
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
