June 7, 2006 at 2:49 am
Hi,
I'm trying to upgrade an access database to SQL server - could anyone advise whether it is better to upgrade to an Access project or an Access database?
One of my reasons for upgrading are that there are too many users logging onto the database - not sure if that influences things one way or the other?
thanks
Steve
June 7, 2006 at 4:00 am
I cant see a reason why you wouldnt want to use the Project rather than the normal access DB.
When it comes to using a project you get the benefit of being able to use and link forms etc to stored procedures. But you may run into a few problems if you have complex procedures on your forms report and queries! But this still wouldnt take long to mess about with re writing stuff in Stored procedures instead of queries etc.
The biggest benefit of all using projects is the huge increase in performance. as it can use stored procs instead of local run queries!
Thanks
Antony
June 7, 2006 at 4:20 am
OK - thanks for this Antony - I'm new to SQL server & just needed a bit of guidance on this.
June 7, 2006 at 8:14 am
There are legitimate reasons for using an MDB over an ADP.
1) Local data storage. If you're using an ADP, all data must be stored in the SQL Server. No local temp tables, import tables, config tables, etc.
3) Minimal conversion time. You pretty much just move your tables into SQL Server, then link to them in the MDB, and you're done. A little tweaking for changes in how certain datatypes are handled, and a little tweaking to make sure the connection is robust are about all you need.
2) Learning curve. Not only do you have to rewrite all your queries into SQL Server format, but forms and reports will act differently, too (and have some new properties). This isn't a huge deal, but be aware that there are precious few resources out there for ADP work specifically. Most Access books devote about half a chapter to it, if you're lucky. I've only ever found one training program devoted to it.
There are excellent reasons for using an ADP over an MDB, too.
1) Security. This is the biggest one. ADP projects pretty much completely rely on SQL Server's security. Which is orders of magnitude better than Access'. Because all of the data, and all of the queries, reside on SQL Server, pretty much the only thing you're protecting in Access is your code.
2) Performance. This is true in a lot of ways, but the biggest is that the SQL Server engine is superior to the Jet engine. With an ADP, you don't touch the Jet engine at all. Also, you'll be using ADO in your code instead of DAO, and ADO is better.
3) Clarity. There is a clear break between what's going on on the server, and what's going on on the client. This makes it easier to debug problems, as well as being a proper client-server architecture.
4) Flexibility. Remember some of those new properties I mentioned before? Some of them are really handy. The Input Parameters property of your forms can replace a lot of your Filter code.
5) Power. With an MDB, you can use a pass-through query to execute a stored procedure. But, to me, that always feels like those boxes you see doctors use, where they're working on hazardous material by sticking their hands into those gloves. It works, especially once you get used to it, but it's not ideal. With an ADP, you're working with stored procedures (and UDFs) directly, which allows you to really leverage the power of T-SQL.
June 8, 2006 at 5:58 am
Hmm.
1. Security. Unless you make a mess of it, then an mdb uses SQL security to access data etc held on a server.
3. Clarity. You should have a clear front end back end split whichever method you use.
The basic reason I've found to use an mdb is when you have mutli table views that need triggers to update. A project talks about the main table and stops dead because it doesn't use your triggers. An mdb works fine once you define the primary key.
Oh and all the code is on the server (triggers and views) and you have a clear front end back end split because all the user should see is the view not the tables.
June 8, 2006 at 6:22 am
I think this has in recent times become an easy question to answer: Access projects (adps) appear to be going nowhere. AFAIK, they're unable to connect to SQL Server 2005 databases (either at all, or in 2005 mode), and I have read they are not going to be supported in the future. I have no hesitation in recommending continued use of the .mdb, even though a year ago I was preaching otherwise!
June 8, 2006 at 6:24 am
My clarity point was that, in an MDB, your queries, or at least parts of them, exist in the front end. The same queries that you often use as datasources for your forms and reports. Which, IMHO, tends to muddy the line between front end and back end. Others have disagreed with me, saying that the queries are actually code, and therefore belong in the front end. I guess it depends on what you consider to be the dividing line between the two.
I don't understand your comments about triggers. I've used triggers in projects without issues.
June 8, 2006 at 7:19 am
Is an mdb quicker to set up? (or rather, does it involve less programming)
The main reason that I'm upgrading is because of performance issues. If I get >8-10 users logging on to the access database at one time then it crashes.
Would setting up an mdb solve the performance issues?
June 8, 2006 at 8:08 am
Point is if you have two tables linked 1 to 1 you need a view and a trigger to do inserts and such. It doesn't work in a project AFAIK. It does work in an MDB because you can specifically define the primary key and it behaves itself.
June 8, 2006 at 8:27 am
There's not going to be a clear-cut answer. Different developers are going to have their preferences based on their unique experiences. Here is my input.
I have written and support several niche database applications using the traditional split MDB (separate backend and front end). In recent years several of those have had their backends moved to SQL Server, with little effort. I have also written and support several Access Projects.
About 7 years ago, I was part of a development team that created a cost/billing application using Access97 (split MDB). The application initially was to be installed on local area networks, and accessed only by local users. Once the application went into service, upper management decided they wanted one database for the whole company, not several database at different locations. A management decision was made to move the front end to Access2000 and the backend to SQL Server 2000. With very little information available at the time on the new Access Project format, the decision was also made to use a Project instead of an Access database as the front end.
This proved to be a costly decision. With hundreds of complex queries and reports, we found ourselves in a three month long project doing the conversion. Then, due to the number of objects that had to be touched, the application had to be extensively retested.
Had a MDB been chosen as the front end, the project would have taken about a week, and very little testing would have been required. I am convinced that, had the amount of conversion required been known up front, a MDB would have been chosen instead of a Project. However, the Project has served us well, and still is. No one is sorry we went with the Project option. I am sure performance for users has been better with the Project than it would have been with a MDB. Access Projects use ADO to communicate directly with SQL Server. MDBs can use either ADO or DAO to communication with SQL Server through ODBC.
In the long run, I have been happy supporting this Access Project. The conversion and retesting was the painful part.
Some of the problems you need to know about if you are considering converting a MDB to a Project are as follows:
* IIF(x,y,z) functions are not supported by SQL Server. For use in Views, Stored Procedures, and UDFs, they have to be converted to Case statements.
* Any Access Report or Form control that contains an Aggregate Function that is evaluating a complex statement must have the complex statement moved to the record source. For example: Sum([FieldA] + [FieldB]) is perfectly fine on a MDB report. However, on an ADP report this will fail. The record source must be modified to include [FieldA]+[FieldB] as SomeFieldName. Then the report changed to Sum([SomeFieldName]). Another example report control source: Sum(iif([Included], [BillAmount], 0)) must be modified to Sum([IncludedAmount]) and the backend record source be modified to include a column: (CASE WHEN [Included]<>0 THEN [BillAmount] ELSE 0 END) As IncludedAmount.
* Any Access Report with a complex statement as a Sort/Group field must have that complex statement moved to the record source. So if the report is Grouping on [Company]+[JobNo] as one group level, the record source must be changed to include a column: [Company]+[JobNo] as [CompanyJobNo], and the group level source set to [CompanyJobNo].
In my opinion, these are the big issues. If your application does not have a lot of these sorts of things you can easily move to a Project. If it does have them, use a MDB as the front end or plan for time to convert and test. It’s your choice.
June 9, 2006 at 5:39 pm
I have been playing around with SQL Server Express and you can connect an ADP to an Express Database. You can't modify any of the back end objects in the ADP, but if you have the SQL Server Management Studio Express CTP installed (which is free) you can make all your changes there and then just use the ADP for your front end functionality. You can also use an MDB as a front end to SQL Server databases without linking the tables or having any front end queries but to do this all of your data access has to be through ADO - a hefty code overload but it is workable and you can even have disconnected recordsets allowing you to have off-site databases which reconnect and update the main database at a later date. Here's a link that gives you a good grounding for doing this:
http://www.quepublishing.com/content/images/0789729520/samplechapter/CH30_0789729520.pdf
The pain is handling reports as you can't set the recordset property for them, so you need to use either local tables, linked tables or SQL Pass Through queries... so it ends up a funny mix but there are some interesting possibilities there.
June 13, 2006 at 10:09 am
I think that the answer is actually pretty easy these days: Use MDB.
ADP's, although they can use SQL Server 2005 data, do not allow you to make any table changes, so you get nothing on that score. Someone else mentioned that being able to store queries and some tables locally, which you cannot do in an ADP, has some value. If you're starting from scratch that's not such a big consideration.
Most of all, I cannot see any indication that ADP's are going to be with us forever except "for compatibility".
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply