Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Need help in configuring an ODBC data adapter

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Need help in configuring an ODBC data adapter
 Login/Join
 
Francis Mariani
Expert
posted
I have several Access databases configured in Windows ODBC Data Source Adminstrator.

I can successfully access these databases via a SQL Client (AQT). Now I would like to set up ODBC data adapters for these databases. I'm having trouble with the connection settings.

What is "Datasource"? According to the Help documentation: "ODBC data source name (DSN). This is no default data source name. You must enter a value." I have entered the datasource name as I configured in ODBC Data Source Adminstrator (as a "User DSN"), when I Test the connection, I get this error: 
(FOC1394) CONNECT FAILURE 
(FOC1400) SQLCODE IS 1 (HEX: 00000001) 
: [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and 
: no default driver specified 
(FOC1405) SQL PREPARE ERROR.


For another ODBC connection that I set up as a "System DSN"), I get this error:

(FOC1394) CONNECT FAILURE 
(FOC1400) SQLCODE IS -1023 (HEX: FFFFFC01) 
: [S1009] [Microsoft][ODBC Microsoft Access Driver] '(unknown)' is not a v
: alid path.  Make sure that the path name is spelled correctly and that y
: ou are connected to the server on which the file resides. [IM006] [Micro
: soft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed 
(FOC1405) SQL PREPARE ERROR.

The Connection Name and Datasource are filled in, so I don't know where the "(unknown)" comes from.

This message has been edited. Last edited by: Kerry,


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
dhagen
Virtuoso
posted Hide Post
Test your data source access via the get external data option in excel. This should allow you to verify that the ODBC system dsn's are configured correctly.

If it doesn't work, then you can assume that the system dsn's are not set up properly. If it does, then open a case with css.


"There is no limit to what you can achieve ... if you don’t care who gets the credit." Roger Abbott
 
Posts: 1102 | Location: Toronto, Ontario | Registered: May 26, 2004Report This Post
Francis Mariani
Expert
posted Hide Post
dhagen, I can successfully connect to these ODBC dsns via a SQL client. The same dsns cannot be connected to on a local WebFOCUS environment.

Thanks,


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
fatboyjim
Platinum Member
posted Hide Post
Hi,

Have you tried opening up the ODBC configurationa and Click the on the 'Select' option to locate your Access database (.mdb) file?

Best Regards,

Jimmy Pang


DEV: WF 7.6.10
TEST: WF 7.6.10
PROD: WF 7.6.10
MRE: WF 7.6.4
OS/Platform: Windows
Dev Studio: WF 7.7
Output: HTML, EXCEL, PDF, GRAPH, LOTUS, CSV
 
Posts: 117 | Location: Toronto, Ontario, Canada | Registered: February 29, 2008Report This Post
Francis Mariani
Expert
posted Hide Post
I set up six ODBC connections. They all work correctly when connecting via a SQL client.

The Access databases are on a network drive.

I copied one db to my local drive, set up an ODBC connection, tested it with the SQL client and then successfully added a WF ODBC data adapter.

It seems WebFOCUS cannot access these ODBC connections when the databases are located on a network drive.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
dhagen
Virtuoso
posted Hide Post
Oh! You will have to set up your system DSNs as a UNC type connection (\\sharename\dir\filename.mdb). Also, ensure that the userid that the WF server is running as has read access to the share names.


"There is no limit to what you can achieve ... if you don’t care who gets the credit." Roger Abbott
 
Posts: 1102 | Location: Toronto, Ontario | Registered: May 26, 2004Report This Post
Francis Mariani
Expert
posted Hide Post
dunno where to do that. I took a look at C:\Windows\ODBC.INI and the connection settings in the file do not have the physical location specified. e.g.:

...
airbext200609=Microsoft Access Driver (*.mdb) (32 bit)
...
[airbext200609]
Driver32=C:\WINDOWS\system32\odbcjt32.dll
...


The physical file is located at G:\BASEL\01 Monthly Tasks\BCS-NCR\Commercial\BCS BSLOPF data\bsl_bcs_comm_airb_extract_f_OPF_200609.mdb.

Where, for the ODBC connection would this be specified?

Thanks a bunch.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
dhagen
Virtuoso
posted Hide Post
set up a share first. Then, when you setup your odbc system dsn, do not click the browse button, just type the unc reference directly in the input box. I've done this before, so I can verify that it does work ... I just don't know if it is officially supported by microsoft!


"There is no limit to what you can achieve ... if you don’t care who gets the credit." Roger Abbott
 
Posts: 1102 | Location: Toronto, Ontario | Registered: May 26, 2004Report This Post
Francis Mariani
Expert
posted Hide Post
Confused The UNC reference, \\Officebmtgrp2.office.adroot.bmogc.net\irr group\BASEL\01 Monthly Tasks\BCS-NCR\Commercial\BCS BSLOPF data\bsl_bcs_comm_airb_extract_f_OPF_200610.mdb does not work Confused Too long?


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Tony A
Expert
posted Hide Post
Francis,

You state above that they are User DSNs? Is that correct? As Darryl suggests, they should be System DSNs.

T


In FOCUS
since 1986		WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Francis Mariani
Expert
posted Hide Post
I did re-add them as System DSNs. When I paste the UNC reference to the file, it is accepted, but when I re-edit the ODBC connection, the reference is no longer in the Database Name field of the Select Database dialogue box.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Danny-SRL
Virtuoso
posted Hide Post
Francis,

1. Go into ODBC Data Source Administrator: Control Panel->Admin Tools->ODBC
2. Click System DSN
3. Click Add
4. Dbl-Click either:
- Driver do Microsoft Access (.mdb)
- Microsoft Access Driver (.accdb)
5. Click Select
6. Choose your Access DB
7. Give it a name (e.g. Francis)
8. Click OK. Click OK.
9. Create a connection to Francis in WebFocus console: ODBC adapter->Add connection->Enter Connection Name and Datasource name (Francis)
10. Configure

This should do it.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Francis Mariani
Expert
posted Hide Post
Did all that. The WF connection does not work when the Access DB is on a shared drive. I copied the files to the local drive and had no trouble connecting. It's not a question of permissions, because I have update access to the DBs.

Thanks.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Tony A
Expert
posted Hide Post
Not wishing to state the blindingly obvious (and I'm sure you have checked) but the network share is attached in the profile of the ID used to run the reporting server?

As I say, I'm sure you know but when others read this is future searches (providing they search of course Wink) then it's just another snippet to be considered.

Have a great weekend Francis.

T


In FOCUS
since 1986		WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Francis Mariani
Expert
posted Hide Post
Tony,

Yes I did check that too. To temporarily solve the problem, I copied the Access database files to the server. I successfully created a FOCUS database from these Access databases for future use.

Cheers,


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
CLH
Platinum Member
posted Hide Post
Hi, for others who may search this we found that the Current Microsoft ODBC driver does not work with UNC mappings.

We had 2 solutions from tech support. The first was to map a drive to the remote server and then use that drive to establish the connection.

The other solution was to create a dummy databse on a drive local to the reporting server. Create the connection to that database. Then modify the registry and change that ODBC connection in the registry so that the DBQ key uses the UNC name. I would not recomend this option to anyone unfamilar with the windows registry.

The only other thing we ran into was that the service account that is running the webfocus reporting server service will need access to the share where the database actually is.


Webfocus 8.0.7 on Windows
 
Posts: 176 | Location: Ohio | Registered: October 26, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Need help in configuring an ODBC data adapter

Copyright © 1996-2020 Information Builders