ODBC error messages

[Mike_Pelone]

Howdy!____I am using R&R Ver 10 (build 11).__My problem is trying to connect directly to an Access 97 file.__Error message displayed is "Database access [ODBC Microsoft access driver] syntax error in FROM clause..."____I used the help menu but that seems to lead me into the ODBC Data Source Administrator window. Multiple tabs and very little information on what parameters to set or change...____My software installation is a single work station.__Can anyone give an SQL "rookie" any hints on how to establish a direct connection to data inside an Access table? Also - does it get more involved as I move from Access97 to Access 2000??____Thanks for any help on this problem.____Mike Pelone__(716) 551 - 5755 ext: 5002____

[Michael]

I wouldn^t be so sure that it^s an ODBC error and maybe just a SQL error. Can you reply with the EXACT Query that you are using?? along with the Table and Field names that you are requesting?____Also what is your access db relation to R&R?? same computer? over a network via mapped drive?? OS ?? etc.____Thanks.____

[Mike_P._(Guest)]

Actually - I never "hooked" into the remote Access data table. It is not a question of a bad query - it appears to me that some connection is not made so I never get past step #1. A short time after I posted the original problem, I hooked into the data table using the ODBC window - but now I can^t go thru the same motions and connect with and Excel2000 table. The error message is somewhat different in that it appears that the *.dll file is not where is belongs...I had an older copy of Version 9.0 and was able to re-create the same problem and solution (that is, connect to the Access data table but NOT the Excel data table...)____This is the first time I have used the R&R SQL version to open up and report on non-Xbase data structures. I am sure that 90+ percent of the problem is me and the proper use of the setting in the ODBC window....____Let me know what else comes to mind !!__

[kfleming]

When you set up the data source, did you uncheck the use current directory box and then explicitly select the MDB file?____Kathleen__R&R Support

[Mike_P._(Guest)]

I was able to connect to the Access2000 data table using User DSN tab then selecting "create new data source"...____However, my next question is: can I make a similar ODBC connection to an Excel2000 spreadsheet? I don^t see a choice other than several database file types...do I need to install any other program features other than "typical" followed by the overlay of the Build 11 updated files???____Thanks for the assistance to date on this problem..____Mike P.__Buffalo__

[Mike_P._(Guest)]

Another thought...____After I open the ODBC Data Source Administrator window - I do not see any file type for Excel under the "User DSN" tab but see a driver name in the "Drivers" tab in the same window??__Is there any distinction between how data tables are handled in Access2000 versus columns & rows in worksheet tabs within Excel ???____

[kfleming]

When you create a new data source, you first select an ODBC driver.__The one for Excel is separate from the one for Access.____Kathleen__R&R Support

[Mike_P._(Guest)]

I certainly agree on that...____But right from the get-go (launch program / file new / blank report / Data Source Window opens with a prompt to "select a data source" with choices in the window) - Yes I can see a choice for MS Access Database file types but NO Excel. Am I missing something on the install?____Also - when work my way into the ODBC Data Source Administrator window and look under the "User Data Sources" tab - I can see MS Access once again but NO Excel...____I am running XP and trying to link to an Excel2000 file type....____Also - If and/or when I get a connection to the Excel file - will I see a sheet name as the "data table" to select fields from?____Thanks for the pre-New Year^s Eve attention to this problem...____Mike P.____

[kfleming]

You first have to create a data source for it to appear in the selection list. From the Datasource menu choose Configure which will bring you to the ODBC administrator and then choose Add. This will give you a listing of drivers. Select the Excel driver from the list and you should be on your way.____Kathleen__R&R Support