Upsize from Access to SQL Server

[notsirk]

Hello!

We are in need of upsizing our Access database (built on 2010 but I'm still on 2007) to SQL Server (I believe we have 2008 but it could be 2012). I'm told it's relatively easy but I am fairly new to Access and I know practically nothing about SQL. I've been reading about it on Microsoft's website but it's not terribly clear and I wanted to get an idea of any potential pitfalls before moving forward.

I'm looking to continue using Access as the front end and just move (most of) the tables to SQL for the back end. This would be for my group's use with the current Db but I'm also mulling over writing a separate Access front end to distribute to other groups in my organization which would use data from these same SQL tables. Here are my questions:

1. Is it safe to allow many copies of the same Access Db to connect to the SQL Db? This would potentially be more than 100 but less than 10 frequent users. I'm not asking from a security standpoint but from a stability one.

2. Are there any dangers to table/ query relationships? How about between local and linked tables?
3. According to Microsoft, queries that are “too deeply nested” may need to be manually converted. How do I know if a query is deeply nested?
4. Are there any other potential pitfalls with queries? I do not have any crosstab, pass-through, or data definition queries (which are warned against).
5. Are there any concerns with data that have different decimal lengths in a given field (i.e. 0.03 in one record vs. 0.022 in the next)? It gave me a bit of trouble just within Access...



Thanks in advance for your expertise!

 

[mdlueck]

I do not have any crosstab, pass-through, or data definition queries (which are warned against).

WHERE did you encounter such a warning?!?!?!?!?

Here is how I am using Access and SQL Server in a Client/Server environment, with great success.

Client/Server Architecture
http://www.access-programmers.co.uk/forums/showpost.php?p=1110794&postcount=5

 

[notsirk]

Note You might need to manually convert queries that did not upsize, such as SQL pass-through queries, data definition queries, and crosstab queries. You might also have to manually upsize queries that were nested too deeply.

This is from MS: http://office.microsoft.com/en-us/a...by-using-the-upsizing-wizard-HA010275537.aspx

 

[mdlueck]

Access by itself can not execute a pass-through query. By design, Access must download all records it needs to consider in performing the query and process those records right within Access running on the client computer.

So, "might need to convert pass-through queries by hand" does not make sense in the context of up-sizing to a SQL BE DB.

Even if it did make sense, still it is not a valid reason to choose the harder path of making queries "work" with two query interpreters involved... Access + the SQL BE DB.

I recommend ALL pass-through queries, as outlined in my development design overview I shared the direct URL to above, when up-sizing to Client/Server.