How to connect JDE database by using ODBC in Visual Basic?

[tee]

Hi,

How to connect JDE database by using ODBC in Visual Basic.
I tried the following connection.
Set db = OpenDatabase("Business Data - TEST", _
' dbDriverNoPrompt, True, _
"ODBC;DATABASE=TESTDTA;UID=JDESMS;PWD=JDESMS;DSN=Business Data - TEST")

but i hit error "ODBC-Call Failed"

In iSeries Access for Windows ODBC Setup, Data source name :Business Data - TEST, System:S65F996B, SQL default Library : TESTDTA, Package Library :TESTDTA, Driver: iSeries Access ODBC Driver


Please give some guidance.

Thanks

tee

 

[tee]

Currently, I am using ERP8.0.

 

[coughter2000]

I would connect using OLE DB. Use a DSNless connection. It retrieves data much faster than the fetch process of ODBC. If you are using SQLServer this will work....

Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset
Dim sConnString As String



'Data Source = Server Name
'Initial Catalog = Database
'Use your own user names and password

sConnString = "Provider=SQLOLEDB.1;User ID=USERNAME;password=PASSWORD;Initial Catalog=DATABASENAME;Data Source = SERVERNAME;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096"
'Open your connection
conn.Open sConnString
Set cmd.ActiveConnection = conn

'Then fetch your recordsets.....
cmd.CommandText = "SELECT * FROM F0006"
cmd.CommandType = adCmdText
Set rs = cmd.execute


Then you can use the recordsets however you wish....


Make sure to close everything once you are done....

Set rs = Nothing
Set cmd = Nothing
conn.Close
Set conn = Nothing

 

[coughter2000]

if you're using as400....the connection string is


conn.Open "Provider=IBMDA400;" & _
"Data source=myAS400;" & _
"User Id=myUsername;" & _
"Password=myPassword"

 

[tee]

hi,

I hit an run-time error '-2147467259 (80004005)' Unspecified error when I try to opent he connection.

My connection string is as below

sConnString = "Provider=IBMDA400;" & _
"Data source=Business Data - TEST;" & _
"User Id=JDESMS;" & _
"Password=jdesms".

I am using AS 400, is the provider must be "IBMA400". The Data Source, is it refer to the Data Source at iSeries Access for Windows ODBC Setup.

Attached is the print screen of iSeries Access for Windows ODBC Setup. Please advise me which field should I use for Data Source, so that I can connect to JDE DB.

Thanks

 

[Wkunneke]

JD Edwards has a habit of setting the DSN to use System Naming Conventions (*SYS) by default. Look at the second tabbed page Server) of the Client Access Express ODBC Setup from ODBC Administrator for the DSN in question. The Naming Convention should be set to SQL (not SYS) in order for most ODBC connections to work. Hope this helps.

 

[tee]

hi,

I have changed the naming convention to SQL. It still giving me the same error message. I suspected my connection string got problem. Can please help me on it

Thanks

 

[newguy]

Hi, in the release E810, it looks like they are going towards OLE DB, and doesn't have a DSN connection, as mentioned by coughter2000. I'm trying to do the same code posted here, but in C++. Can somebody please help me with the same code, but in C++? thanks so much!

--Jim
E810
SQL Server

 

[tee]

I had connected to JDE Database successfully.

The following is my coding.

Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set cmd = New Command

conn.CursorLocation = adUseClient
conn.ConnectionString = "DSN=Business Data - CRP;UID=JDESMS;PWD=jdesms"
conn.Open

rs.CursorType = adOpenStatic

Set cmd.ActiveConnection = conn

cmd.CommandText = "Select * from F41021 "
cmd.CommandType = adCmdText
Set rs = cmd.Execute

Set rs = Nothing
Set cmd = Nothing
conn.Close
Set conn = Nothing



Cursor Location and Cursor type must included so that the record count won't be -1.

hope can help u all

thanks alot for your help