February 14, 2006 at 2:55 pm
Hi everyone, can anyone help me with this issue, I am a database administrator for an MS access application with a back end file and 35 front ends, and the company decided to upsize to sql server, although I am new with sql, but I was told that only the back end file is converted into SQL and the users can work normally without changing anything to the queries, objects …, is that true ? and they asked me about the SQL server license, which edition do we buy, developers, standard … and do we need to buy sql clients also or is it sufficient to buy the SQL server and link to MS access front ends, thanks for replying, you will be seeing me a lot here in this forum J thanks …
February 16, 2006 at 6:18 am
I have found there are a few things to watch out for when moving your tables into SQL, but for the most part what you say should be correct.
Do not use datatype bigint in SQL as Access does not have a corresponding type.
Make sure EVERY Table in SQL has a unique key and Access knows what it is! (Do this when originally linking the tables if I'm not mistaken). This can be a new unused identity field in SQL if a table doesn't already have one. This prevents problems with unable to update/delete some rows.
Watch out for forms/procedures performing updates on joined tables, I believe Access may be less picky in allowing updates in this area (can someone confirm?)
Under most SQL Licensing models, you will need client access licenses available for each device that accesses SQL Server. I believe that users may get an "access denied, unable to login to server" sort of message if all the licenses are currently in use (can someone confirm?)
February 16, 2006 at 6:40 am
but I was told that only the back end file is converted into SQL and the users can work normally without changing anything to the queries, objects
I went through the process years ago and while the changes are minimal, it is unlikely that you will escape without having to make ANY updates.
Scott
February 16, 2006 at 7:47 am
Someone correct me if I'm wrong, but I think that you will want (at least) standard edition. Also, I am pretty sure that if you will be connecting to the SQL server from MSAccess via an ODBC DSN link that you can use the same user name and password for all of these connections and you will only be using a single CAL. ALso, from my experience, I have found that creating a file based DSN that is on a server where all of the users machines can access it will make it much easier to manage instead of creating individual DSNs on each cllient machine.....
February 16, 2006 at 10:12 am
Can't address the SQL licensce issue, but having just converted an Access backend to SQL, there are some things to watch out for.
We are using both file and machine ODBC DSN links, depending on where the application is executed from; either one will work. Our SQL server is hosted by another company, and they have been dealing with the licensing issues, so I'm not aware of the specifics there.
We have a lot of complex queries with multiple table joins. Running under Access 2000, these queries caused the database to lock up and go non-responsive, requiring us to end it using Task Manager - not a great solution from a user perspective, and of course whatever they were attempting to do involving those queries could not be done. We have needed to move large numbers of queries up to SQL server views to get the database performing at a respectable level. Some queries that have not been moved up to SQL are still very slow to respond, impacting the users productivity.
Also, look out for Access Yes/No fields that are translated to "bit" fields in SQL. If any bit fields in SQL contain a null value, you won't be able to update the record they exist on using Access/ODBC. (There have been other posts in the forums regarding bit fields, and I agree with them - it's a two-value field, not a three-value field!)
So depending on the design of your database, you may need to do significant rework of queries. We converted in mid-December 2005, and two months later I'm still optimizing queries (we're also moving to web-based entry, so I have not been able to focus on the optimization segment).
The good news is that copying the Access SQL statement to Enterprise Manager views works fairly well, as long as you make a stop in Notepad or other editor along the way - change double-quotes to single quotes, Yes or No to 1 or zero respectively, and the asterisk (*) in the "LIKE" statement to a percent sign (%).
Good luck with your conversion!
Steph Brown
February 16, 2006 at 3:07 pm
Thanks everyone for your help, stephanie when you said you neede to convert most of your complex queries into SQL, i have two questions :
- how do you define if a query is complex, is it by the number of tables it had ?
and converting the query is as simple as copy its SQL view from access and paste it into SQL ?
- One the query is converted into SQL server, how do i link it to all the users or should i create it on all the pc's, on access i create query on the machines and then import it from machine to another, but in SQL how it works and thankss ...
February 17, 2006 at 7:34 am
I also just went through a similar migration, although it was planned that way from the beginning. We did most of the prototyping in Access 2000, then moved the tables for each module to SQL Server as the module matured.
It has worked well so far using ODBC connections.
In addition to the things Stephanie mentions, you need to look out for parameterized queries. They don't convert to views.
Creating a view from an Access query is pretty straightforward. Open the query in SQL view, copy out the SQL string. Go to Enterprise Manager and create a view, paste the SQL string into it and save it. There are some syntax differences, so you do need to be on your toes.
Then, from Access, link to the view just as you do other tables.
George
February 17, 2006 at 10:10 am
CManti,
George covered some of your questions pretty well, so I won't repeat.
Complex queries (from my experience with our data) are generally those with three or more tables that contain large numbers of records - approximately 8,000 or more per table. Several of our main tables contain over 19,000 records, and when we start joining several of those table we see severe slowdowns and lockups (these tables are one record per "entity", by the way - if we start linking the ones with multiple records per entity we experience mixed results). We've also experience difficulty with queries where a table is self-joined, so apparently that qualifies as complex as well.
Also, if the query used a called function as a criteria (where the value returned by the function dictates which records are selected) we experienced the lock-up behaviour. Moving the query up to SQL with the other (non-function-related) criteria, linking to the new view, and then having the query pull data from the view using the function criteria worked, but it can still be slow.
With our system, we have a single "master" database that we ship to the other offices. Prior to sending it, we re-link the tables and views using either the file dsn or the machine dsn, as appropriate for that office setup, then we ftp it out to the offices and install it on their machines.
Hope this helps with your planning and execution.
Steph Brown
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply