Converting ACCESS and EXCEL data to SQL

Hi,

I have some tables in an ACCESS database, and would like to recreate them in a SQL2005 databse.
How may this be done?
I am able to create a Data Component with the ACCESS mdb file.

Likewise, how may I convert EXCEL data to SQL2005 table?
Thanks.

David

Access should have an upsizing wizard look for it, excel try the link and code below. Hope this helps.

http://www.sqlis.com/

/* Excel as a linked server */
/* Assuming we have an Excel file 'D:\testi\Myexcel.xls'
with following data in the first sheet:
id name
1 a
2 b
3 c
*/

EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'D:\testi\Myexcel.xls',
NULL,
'Excel 5.0'

EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false'

EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource

SELECT *
FROM ExcelSource...Sheet1$

CREATE TABLE test_excel
(id int,
name varchar(255))
GO

INSERT INTO test_excel
SELECT *
FROM ExcelSource...Sheet1$

SELECT *
FROM test_excel

/* Now define two ranges in Excel on the 2nd sheet as tables */
/* Select the range, Insert->Name->Define */
/* Note: sp_tables_ex does not recognize the defined tables */
/* We can still refer to the tables explicitly */

EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource

SELECT *
FROM ExcelSource...Table1

SELECT *
FROM ExcelSource...Table2

|||

Thank you very much for the reply!
Moving along, I tried do an "upsize" from ACCESS to SQL, but I was not able to create a DSN for the SQL server. (Could not specify the "server"; I have tried using "local" and it didn't work.)
I am using SQL 2005 express - not the full version.
Can anyone tell me if this is due to limitation of the express version?
i.e. is it not possible to create a DSN to SQL 2005 Express ?

David

|||

You don't need DSN see if you can upsize it into SQL Server 2000. Then you can backup and restore it. Try the link below download and install the SQL Server 2005 Express Manager it, there must be a way to upsize. BTW Microsoft bought a migration tool company I forgot all about it you can download from the company site and test drive or use the link below to sign up for a Microsoft beta program. Hope this helps.

http://www.microsoft.com/sql/migration/default.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sseoverview.asp

sqlsql