How to use excel as a database

[Former Member]

Hello,

due to fact that excel's datasheet does not work anymore when I use an ODBC connexion, I would like to known if anyone has an idea so as to use ADO or OLEDB in order to use excel as a database.

I found an OLEDB Connector in xMII 11.5 but I don't know how to work with it. Where am I supposed to set up in windows?

If also other solutions exist, I would be glad to learn it!

Thanks

[Former Member]

In all seriousness, you should avoid using Excel in this mode. It was not designed for multiuser access and will cause you nothing but problems.

If you absolutely MUST do this, I recommend the following:

1) Use the UDS 4.0 OLEDB connector to connect to the Excel spreadsheet

2) Create a scheduled BLS transaction that reads the contents of the Excel spreadsheet and either a) writes the resultant XML to some persistent storage or b) write the data to a database table that matches the structure of the Excel sheet

3) Design any other application access to use this "cached" version of the data

This will minimize the conflicts accessing the Excel file.

You could also provide a clickable link on a web page to "update the cache" (e.g. run the BLS transaction), if a user updated the Excel file and wants those changes to be immediately visible.

In fact, you could quite easily add this link as a button in the Excel workbook itself, which would run the appropriate URL whenever the button was pressed.

[Former Member]

Hi,

follow below steps

1 Create a excel file that holds data.

2 Create a range name for excel file.

[Former Member]

Follow below steps.

1 Create a excel file that holds data.

2 Create a range name for excel file.

To create name range.

(I)Go to Insert-> Name->Define. Click Define. A dialog box appears. Enter Some Name .

(II) Then Click image present on right hand down corner.

Select the all the record which you want in your table.

(III) A small dialog will appear. Then click on the button present at right corner.

U have created a table now.

3. Creat a system DSN. Select Microsoft Excel driver.

Configure Excel file u created. Save DSN with name.

4. Use this DSN name to configure this Excel database with xMII.