Access to Ms Sql ??

godhands · #1 (**permalink**) 12-13-2006, 06:53 AM

Im a beginner, i know asp and asp.net, i have done several projects over that. But all are using Asp, Asp.net with the back-end using Ms- Access. Now whats the problem one of my clients want to upgrade the Ms-Access into Sql database. I search all around the net.

I need to convert Ms-Access data to Ms -Sql How will i do that ?

Is there any software if its available free that will be well and good becos my client is not goin to pay me for converting the data to ms-sql.

nottheusual1 · #2 (**permalink**) 12-13-2006, 04:12 PM

SQL Server has migration tools that can be used for Access DB's. Not knowing which version of SQL Server you'll be using, here's an examlple for "old" SQL Server 7:

http://www.microsoft.com/technet/pro...oy/access.mspx

You shouldn't need to buy software, but you'll need to learn some admin stuff or pay someone who knows. We are all "self-taught" - and aren't that bright - so it is doable.

theengel · #3 (**permalink**) 12-13-2006, 04:31 PM

I'm not sure about the webpages themselves...I've always used MySQL with PHP. And I'm not even sure if this would apply in the case of MS-SQL. But I do know that there are programs that can take access data tables and transform them into SQL statements. Do SQL statements work with MS SQL?

ccazier · #4 (**permalink**) 12-13-2006, 04:49 PM

Do it all via ODBC

wcollens · #5 (**permalink**) 12-13-2006, 05:09 PM

We recently used the "SQL Server Migration Assistant for Access" to convert a client's Access 2000 application to SQL Server Management Studio. This is a FREE tool

Here is the link: http://www.microsoft.com/downloads/d...DisplayLang=en

and you will need a FREE SSMA licence key from here:
http://www.microsoft.com/downloads/d...DisplayLang=en

Good Luck!

ronchalice · #6 (**permalink**) 12-13-2006, 06:39 PM

There is another freebie that comes as a part of MS Office 2003 Pro, and I think you can get it direct from MS as well. It's called the SQL Server Desktop Engine (MSDE). When installed alongside Access 2003, the Access application now shows a tool to covert any Access database to an Access Project (.adp) with a SQL server back end. You can then migrate the data from the MSDE to any SQL server 7 database.

garywhw · #7 (**permalink**) 12-13-2006, 08:24 PM

If all free stuff doesnt work for you for some reasons, the last resource is to export your Ms Access data to Ms Excel.

From Ms Excel, you could easily insert them into Ms Sql Server without much problem. However, you may need to do a bit of admin manually by comparing the design view of both databases. Find the difference in the design view of each table and update them accordingly on the Ms Sql Server.

This should help :-)

blitzen · #8 (**permalink**) 12-13-2006, 09:18 PM

phpMyAdmin (free) doesn't convert, but will import into MySQL from many formats. It's a great administrator for your database.

godhands · #9 (**permalink**) 01-13-2007, 08:26 AM

I like to know is there any php or asp page to transfer the access db to ms sql server

Matt Kelly · #10 (**permalink**) 02-12-2007, 08:18 PM

I've done this before and did a manual export from access into a excel file and then wrote a quick custom script to import into my SQL data base.

If you know C++, and since you have already programmed ASP/Access, it should be easy to pickup PHP/SQL.

If you are only doing the export/import once ever, the custom approach may make the most sense anyways IMO.