Thanks Bob. I did some research into the office ODBC driver levels. It looks like office 2003 installs the driver when you install office. From what I can find, I think it's the same driver for 97-2003. The version box in the ODBC Data Source Administrator has a drop down for 97-2000. If you have 2003 option I'd love to hear about it. I can't find any downloads for newer drivers.
I have made some progress. Prior to researching the driver level, I did not understand that you must define the 'names' in the XLS spreadsheet (although It does make sense). I am able to attach to a test spreadsheet and see the data. I can make changes to existing data, but if I try to edit a field and change it from a null to a non null character it will accept the character and the disappear when I hit enter. Am I doing something stupid?
My second problem is that I get the same error on the 'Usage_Report' spreadsheet that is my ultimate goal to update. '[Microsoft][ODBC Driver manager] data source name not found and no default drive specified' still appears when I try to access the table. I have even added 'named' range and tried that. Does anyone have any ideas?
Does anyone know the name of the Microsoft excel driver so I can try the sconnect command. I am currently using
SCONNECT ';driver=Microsoft Excel Driver (*.xls) ;dbq=c:\test1'
and get the same error message referenced above.
Thanks again for all your help.
To: [email protected] (RBG7-L Mailing List)
From: [EMAIL PROTECTED]
Sent by: [email protected]
Date: 03/22/2005 08:12AM
Subject: [RBG7-L] - Re: Sattach to xls workbook
Chuck,
What version of Excel are you using? On my XP Pro machine, the standard supplied MicroSoft driver supports only 2000, not 2003. You may have to load an updated driver for the 2003 version. When creating your ODBC source in the ODBC manager, there is a small drop down window where you select the Excel version. Insure that this is set to the correct version or it will not work properly.
-Bob
-------------- Original message --------------Hopefully I won't wear out my welcome here! I now need to export data into an xls spreadsheet template that has existing information in it. I am struggling with the ODBC to an XLS workbook. I can go to Access or another RBase database with no problem. I set up my file 'usage_request.xls' as a system DSN using the Microsoft XLS driver. I connect to the data source just fine, but when I try to attach to the tables (through the menu bar) I get an error (2045) Syntax is incorrect for the command Sconnect. It then prompts me to attach tables. I select my table 'Usage Request' (a workbook tab) and supply a valid alias name 'Usage_Req'. I get the same (2045) Error. I click OK and I get Error (2370) Row length exceeds maximum of 4096 characters. My table appears in the table listing. When I try to access the table I get an error [Microsoft][ODBC Driver manager] data source name not found and no default drive specified. What am I doing wrong..........and yes I'm sure if Razzak responds...it will be a short answer that get me out of trouble and the....It's a simple as that or that's all there is to it...I'm spending way to much time in the forum!
