Table structure and data transfer from SQL2000 to Access (.mdb)

Giganews Newsgroups
Subject: Table structure and data transfer from SQL2000 to Access (.mdb)
Posted by:  jc (jc_usern…@aanet.com.au)
Date: 7 Aug 2006

Hello.  I want to ask about the possibility of copying both a table
structure and it's contents from a
SQL server table to a table within MS access.  The problem cannot be
solve with a permanent table structure at the target location.
The names of the columns are essentially data with the application and
so are subject to change.  I am targeting a solution using SQL Query
Manager.

The approach I have tried (with failure) is
SELECT *
INTO  <linkedserver table>
FROM  <local table>

This should create and copy.  However, I am not sure if this is
achievable with this approach.

Refer to the dialogue;
-------------------------------------------------------
USE MASTER
GO
EXEC sp_addlinkedserver
  @SERVER  = 'Freddie',
  @PROVIDER = 'Microsoft.Jet.OLEDB.4.0',
  @SRVPRODUCT = 'OLE DB Provider for Jet',
  @DATASRC    = 'C:\temp\HMIS_Recipe.mdb'

-- I am not sure if this is required
EXEC sp_addlinkedsrvlogin 'Freddie', false, 'sa', 'Admin', NULL

SELECT * FROM Freddie...FRED                -- This is OK

SELECT * INTO #Temp FROM Freddie...FRED      -- This is OK

-- This fails - Refer error
SELECT * INTO Freddie.FRED65
  from #temp

Server: Msg 2760, Level 16, State 1, Line 1
Specified owner name 'Freddie' either does not exist or you do not have
permission to use it.

-- This also fails and I thought reflected the above select with naming
- Refer error
SELECT * INTO Freddie...FRED65
  from #temp

Server: Msg 117, Level 15, State 1, Line 2
The object name 'Freddie...' contains more than the maximum number of
prefixes. The maximum is 2.

EXEC sp_dropserver 'Freddie',
    @droplogins = 'droplogins'

------------------------------------------------------------

Thank you.

Regards JC...

Replies