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

--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

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