Post reply

Access / SQL2005

  • November 16, 2006 at 12:24 pm

    #72189

    Hello All

    I apologize if this posts twice.

    I am primarily an Access guy. I am in the process of converting my data to a SQL2005 back end and will keep an Access02 front end. I do most of my external data connecting using ADO or DSN/ODBC connections.

    A comment was made to me that SQL2005 and Access do not play well togther due to a SQL translation issue and will cause many performance issues. Nothing more specific was given to me.

    This makes no sense to me.

    Can anyone give me some commentary on Access front ends and SQL2005 backends. Has anyone heard specifically of what i mentioned. I would love to hear feedback from all who have done Access/SQL combinations.

    Thanks Guys

    Peter

     

  • November 17, 2006 at 8:09 am

    #672622

    I use SQL2000 and Access front ends in many applications...works great.  I have tested the apps with SQL2005, but don't have any in production.  I am not sure what translation issue they are referring to, I did find that moving some parts of complex queries to views or procs in SQL was helpful in improving performance.

    I use DAO, ADO and DSN/ODBC connections...depending on the original app that was moved from an Access backend to a SQL backend.  The improved security and backup as well as triggers for SOXX audits was a main driver for the switch.

    Mike

  • November 17, 2006 at 8:22 am

    #672631

    Thanks Mike

    The little bit i found on the web was problems with .adp's and SQL2005. But i have yet to find an exact comment on what the problem is. There was some mention of not being able to create database objects (tables, views etc.) on the server with SQL2005 and Access. I don't create many objects on tyhe fly and if i do they are temp and i create them locally and throw them away. I also don't use adp's.

    As i mentioned i will mainly connect via linked tables or ADO. I don't understand why i would have any problem doing that.

    Any additional feedback is still welcome from all.

    Thanks Folks

     

     

     

  • November 17, 2006 at 9:30 am

    #672662

    AFAIK the only thing you need is to install the "SQL Native Client" so Access can talk to SQL 2005.

  • November 17, 2006 at 11:00 am

    #672703

    Issues I've encountered:

    1) Access' hyperlink datatype is not supported in SQL Server. (It is converted to text.) Access systems that use hyperlink fields will lose that functionality. There are workarounds for the read-and-open aspect, but so far I've not found a way to replace the functionality that Access provides for browsing to and creating new hyperlinks.

    2) The Memo data type in Access is converted to text data type by default. I have found recommendations to convert this to VARCHAR(MAX) instead.

    3) I have encountered a performance issue in a table with 800,000 records. Adding/deleting worked virtually instantly when the table was local (all in the front end Access database) but slowed dramatically upon moving the table to the backend. Played with indexes and foreign keys to try to improve performance but no luck. The users now have to suffer with a slower app, which is the opposite direction we want to go.

    4) Code might need to be rewritten. If you're using Access functions that apply only to local tables (eg. Seek), those will break when the table is changed to a linked one. So far, it has been painful but not impossible to create workarounds.

    5) All tables on the back end must have a primary key. In Access, it is possible (and sometimes desirable of course) that a table might not have/need a primary key. In order to link it, you will have to restructure the table to add a primary key if it does not have one prior to moving it to the back end.

    6) Tables with an IDENTITY (AutoIncrement) field behave differently in SQL Server than in Access. Getting the ID value of a newly-added record is more difficult in SQL Server and the code you use for doing so probably will have to be changed.

    I too am a newbie with SQL Server, having only worked with it for a couple of months. I expect to find even more issues/problems as time goes on.

  • November 17, 2006 at 11:24 am

    #672710

    Thanks again everyone for the input.

     

    Keep it coming!!!!!!!!

     

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply