Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server DTS
| Search | Today's Posts | Mark Forums Read
SQL Server DTS Discussion specific to Data Transformation Service with SQL Server. General SQL Server discussions should use the general SQL Server forum. Readers of the book Professional SQL Server 2000 DTS with questions specific to that book should post in that book forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server DTS section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 21st, 2005, 05:06 AM
Registered User
 
Join Date: Dec 2005
Location: Athens, , Greece.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default create SQL Server table from Access

Hello All,

I have stumbled on a quite tricky problem and i need some help as I'm new to DTS. I have an Access database and i need to import its tables in SQL Server 2000 DB. The problem is that the number of tables in Access is not constant, so i have to check how many tables are in there and create the equivalent ones in SQL Server..
I tried to use ADOX.Catalog in ActiveX Script Task but its a whole mess to create the table in SQL from the Access prototype.. I found somewhere that i can use something like this:
"SELECT * INTO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Password='';Initial Catalog=myDB;Data Source=MyServerName].[dbo].[myTable] FROM [Provider=Microsoft.Jet.OLEDB.4.0;Data Source=myAccessDB].[myAccessTable]"

When i run the package i get the error shown below:

Invalid object 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=myAccessDB].[myAccessTable]'

Does anyone know what the problem is or if there is another way to get through this ;; I can provide the code if needed.

Any help appreciated

Kostas Lagos
 
Old December 22nd, 2005, 01:00 AM
Friend of Wrox
 
Join Date: Dec 2005
Location: , AZ, .
Posts: 146
Thanks: 0
Thanked 1 Time in 1 Post
Default

--A simpler approach would be to setup the Access Database as a linked server
--(see http://support.microsoft.com/?kbid=246255 )
-- Then use the following script: (this could be an EXEC SQL task in DTS, or SQL Agent Job Step)
--
-- Purpose: Import data from unknown tables in an Access Database
-- Assumptions: the value you use for @dbname is defined as a linked server
-- and that EXEC sp_tables_ex @dbname. Additionally, you must have
-- Create TABLE permissions in the database
-- Caveats: It will drop a table in the existing database if it has the same name
-- as the table in the access database
-- Written by David Lundell Copyright 2005
-- www.MutuallyBeneficial.com

USE tempdb -- for illustration purposes
GO
DECLARE @dbname sysname
SET @dbname = '' -- Put in your linked server name

CREATE TABLE #AccTableList
    (TABLE_CAT sysname NULL
    ,TABLE_SCHEM sysname NULL
    ,TABLE_NAME sysname NULL
    ,TABLE_TYPE varchar(32) NULL
    ,REMARKS varchar(254) NULL
    )


DECLARE @vchDrop varchar(8000)
DECLARE @vchPopulate varchar(8000)

DECLARE @AccTableName sysname

INSERT #AccTableList (TABLE_CAT ,TABLE_SCHEM ,TABLE_NAME ,TABLE_TYPE ,REMARKS )
EXEC sp_tables_ex @dbname

DECLARE CursAccTable CURSOR FAST_FORWARD
FOR
    SELECT TABLE_NAME FROM #AccTableList
    WHERE TABLE_TYPE = 'TABLE'
    AND TABLE_NAME IS NOT NULL -- Put in your own criteria

OPEN CursAccTable
FETCH NEXT FROM CursAccTable INTO @AccTableName

WHILE @@FETCH_STATUS = 0
   BEGIN

      SELECT @vchDrop = 'IF OBJECTPROPERTY(OBJECT_ID(''' + @AccTableName
        +'''), ''IsUserTable'') = 1 BEGIN DROP TABLE [' + @AccTableName + '] END'
      PRINT @vchDrop

    SELECT @vchPopulate = 'SELECT * INTO [' + @AccTableName + '] FROM ' + @dbname + '...' + @AccTableName
    PRINT @vchPopulate


      FETCH NEXT FROM CursAccTable INTO @AccTableName

   END

   CLOSE CursAccTable
   DEALLOCATE CursAccTable

DROP TABLE #AccTableList

David_the_DBA
www.mutuallybeneficial.com
 
Old December 30th, 2005, 06:11 AM
Registered User
 
Join Date: Dec 2005
Location: Athens, , Greece.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you very much for your answer..I 'll give it a shot and i'll let you know. :)




Similar Threads
Thread Thread Starter Forum Replies Last Post
Create new Table in access djobes31770 VB Databases Basics 4 November 20th, 2007 09:37 AM
can to link a ms access table to sql server? sugar SQL Server 2000 6 May 23rd, 2006 11:45 AM
Access MDB with a Linked SQL Server Table..... timmaher Classic ASP Databases 0 September 28th, 2005 03:57 AM
How to create table in Ms Access (TSQL) abdusalam SQL Server 2000 4 July 31st, 2004 01:54 AM
Access and update linked SQL Server 2000 table Lizu Access 9 May 10th, 2004 12:42 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.