From Access 2003 to SQL Server 2005
-
G'day,
I got a question which I hope someone can answer.
At the moment I am running Access 2003 at my work however my boss now wants a change over to SQL 2005.
My question is that at the moment in Access when the database is loaded up the user it presented with a form with various buttons to other forms. These forms include a Data-input form and a mail-merge to MS Word.
I downloaded the trial version of SQL 2005 and I yet can not see how one can create the interactivity that MS Access has. Is it is possible to have forms in SQL 2005. And is it possible to do mail merges to MS word in SQL 2005. I notice MS boasts there is great integration to MS Office with the business intelligence system but I fail to figure it out. Do I need to do this in Visual Studio or html?
Some starting pointers on how to create some interactivity & mail merge to MS word would be extremely greatful as I am a complete noob to SQL 2005.
Thanks
Karv
-
SQL Server is a database platform, not a GUI tool.
Access is both a GUI tool and a database platform - it stores, processes and presents data.
To use SQL Server in a similar way to what you have in Access, you will need a front-end application to display the SQL Server data and kick off data input, processing and output functions.
If you like, you can continue to use Access for this - or you can write an ad hoc .NET application to do exactly what you want with the data.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Thanks for that, I did know SQL server wasn't a GUI, I just didn't know which front-end application to use.
Now another newb question is ad hoc.Net part of Visual Studio or is it a client app I must purchase separately. Furthermore what is it like with Microsoft Office integration?
My apologies if my questions seem so obvious.
Thanks
Karv
-
Sorry about that - I should know better than to leave my replies strewn with Latin phrases.
Ad hoc means "for a specific purpose" - I meant it literally - it is not the name of a VS component
Expanding on this - if you want to write a standard Windows client app that accesses SQL Server data, you will probably write it in VB, VC#, VC++. A web app would probably use ASP.NET. There are many other languages and products available, but if you want to stick with VS ...
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
THanks mate, that makes more sense. Yeah I probally try something in VB then as it is the one I am most use to.
Thanks again
-
Presumably you could also use your existing MS Access forms/queries and have them source data from SQL Server 2005 instead of MS Access tables.
I'm working on a project to encourage customers not to use unsupported versions of MS Access and I would be grateful to hear from other users why it would be better to use VB, C+, C# or web instead of MS Access as a GUI.
Thanks
Alan
-
if you're looking for reasons not to use access then i can think of about a million.
aside from the obvious
security
performance
support requriements (have you seend how many times mdb files need repairing)
jet 4.0 bugs and service pack levels
DAO (not ado)
redistribution
Access is great for
low cost personal database
as a rapid prototyping system
as a small scale front end for say a 5 user application where the cost/time of deploying a VB/c# app is prohibitive
Developers looking to manipulate and correct data or prove principles
I think the gernal rule of thumb "should" be if you plan to have the system more than 1 year and you may have more than 5 concurrent users or backup/recoverability is an issue then use SQL and VB/ASP/C#
but hey these are only my opinions from personal experience of working in these environments (you wouldn't beleive where access pops up as a business critical system - e.g hospitals!!!!)
MVDBA
-
Yeah from experience Access seems to have its bugs especially when more than 5 users are using Access. The mail merge is also very buggy.
I have been doing some extra research into Visual Studio.Net & I have come acrosss something called crystal reports. Apparently it is possible to export crytal reports to Word.
What is everyones opinion on this function? Is it stable and can it perform data merges in the same way you can with the mail merge in MS Word & access?
Again help is appreciated before I get my boss to fork hundreds of $$ to purchase Visual Studio.Net.
Cheers
Karv
-
Somewhat less than a million if you consider that Access 2003 projects could use SQL Server 2005 so security it handled by SQL Server; performance then would also be SQL Server 2005 which is significantly better than an Access .mdb file; jet 4.0 would then be N/A; DAO is not the default for Access 2000 and above databases but ADO is; and redistribution is simiplified with connecting to SQL Server 2005.
Our poster knows Microsoft VBA which he could still choose to use. TSQL would then be the only new application technology to use.
I'm very familiar with the limitations of Access which are mostly confined to the database application .mdb. I most develop SQL Server applications now but also don't like to see misinformation about Access. Everyone needs to determine what to use based on facts.
-
i'm not sure why you're in such a mood scking, but basically i was saying
choose a solution that meets the scale of the problem
i agree with some of your comments, but definatley not all of it (especially the jet 4.0 comments - try office 2000 linked to mssql2000 with pre jet 4 service pack 8 and edit data directly on the table view!!!)
if you do build in access i'm sure you'll get most of the bugs sorted pretty quick and moving msaccess table tosql and changing the the client file to use linked tables to mssql is very easy (only a few minor code changes required (if using dynasets and dbseechanges) but i'd guess that within a year or two you may want to rethink that desicion and go back and ask for more money to rebuild
karv - if you want to contact me directly by email with any questions please feel free - i'm not against access in any wasy as i use it every day (and so do our internal staff), but it depends what solution you are building and who for!!
MVDBA
-
If you do decide to stick with Access, the functionality you are looking for is called linked tables. The basic steps required would be to export all your data tables from Access into your SQL server instance first (there are many ways to perform this), then to "Link" these tables into a copy of your current Access front end (File, get external data, link tables).
Once all your data is accessible, you will need to make adjustments to keep everything working. Probably, you will want to remove the account name from the linked tables to return them to what they were called before (example: Table Employees becomes dbo.Employees when linked if it was created/imported in SQL server by the default admin account "sa")
Next, you will need to perform testing on all your forms/procedures to make sure everything is compatible with your SQL linked tables.
For a smaller application, transferring the back end from Access forms to SQL server is not a terribly difficult project, especially if the amount of VB code is minimal.
There are a few things sure to give you some trouble, but a few google searches will usually be enough to figure them out.
Tips: Make sure a Primary key is set on EVERY table (make an auto-incrementing integer ID field a primary key if there is no other). Without this, Access can read but not update the data.
Do NOT use the bigint datatype in SQL Server. Access does not seem to support numbers that large and you will get errors with messages completely unrelated to this true cause.
This should be enough to get you started if this is the method you choose to pursue. There is plenty of info online to help you with the little snafu's that you may come across.
-
It is not my intent to be in a mood, Michael, but merely to advise. My comments were about Access and not Office interactivity. You are correct about Office 2000 using Jet but any 2000 VBA application such as office or Access can use either DAO or ADO when Jet is used or also access SQL Server databases.
I totally agree with your last paragraph. Although I've been developing Access applications since Access 1.0 when I just used macros, ugh, I generally develop any db application in SQL Server for a number of reasons: 1) Most of my applications have a tendency to grow and catch on as corporate applications so my sizing is set for growth, 2) performance improvement using disconnected recordsets with .NET 3) Access security has too many holes which SQL Server doesn't and 4) TSQL allows a user to build application code logic into the stored procedures and there is no comparison using Access queries. That's just a few.
-
I have been using Access since 2.0. I agree the mdb problems got realy bad with Access 97. The bloating was a problem and also corruption. I have migrated several Access databases to Access Projects with SQL backend. This is working good for all the production databases that were originally mdb's. The forms that were developed can basically be kept, just all the queries have to be re-written as stored procedures or views. This was a many month project, but have had no problems since they have been migrated. Also having all the databases in SQL allows for tighter security and backups arte a breeze.
-
does anyone know of a good Access to VB or .Net conversion tool?
-
Just went live with SQl express 2005.
We were using an access database with 20+ users and it was getting real SLOW.
The access data base was 40mb with 10000's of linked records. Most of the front end was VB code and we still use access for it. Data now resides on SQL with linked tables.
If not on XP upgrade to mdac 2.8 on any clients. Spent a day looking for that cure.
Viewing 15 posts - 1 through 15 (of 49 total)
You must be logged in to reply to this topic. Login to reply