Import Access tables into SQL2005

[sparkbyte]

I need a VBscript that will query SQL2005 for the user tables in a database and then open an MDB (Access2003) file and import all matching tables.

I have an Access database that needs to be migrated to MS SQL server but it has grown so much that it is now spread across multiple MDBs.

I have the portion of the script written that will enumerate all files within a given sub folder structure, now I need to add the bit for the data import.

Any help, links, or examples would be greatly appreciated.

Thanks!!!

Thanks

John Fuhrman
faq329-6766

 

[tektipGuyInWhistler]

Hello. Have you considered using the SQL Management Studio to do this? Much easier and faster than all that scripting. The basics are:

1) create your sql database.
2) right click on the new db and choose Import Data
- change the import type to Access and browse to your database
- follow the rest of the wizard....

....alternatively
For what is worth - I'm a big advocate of sql scripts for a number of reasons. You can always put together a script that will create the tables and another script file that creates the relationships. This is good for version control and it gives you the ability to get predictable results on another server for development purposes.

Maybe you already know tsql, if not - here are some snippets and there is plenty of material via google too.

Not sure if this helps you or not. If this doesn't work for you then I can always get you started on the ado stuff to read the db structure - really trying to make it easier for you.

Regards,
Eric

TSQL for Create Database

CREATE DATABASE yourDBName ON PRIMARY (
NAME=DataFile1,
FileName='E:\Sql.Data\yourDBName\yourDBName_data.mdf',
Size=5MB,
MaxSize=500MB,
FileGrowth=5MB
)
LOG ON (
NAME=LogFile1,
FileName='E:\Sql.Data\yourDBName\yourDBName_log.ldf',
Size=10MB,
MaxSize=500MB,
FileGrowth=5MB
)

TSQL for Foreign Key Constraints
Here there would be a country table and a client table.

--=============================================================
--Clients
--Table Name: tblClient
--=============================================================
ALTER TABLE tblClient ADD CONSTRAINT FK_tblCountry_tblClient REFERENCES tblCountry (countryID)

ALTER TABLE tblClient ADD constraint constrain_Unique_ClientName unique nonclustered(Name0)

CREATE UNIQUE INDEX idx_Unique_ClientName ON tblClient(Name0)
go