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
  #1 (permalink)  
Old January 29th, 2008, 11:53 PM
Authorized User
 
Join Date: Jun 2007
Location: , , .
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default Import Access data whith varying columns

Hi ,

I am trying to import n number of access database.

A1.mdb
A2.mdb
A3.mdb
.
.
.
An.mdb

Each of the above .MDB file has a table name called tblTEST
The table name same across all the mdb files.

But the COLUMNS ARE NOT THE SAME IN EACH TABLE tblTEST (but different .mdb file).


What i want is below,

1. Put all the access file in a folder.

2. copy one mdb file :- its table tblTEST,the STRUCTURE and DATA into sql server at a time.

3. Loop thro all the mdb files in the folder.

I am not sure how to automate this task. To copy the whole structure and data from access to Sql server.

That means i need to create a table with the exact structure as in access in SQL SERVER and then import the data.

Any help is highly appreciated.

Thanks Heaps. This is really important and urgent.



  #2 (permalink)  
Old January 30th, 2008, 12:06 AM
Friend of Wrox
 
Join Date: Oct 2007
Location: , , .
Posts: 130
Thanks: 0
Thanked 3 Times in 3 Posts
Send a message via AIM to urtrivedi
Default

try macro, there are many options in macro such as open database, run query etc. you can also edit it like vb code.

urt
  #3 (permalink)  
Old January 30th, 2008, 12:09 AM
Authorized User
 
Join Date: Jun 2007
Location: , , .
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

But i need to write a DTS or a Stored procedure to import the data from the MDB files into SQL SERVER.

  #4 (permalink)  
Old January 30th, 2008, 12:12 AM
Friend of Wrox
 
Join Date: Oct 2007
Location: , , .
Posts: 130
Thanks: 0
Thanked 3 Times in 3 Posts
Send a message via AIM to urtrivedi
Default

First you take every table from various mdb files in one mdb file, and then from sql server import all tables from one access file

urt
  #5 (permalink)  
Old January 30th, 2008, 12:19 AM
Authorized User
 
Join Date: Jun 2007
Location: , , .
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi urt,

Thanks for the suggestion, but...

The Table names are the same in all the mdb files. And more over how do i programatically copy all the tables into sql server.

As the table names are same across all the mdb files but the columns will vary in each of the mdb file.

So copying the tables into the same mdb file will be needed to be done programatically and thats a waste of time.

  #6 (permalink)  
Old January 30th, 2008, 12:38 AM
Friend of Wrox
 
Join Date: Oct 2007
Location: , , .
Posts: 130
Thanks: 0
Thanked 3 Times in 3 Posts
Send a message via AIM to urtrivedi
Default

How many mdb files are there and how many tables to import are there 1 mdb file

urt
  #7 (permalink)  
Old January 30th, 2008, 12:46 AM
Authorized User
 
Join Date: Jun 2007
Location: , , .
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

There are N mdb files, but the same table name in all the mdb files. And i want to import this particulat table in all the mdb files.

  #8 (permalink)  
Old January 30th, 2008, 12:47 AM
Friend of Wrox
 
Join Date: Oct 2007
Location: , , .
Posts: 130
Thanks: 0
Thanked 3 Times in 3 Posts
Send a message via AIM to urtrivedi
Default

Your create one access file in the same folder where all a1,a2.... files are placed, say final.mdb
create new module and paste following code

Sub ImportTables()
    For i = 1 To 10
        DoCmd.RunSQL ("SELECT * into tblTEST" & Trim(Str(i)) & " from A" & Trim(Str(i)) & ".mdb.area")
    Next
End Sub

replace 10 with no of mdb files in your folder
run this module all tables will be imported directly to your one final database, now you can import this one file in sql server.

urt
  #9 (permalink)  
Old January 30th, 2008, 12:56 AM
Authorized User
 
Join Date: Jun 2007
Location: , , .
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

hmmm......... yeah now i get what u mean. Except that, as i said this is a matter of time as well, as the data in each table is a few hundred thousand.

But will try this solution. Thanks for the suggestion.

If anything better please let me know. As i will have to look into the time as well.

  #10 (permalink)  
Old January 30th, 2008, 01:00 AM
Friend of Wrox
 
Join Date: Oct 2007
Location: , , .
Posts: 130
Thanks: 0
Thanked 3 Times in 3 Posts
Send a message via AIM to urtrivedi
Default

Sub ImportTables()
    For i = 1 To N
        DoCmd.RunSQL ("SELECT * into tblTEST" & Trim(Str(i)) & " from A" & Trim(Str(i)) & ".mdb.tblTEST")
    Next
End Sub


urt




Similar Threads
Thread Thread Starter Forum Replies Last Post
varying data range stealthdevil Excel VBA 6 July 27th, 2007 02:30 AM
append record whith do while function iisha64 Access VBA 0 February 19th, 2007 09:24 AM
Import Data from Open Excel Sheet to Access chintu4u Pro VB Databases 0 May 15th, 2006 01:24 AM
Import Excell Data into Access Mitch Access 4 February 15th, 2005 10:29 PM
Editing Access and import data edramail Access 2 May 7th, 2004 02:27 AM





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