Expand my Community achievements bar.

Dive into Adobe Summit 2024! Explore curated list of AEM sessions & labs, register, connect with experts, ask questions, engage, and share insights. Don't miss the excitement.

oledb connection string for Excel file

Avatar

Level 3

I create an OLEDB data connection with Excel file on Designer ES2, my form will only need to read this file for data, there is no need to lock it at all.

Following is my connection string :

Provider=MSDASQL.1;Persist Security Info=False;Data Source=Excel Files;Mode=Read;Extended Properties="DSN=Excel Files;DBQ=C:\test.xls;DefaultDir=C:;DriverId=790;MaxBufferSize=2048;PageTimeout=5;"

The case is after I open my pdf, then if I open the excel file using Office Excel, it will prompt a dialog which told it is being used by some application now, either choose Read only or ....

In fact I just need to read the Excel file for data in my pdf, I hope I can still edit the excel file in office at the same time.

So I change the mode from Read to Share Deny None, as shown below:

Provider=MSDASQL.1;Persist Security Info=False;Data Source=Excel Files;"Mode=Share Deny None";Extended Properties="DSN=Excel Files;DBQ=C:\test.xls;DefaultDir=C:;DriverId=790;MaxBufferSize=2048;PageTimeout=5;"

However now I can't even open the excel file in Office after I open my form, it simply tells can't use the file at all.

Any ideas?

6 Replies

Avatar

Former Community Member

Certainly when you execute an ODBC connection to the SS it is opening that spread sheet and the connection will keep the sheet open until you close it or the user closes the form. After you retrieve your data you will want to call the Close method

xfa.sourceSet.DataConnection.close()

Other than that I do not know enough about how Excel supports ODBC connections to suggest what settins in the data string should be set.

Paul

Avatar

Level 3

Dear Paul,

So do you mean there is no way that the Excel file will keep locked whenever a connection is not closed?

I just wonder if I can still open and edit the spreadsheet in Office if the connection is still not closed,

Rgds.

Avatar

Former Community Member

Yes that is wha I am saying ....when your form is getting the data and the ODBC connection is executed the file will be locked. Have you written code to do the connection or did you just use the wizard?

paul

Avatar

Level 3

Dear Paul,

I simply write these codes :

xfa.sourceSet.DataConnection.open();
xfa.sourceSet.DataConnection.first();

while not EOF

{    do something ...

     xfa.sourceSet.DataConnection.next();

}

I see your words.

ie. There is no way the file must get locked unless I execute xfa.sourceSet.DataConnection.close();

right?

Rgds.

Avatar

Former Community Member

Yes that is correct ...so as th elast statement in your code add the command:

xfa.sourceSet.DataConnection.close();

This shoudl close the connection and free the lock after you have completed your retrieval of information. certainly that is how it works on "real" databases and thats what i expect in Excel but you will have to try it and see.

Paul

Avatar

Level 3

Paul,

Close connection can release the lock, thanks for that.