468,316 Members | 1,529 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,316 developers. It's quick & easy.

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

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
3 2274
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
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
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.

Similar topics

reply views Thread by Robert Morgan | last post: by
reply views Thread by hai99 | last post: by
2 posts views Thread by Andy Davis | last post: by
reply views Thread by Vikram Attiganal | last post: by
3 posts views Thread by genojoe | last post: by
1 post views Thread by howard w | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.