How do I connect to an Access database query from an office spreadsheet webpart in Sharepoint?
I have information of all employees stored in an Access database stored on a server. There is a query that contains the Project roster. I want to connect to this query from Sharepoint through an office spreadsheet web part.
I start by going to "connect to data" from Sharepoint. It prompts me for an .odc file and I create a new source file. I go through the data connection wziard and choose "ODBC DSN" and try both "Microsoft Access Driver" and "MS Access database". I choose the table that contains the project roster. When I finish, I receive an error in the spreadsheet webpart "[Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'." I want for all users with reader rights in Sharepoint to be able to view the roster from Sharepoint. The thing is, if the database is stored on my hard drive, I am able to view it with no problems. How do I set this up so the database can be stored on a server and users will be able to view the roster query from Sharepoint? Please help! Thanks in advance.
