Questions from the Access to SQL Server migration webcast

  • Article

 

Asked: One of the most difficult tasks of the migrating process was the query upgratings. Most of them had to be all rewrited. PLease coment about queries and the sql of Access and sql of SQLServer.

Answered: Access and SQL Server use different SQL dialects. When using the SQL Upsizing Wizard in Access queries are not upsized to the server. Access uses Jet's query processor to automatically translate queries on-the-fly and then sends those queries to the server for processing.

 

Asked: Why is SSIS(formally DTS) not included with Express Adv Services? Is it only because it depends on the VS devenv.exe?

 

Answered: No, This was not a technical decision rather a marketing one.

 

Asked: how do I backup SQL Express?

Answered: You can backup SQL Express in a number of ways. You can use Management Studio Express, which was release at the same time as SQL Server SP1, you can use the T-SQL BACKUP DATABASE command (see Books Online for more info) or you can simply copy the .mdf file once it has been close or detached.

 

Asked: A number of Microsoft Desktop applications create separate copies of MSDE on my desktop. This results in several copies of SQLSRV all trying to take all available memory. Can I consolidate some of these along with my Access upgrade into one SQL Express instance rather than having so many instances of MSDE or SQL Express?

Answered: Yes, it is even recomended that you have multiple applications use a single instance of SQL Express. I recomend you use the default Instance Name of SQLEXPRESS. If you write several applications that use SQL Express, you can have your setup program check for the existance of the SQLEXPRESS and only install SQL Express if it doesn't already exist. (This is how Visual Studio ClickOnce deployment handles SQL installation.)

 

Asked: I am unable to locate Sql Server Express with Advanced Services in MSDN Subscriber Downloads. Is it still not available?

Answered: You can also download it from https://msdn.microsoft.com/vstudio/express/

 

Asked: I thought that linking to SQL Server backend from Access was extremely slow. Is this true?

Answered: This is not true. The Jet query processor will send all queries to the server that can be processed by the server. If queries contain elements (such as VBA expressions or heterogeneous joins) that cannot be processed on the server, then all or part of the query will be processed on the client.

 

Asked: I would like to know how to identify a way or a tool in order to help convert queries in Access.sql to SQLSERVER.sql.

Answered: I am not aware of any tools to convert Access SQL (Jet SQL) to SQL Server SQL (T-SQL).

 

Asked: Are there any plans for an Office 2003 Service Pack to support some of the unsupported SQL 2005 features?

 

Answered: SQL Server 2005 will be supported from Access 2007, but there are currently no plans for an Office 2003 Service Pack to add SQL Server 2005 support.

 

Asked: Do you think there would be a market for a tool to convert JetSQL to SqlSvrSQL, and would Microsoft object to its independent existence?

Answered: This conversion is done automatically when upsizing an Access database to an ADP. When upsizing to Linked Tables, the conversion is not required. A Jet SQL to T-SQL conversion tool would be necessary for the .1%-.3% of Access applications that need to be replatformed when upsized. That said, Microsoft encourages individual developers to extend our products by building tools they feel serve a need. It would be interesting to see this type of tool developed as a shared source project on https://www.codeplex.com.

 

Asked: In Access 2002 I have developed a form with a subform, trying to use Pass-through queries for the recordsource for the form and subform. I was unable to get the subform to work with a subform. Is this possible with SQL Server 2005 and Access 2003?

Answered: Because of the way that Access controls the main form/subform interaction through a parameterized query, you cannot base a subform on a pass-through query. This is because the pass-through query cannot be modified by Access to allow for the parameterization.

 

Asked: Can we set up a partial load like top 25 and then fill remainder after user is functional?

Answered: Access will show data as soon as the first "page" of data is available. The remainder of data in the data set will automatically be fetched in the background.

 

Asked: Can't the new CLR mimic VBA like style?

Answered: Yes, CLR stored procs and functions can be used this way. This functionality is available in Express

 

Asked: Which is the best approach to upsize from Access 2.0 to SQLServerExpress ?

Answered: The recommended approach is to upsize to Linked Tables. This allows your Access application to run unmodified after the upsize and then the application can be tuned using the suggestions in today's presentation.

 

Asked: Will MS add additional functionality into the Management Studio, such as a query designer, for SQLServer Express?

Answered: Query Editor is available in SQL SERver Management Studio Express

 

Asked: I currently use Access to retrieve data from a DB2 database. I will need to get data from SQL Server and from DB2. Is it possible to do this from an Access Project?

Answered: The Linked Tables that you currently use in your Access database are a feature of the Jet database engine and are not supported in Access projects. Since Access projects use SQL Server, you do have Linked Servers available to you, which are similar in that they let you access data from external sources. You can find out more about Linked Servers in Books Online.

 

Asked: What's the equivalent of forms in express? Would you write asp.net app for that?

Answered: SQL Express is only for storing data, it does not have a forms package. You can actually link you SQL Server tables into your existing Access application and use the Access forms. If you want to convert your entire application to a different platform, you can use Visual Studio to write either a client application using VB.NET or C# or you can write a web application using ASP.NET.

 

Asked: How would one upgrade from Express to standard or better SQL server?

Answered: You can simply detach your database file from SQL Express, move the database and log files to the new server and then attach them. Once attached to the new server, you change your connection string in Access to point to the new server and re-link your tables.