October 17, 2005 at 2:34 pm
I attempt connect to a SQL 2005 database using an MS-Access XP proyect (.ADP). I can execute read and write operations, but I'm not able to modify the database structure through .ADP
Is it a MS-Access XP/2003 limitation ?
Is there a patch for MS-Access allowing that ?
Guillermo
October 18, 2005 at 4:49 pm
I think you have to ensure that the SQL Server tables you are trying to modify through Access must have an Index, else you can't update.
I remember having this problem once, but can't find the reference - I think it was on the MSDN site.
Simon
October 18, 2005 at 7:55 pm
Simon:
I have not problems using SQL 7/2000. Only in 2005 edition.
I'm thinking that .ADPs can't work properly connected to SQL 2005
Look this article: http://www.databaseadvisors.com/gazette/sqlexpress.htm
Guillermo
October 18, 2005 at 8:24 pm
Sorry Guillermo - I didn't read your question properly. I was thinking of updates to data rather than schema.
Yes - reading that article doesn't look good. Sorry I can't be of help.
Simon
October 18, 2005 at 8:42 pm
Guillermo
I do not believe this is possible.
You need to read up on the use of DDL to create a script which will modify the backend tables, or use SQL's Enterprise manager.
I assume the tables are linked and it may be that you need to relink the tables before your ADP will recognise the amendments made to the structure.
Good luck
Ian B
October 19, 2005 at 12:28 pm
While I wait for Microsoft patch, I still using the .ADPs for backend tables management, as follow:
- I've installed one second SQL Server, bud this is 2000 edition
- I connect my .ADP to Server 2000 for development works
- Then, I move file.mdf and file.ldf to Server 2005, and reconnect the .ADP to this
By now, it is easier than using linked tables to Server 2005, because in this case I should need to apply Enterprise Manager for structure changes and to relink the tables each time.
January 2, 2006 at 10:34 am
In general Schema modification thru Access Project is dead.
Good luck waiting for the patch. From what I hear MS has abandonned Access Projects.
The reasons offered being managed code and datatype incompatibilities - bogus in my opinion.
I use SQL Server 2005 Management Studio for schema design and modification. This works for everything but the features that Access stores as extended properties (combo boxes on table columns definitions etc.).
SMS is much slower than Query Analyzer or Enterprise Manager, so add a little extra time to your schedules.
January 4, 2006 at 11:03 am
" From what I hear MS has abandonned Access Projects."
PLEASE, Stop spreading this terrible rumor! It is not true!
ADPs are alive and well in Access 12 (2006), and the MS Access team has stated publically that ADPs are getting all the relevant new features of Access 12 in the next version. VBA is also alive and well for many years to come, and .NET is being slowly tricked into Access over the next few versions. Access 12 will support limited intergration with .NET, particularly with the new task panes, the command ribbon, and web services. For forms, reports, modules, VBA will still be used. No code rewriting is required (for now anyway).
Access 12 (2006) will support SQL Server 2005. I highly doubt they will make fixes for the older Access versions, when they should be working on the next version.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply