Not able to create new tables/queries in .adp

[Vinod]

Hi All,

I've comeup with an issue in my .adp file. I've created on .adp file in
Access 2003 followedy by providing 'sql sever 2005' server name. Once it
created, I have got all the tables from 'sql sever 2005' database which I've
selected.

I wanted create my own queries and tables base in .adp file but I am unable
to create tables and queries. I've got a message while trying to create new
as table as follows.

"This version of Microsoft Access does not support design changes with the
version of Microsoft SQL server your Access project is connecte to. See the
Microsoft office updates website for latest information and downloads (on
Help menu, click office on the web). Your design changes will not be save".

Can any one help me out by sharing your idea or thought in resolving above
issue, it would be appreciated.

Thanks
Vinod

 

[Norman Yuan]

I am sure you know tables/queries(view,SPs, UDF) shown in ADP file actually
lives in SQL Server.

Access2003 does not support creating/designing SQL Server 2005's server
objects (table and queries), as you already know. You need to use Access2007
or use other tools, such as SQL Server Management Studio/Express. SSMS
Express is free download, if you use SQL Server2005 Express. If you use full
version of SQL Server2005, you should have SSMS available.

 

[Vinod]

Thanks for immediate response.

Actually my requirements is, I need to have some tables from a database
which are existed in sql server 2005 (management studio/express). Later I
have to run some queries and needs to store the results in access data base.

In other words I need to generate some reports in Access, like database
resides in access. For this it requires input data from sql database.

Is there any other method (link table,etc.,) apart from ADP or ADP is best
one for my requirement?

Advanced Thanks
Vinod

 

[Norman Yuan]

ADP cannot have local database. It is a pure front end for SQL Server. Yes,
it is a good report tool, but you cannot have local data table, nor you can
design server object with Access2003 for SQL Server2005. You can use Access
ODBC link tables as SQL Server front end (*.mdb). In this case, you can have
both linked tables (in SQL Server) and local table. Actually, ADP is or is
closeat to, its end of life, and MS Access team itself recommended to use
*.mdb front end for SQL Server, instead of ADP since Access2003 released.

 

[Vinod]

Once again Thanks Norman.

Its really make sense.

I'm new in linking external databases. Could you please guide me how to
create create link table using odbc?

I tried like this, File|Get External data | Link tables. After this I
selected ODBC databases, from there I selected 'Test' (datasource) and
clicked on OK. A window is displayed with 'Link tables' which shows many
tables (I don't know what are those) but there is no table (student) which I
created in sql server 2005 .

Thanks,
Vinod