How can i transfer a database from Access to Sql Server 2000?

Hy ,

How can i transfer a database from Access to Sql Server 2000?

Exactly : i want to transfer a .mdb into .mdf, and i want to keep the structure of .mdb, a structure which have tables, queries, macros, modules... When i start the transfer with Data Transformation Services ( DTS- Import Data ) , i can see that DTS shows me only tables and queries. At the end of transfer, in my .mdf database, on server, i have only tables, without queries, macros, modules.

My question is how can i transfer the queries and macros ( exactly how are in .mdb ) to .mdf database?

Thanks in advance for any advice..

Emanuel.

The way you did it, SQL Server will materialize the queries to tables. The problem is that Access does not use the SQL that SQL Server does, so this cannot be easily converted. Additionally SQL Server does not have a clue about Macros, so they can′t be imported either. You can use the upsizing wizard in Access if you want to keep your existing frontend in Access (because it will link automatically the new tables stored in SQL Server).

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

My very own first posting here. :)

I have tried using the DTS for the SQL Server 2000 with Data Source MS Access to destination SQL Server.Correct me if am wrong but I think you can get the Queries also but I dont know if the macro can be import as well. My best guess is to create another functions w/c works similar as your Macros.

Best Regards,
Vince

|||

Hi,

see more on this on this paper here: http://www.microsoft.com/technet/prodtechnol/sql/2000/Deploy/accessmigration.mspx

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

SQL Server surely won’t transform macros, forms, and modules because it doesn’t have the equivalents. So the question for you is: Do you want to rewrite your application on another development platform – C#, VB, C++, you name it, or do you want to continue developing in Access. If you choose the former, you’ve already seen what you can migrate. If you choose the latter, you can migrate your tables to SQL Server, get rid of the native Access Tables, and then link the SQL Server tables from Access. If you give the linked tables the same names they had when they were native in Access, you won’t have to modify your queries at all.

Zlatko