[RBG7-L] - Tutorial - Using ODBC to connect to Excel..... For Dummies...

Chuck_Finley Mon, 28 Mar 2005 19:34:16 -0800

How to use an ODBC connection to existing xls workbook in RBASE 7.1.

I?m a big fan off posting the resolution to problems that have consumed to much of my time. Hopefully I will save some other poor sucker from the frustration I have experienced (although I enjoy the satisfaction when I finally win!). I suspect some of you smart people may correct any lies that I inadvertently tell as this is my first try with ODBC.

Here a few things that I have learned on this project:

The ODBC drivers provided by Microsoft are installed when you install Microsoft Excel.

I seems that for the last few versions of office it is the same driver.

sconnect ';driver={Microsoft Excel Driver (*.xls)};dbq=c:\Testfile?

Uncle Bill opens Excel worksheets as READ ONLY (add ;ReadOnly=0 to open R/W)

sconnect ';driver= {Microsoft ExcelDriver(*.xls)};dbq=c:\test_U_R_1;ReadOnly=0'

For the Excel ODBC driver to work properly, any instance of Excel that has the spreadsheet open must first be closed.

You must use named ranges to specify tables / Table names (I can?t get Rbase to work without a named range) Highlight your area and {Insert ? Name ? Define] make sure to start on the row above where you data starts as Uncle Bill assumes the first row is the table name (and the first row will burn off).

You can?t use alias in the Rbase Sattach command if you want to update the excel worksheet. When using an alias with excel, everything works great until you try to update the field. It will accept the change in the browser, but when you move off the row it will revert back to the previous value. Using the update command in an RMD will not throw and error during a trace, but the field does not get updated. (I?m pretty sure my kids learned some new words from me while I was finding this one!)

Most of this was learned at about 3 AM over the last week.

I have attached a few of the better links I used in my research.

P.S. Since my team made it past Duke andKentucky, I have to say GO SPARTINS!!

Excel Data sheets open as Read Only

http://support.microsoft.com/default.aspx?scid=kb;en-us;316475

http://support.microsoft.com/default.aspx?scid=kb;en-us;178717

http://msdn.microsoft.com/library/default.asp?url="">

Names in first row

http://support.microsoft.com/default.aspx?scid=kb;en-us;288343

ODBC DSN less Syntax

http://www.able-consulting.com/MDAC/ADO/Connection/ODBC_DSNLess.htm#ODBCDriverForExcel

OFF97: Complete Setup Doesn't Install All ODBC Drivers