Adding an ODBC Data Source to Laserfiche Forms

asked on December 19, 2023

Greetings,

I am trying to create new ODBC Data Source connection in Laserfiche Forms.

I already created the 64 bit ODBC Data Source. The source of the data is located in an Excel File saved within the Laserfiche Server.

Once I try to test the connection to the ODBC Data Source from Laserfiche forms, I receive the following error:

An error occurred while testing the data source connection. See the Windows Event Log for details. [LFF2400-DataSourceConnectionError]

Here are the details of the error:

An error occurred while testing the data source connection. See the Windows Event Log for details. [LFF2400-DataSourceConnectionError]

Details:

URL: /Forms/api/datasource/test

Error: DataSourceConnectionError

Date: 2023-12-19 10:57:05 AM (Eastern Standard Time)

HTTP Status Code: 400

Business Process ID: 0

User: ******

IP: ******

Browser: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:120.0) Gecko/20100101 Firefox/120.0

Business Process Name:

Stack Trace:

Caught exception: Laserfiche.Forms.CommonUtils.Exceptions.LFFormsException

Message: An error occurred while testing the data source connection. See the Windows Event Log for details. [LFF2400-DataSourceConnectionError]

at E_Forms.Controllers.AuthorizationController.TestDatasource(Int32 dbType, String server, String db, String user, String password, Int32 dbId)

at lambda_method(Closure , ControllerBase , Object[] )

at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)

at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c.<BeginInvokeSynchronousActionMethod>b__9_0(IAsyncResult asyncResult, ActionInvocation innerInvokeState)

at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult`2.CallEndDelegate(IAsyncResult asyncResult)

at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult)

at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass11_0.<InvokeActionMethodFilterAsynchronouslyRecursive>b__0()

at System.Web.Mvc.Async.AsyncControllerActionInvoker.AsyncInvocationWithFilters.<>c__DisplayClass11_2.<InvokeActionMethodFilterAsynchronouslyRecursive>b__2()

at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethodWithFilters(IAsyncResult asyncResult)

at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass3_6.<BeginInvokeAction>b__4()

at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass3_1.<BeginInvokeAction>b__1(IAsyncResult asyncResult)

Inner exception: System.Data.Odbc.OdbcException

Message: ERROR [HY000] [Microsoft][ODBC Excel Driver] The Microsoft Access database engine cannot open or write to the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view and write its data.

ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed

ERROR [HY000] [Microsoft][ODBC Excel Driver] The Microsoft Access database engine cannot open or write to the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view and write its data.

at Laserfiche.Forms.EntityModels.Data.ODBCDBConnector.OpenConnection()

at Laserfiche.Forms.CommonUtils.OdbcConnector..ctor(DataBaseType dbType, String server, String database, String uid, String pwd)

at Laserfiche.Forms.CommonUtils.OdbcConnector.GetODBCConnection(WindowsImpersonationContext& ctx, IntPtr& tokenPtr, DataBaseType databaseType, String server, String db, String user, String pw)

at E_Forms.Models.ViewModels.DataSource.TestDatasource(Int32 dbId, Int32 dbType, String server, String db, String user, String password)

at E_Forms.Controllers.AuthorizationController.TestDatasource(Int32 dbType, String server, String db, String user, String password, Int32 dbId)

Please note that the account I am using to connect to the data source have sufficient rights. I use it all the time to connect to other data sources, but I do this from Laserfiche Workflows, not Laserfiche Forms. The account is already added in Laserfiche Forms as an administrator account.

Also, the username and password for the account is correct.

I am running out of ideas to troubleshoot this issue. I would appreciate your support.

Sincerely,

Wissam

0 0

replied on December 19, 2023

I would highly recommend never using Excel or Access files as data sources and instead insert the data into a SQL database table and query it from there.

The issue with the error above is that the file is still in use by another application. It could be that you closed the file, but Excel or something else has not released it. This is a common scenario for these file types and why I would recommend not using them.

0 0

replied on December 19, 2023

Hi Blake,

Thanks for your response. The reason an Excel based data source was selected because the admin who will be overseeing the business process and the form does not have the expertise to handle an SQL database. This user will frequently access the excel file to add/update/remove rows. Also we want to ensure minimum dependency on the database analyst.

Thank you,

Wissam

0 0

replied on December 19, 2023

The error is expected behavior. Excel only ever allows one connection. It may work in Forms or Workflow for demo purposes, but it is unlikely it will work in a real-life scenario where multiple users may try to access it at the same time. Every time your user has the Excel file open, your data source would return that error.

That said, you could still use Excel as a front-end for a SQL database by using Power Query. That way Forms would work against the SQL table and your user would keep the user interface they're used to.

1 0

Question

Question

Adding an ODBC Data Source to Laserfiche Forms

Replies

Sign in to reply to this post.