Hi,

I am trying to migrate the whole Ms-Access2003 database to SQL serever 2005. I am doing this by using MS-Access *.adp project which contains the references of SQL server table and Quries.

When I am modifying the table in SQL server2005 that reflaction appears in *.adp project properly but when I am doing the same thing in Ms- Access project i.e. *.adp . It shows warning "The version of Microsoft Office Access doesn't support design change with version of Microsoft SQL Server your Access project is connected to. See Microsoft Office update website for latest information and download(on the help menu, click office on the web). Your design will not be saved.

And also I am not able to save the changes done in access project.
I am also getting another warning like
"You have connected to a version of SQL Server later than SQL Server 2000. The version of Visual Studio or Access that you are using wasd released before the version of SQL server to which you are connected. For this reason, you might encounter problem.

..... some more line.........
.... ... some more line...."

Is there is any way to create /alter the table using Ms-Access Project using *.adp file that changes should be saved in the SQL server.

I have tried this with the Access2007 project also but didn't make any progress.
Waiting for a best possible solution.

Thanks,
Dhruvendra
dhruvendra@hotmail.com

How are you connecting to the database? Are you using the SQL Native Client ODBC Driver or just the standard SQL Server ODBC Driver? You would need to be using the SQL Native Client ODBC driver.

And, it is possible that you may need to use the SQL Server Management Client for design changes as it is a lot newer database than existed when Access 2003 was built.

Hi boblarson,

Thanks for the reply.

I am using upsizing wizard functionality under Ms-Access 2003
to migrate Access data/queries to SQL Server 2005 which further creates *.adp project as a separate file.

I have also used SQL Server Migration Access tool
(SSMA 2005 for Access v4.0 (http://www.microsoft.com/downloads/details.aspx?FamilyID=4ECD1E67-C64E-49E6-821E-C4D83D9D5FED&displaylang=en):) given by Microsoft to migrate the same.

I am not quite sure about the conectivity of SQL Server with Access Project with connected to either SQL Native Client ODBC Driver Or standard SQL Server ODBC Driver !!!

but I have got reference for upsizing http://office.microsoft.com/en-us/access/HP052730091033.aspx.

Here my query is can We create/modify/alter the tables/queries using *.adp access project ? which should reflact on the SQL server2005 database.
If yes then how ?

Thanking you in advance.
Regards,
Dhruvendra

This is just an accepted limitation of using Access 2003 with SQL Server 2005 isn't it? I remember that Microsoft said that the ability to modify queries or create tables in Access ADP files was going away with either SQL 2005 or Access 2007...can't remember for sure which one though.

I've always prefered to make changes in the database anyway, so it doesn't bother me. But good luck in your migration. I'm have problems in my testing that I've yet to work out...and no one here seems to have any ideas.

Thanks for the inputs .

I have got my solution by using MS-Access 2007 project file with SQL server2005 combination , which seems fine to me till now.

I agree with "arichins" points but I feel that Ms-Access 2003 should compatible with SQL Server2000 (instead of SQL Server2005) based on my research but Sorry to say that I have not verified yet..:confused:..

Let see If I will make any progress... will let you know...:)

Regards,
Dhruvendra