Hi, We used to backup few SQL Server 2005 tables into MS Access file. The access file is accessed by using linked server.

sp_addlinkedserver 'AccessBackup', 'Access 97', 'Microsoft.Jet.OLEDB.4.0', 'D:\Shaju\AccessBackup\InputDB.mdb'

sp_addlinkedsrvlogin 'AccessBackup', false, 'sa', 'Admin', NULL

1) SELECT * FROM [AccessBackup]...[MailingList]

Works fine.

2) SELECT *

INTO [AccessBackup]...[Cities]

FROM [dbo].[tblCities]

Iam trying to back up tblCities to access db. The error i get is

The object name 'AccessBackup...Cities' contains more than the maximum number of prefixes. The maximum is 2.

3) INSERT INTO [AccessBackup]...[Cities]

SELECT * FROM [dbo].[tblCities]

The error i get is

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "AccessBackup" does not contain the table "Cities". The table either does not exist or the current user does not have permissions on that table.'

How to use select into a linked server