October 6, 2008 at 12:36 pm
I recently converted the access database over to SQL Server 2005 but kept the access application in the front. Everything is working fine except for one scenario where all my tables are linked to SQL Server. I have changed the “Note” field data type to nvarchar(max) but still can’t input more than 255 characters. However, when I go to my link tables in the access table’s window and I right click on my table and click design view and it gives me a warning message “This table is a link table with some properties that can’t be modified. Do you want to open it anyway?” I click “Yes” and table design view opens up and I see the data type is set for 255 which access won’t let me change because it tells me the field size can’t me more than 255 but in the SQL Server I have it set to nvarchar(MAX). How do I overcome this and what’s missing? I have refreshed and re-linked my tables. I have played with the options at both places (access and SQL Server 2005). Any comments are appreciated.
October 6, 2008 at 1:14 pm
There's an option in the linked table manager to relink, but I've at times seen this just propagate issues like what you are dealing with. I'd suggest deleting the linked table entirely and then stepping through the wizard again to link the table. This may help you...
Additionally, there are inherent issues with linked tables you should be aware of, like if you join 2 linked tables, your Access front end will pull every record from each table before it does the join, resulting in bad index usage and network bandwidth issues. You may want to reconsider using linked tables in favor of pass-through SQL queries for your data access.
-Luke.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply