0

I have a MS Access 2010 Data Project (adp) connected to MS SQL Server 2008. Based on Microsoft's website, I should be able to modify the SQL backend from within the MS Access 2010 Data Project.

Once you connect to an SQL Server database, you can view, create, modify, and delete tables, views, stored procedures, user-defined functions, and database diagrams by using the Database Designer, Table Designer, Query Designer, Query Builder, and SQL Text Editor.

However, I can't modify all my columns the way I want to. Let's say I have a table with

Col1 varchar(255)

I can't change that in Design View to varchar(max). Max is not an accepted length in MS Access. Also, per Microsoft's documentation, text should not be used, since it's deprecated. So, I would like to avoid that as a solution.

Another option would be to make the change directly on SQL Server. However, this won't work for me. There will be other developers working on this. None of the other developers will have access to SSMS. Mostly because they're Access developers, who have never seen SSMS or even touched SQL Server before.

How can I make this change in MS Access Data Project? Are there any other gotchas that I should be aware of with this client/server model?

TTT
  • 21
  • 3

1 Answers1

1

There are a few field type comparability issues that you have to work around when using access to design tables and other SQL Server objects. Generally I've found Access to be a very useful front end client for data entry forms, programs, and reports on SQL Server data. However, I always use SSMS for creating tables, procedures, views... and all SQL Server related development tasks.

As for your table design issue, it looks like most activities are supported except for a few data type issues. One option could be to have your developers send ad-hoc T-SQL statements to the server to create and alter tables that have unsupported field requirements. One way to do this would be to create an access form with a text box to enter the T-SQL statement and then a click of a button calls some VBA to execute on the server with CurrentProject.Connection.Execute(SQLStatement). Although this wouldn't be a pretty table design GUI and the developer would have to know the T-SQL syntax to do what was required.

My recommendation is to just get your developers SSMS and some training. It's really not that much more complicated than using the Access table design interface... in fact it's very similar. They will certainly pick it up very quickly. Plus you will be leading them on a path to being informed and productive SQL Server developers.

Brian Pressler
  • 6,653
  • 2
  • 19
  • 40