Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server DTS
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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
  #2 (permalink)  
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
  #3 (permalink)  
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. :)
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 11:52 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.