By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,290 Members | 1,631 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,290 IT Pros & Developers. It's quick & easy.

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

P: n/a
jc
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...

Aug 7 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
jc (jc********@aanet.com.au) writes:
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.
While this will copy some of the table properties, it will not carry
over indexes etc. So I don't think that this is a good start. You
should probably get script with CREATE TABLE statemnets that also
include definitions of constraints, keys indexes etc. Bare in mind that
Access and SQL Server are quite different, so you may have to do changes
along the way.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Aug 8 '06 #2

P: n/a
jc

Hi.
My basic problem with the initial posting is that I can read tables
from the linked server (the Access .mdb) but cannot create a table with
the "SELECT * INTO table" statement.

Regards JC....

Aug 9 '06 #3

P: n/a
jc (jc********@aanet.com.au) writes:
My basic problem with the initial posting is that I can read tables
from the linked server (the Access .mdb) but cannot create a table with
the "SELECT * INTO table" statement.
Yeah, the point with my post is that even if you could it would only take
you half-way, so I don't think is a viable route.

But I see now that I misread your message (was tired last night). I figured
you wanted to copy tables from Access to SQL Server when you in fact want
to do it in the opposite direction. And I'm afraid that's an even deader
end. How would SQL Server be able to create tables on an entirely different
engine? The linked server may even be a non-relational data source.

You may want to look at the Import/Export wizards. I have never used them
myself, though.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Aug 9 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.