mizveggie
asked on
Strange error selecting data from Access 2007 Linked Server via SQL Server 2000
I am using SQL server 2000 and have successfully created a linked server to an Access 2007 database. By successful I mean I can expand the linked server and see the tables. However, when I run a simple select from one of the tables, I get the following error:
OLE DB provider 'Microsoft.ACE.OLEDB.12.0' returned an invalid schema definition.
My code is: SELECT * FROM RMAData...RMA
Is there a problem with SQL Server 2000 and Access 2007 playing nice? Is there something else I need to specify in my connection? The permissions are all setup in the linked server connection.
OLE DB provider 'Microsoft.ACE.OLEDB.12.0'
My code is: SELECT * FROM RMAData...RMA
Is there a problem with SQL Server 2000 and Access 2007 playing nice? Is there something else I need to specify in my connection? The permissions are all setup in the linked server connection.
Is access closed ?
try this
SELECT * FROM ExcelSource1...Sheet$
SELECT * FROM ExcelSource1...Sheet$
SELECT * FROM RMAData...RMA$
ASKER
When I use your code I get the following error:
OLE DB provider 'RMAData' does not contain table 'RMA$'. The table either does not exist or the current user does not have permissions on that table.
OLE DB provider 'RMAData' does not contain table 'RMA$'. The table either does not exist or the current user does not have permissions on that table.
ASKER
what does closed mean? I don't currently have any users in the database. Do I somehow need to open access first?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.