Home » SQL & PL/SQL » SQL & PL/SQL » Open.Recordset problems
Open.Recordset problems [message #212784] Mon, 08 January 2007 05:55 Go to next message
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) Embarassed
"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 Mad
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 Go to previous message
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
Previous Topic: my sql plus Abnormallly shutdown
Next Topic: Different explain plan in a similar DBs
Goto Forum:
  


Current Time: Sat Dec 03 06:00:09 CST 2016

Total time taken to generate the page: 0.08202 seconds