Learning sql server 2003

[rcollins]

I have all of my databases currently in access. I am going to switch them so the back end is sql and the front end access. What would be the easiest way to go about implementing this? At the moment, I have a "play server" that I am using to learn so I dont screw up anything. My boss would like to see me get the whole system changed before I build anymore dtatabases. Any help is appreciated.

[Jim Doherty]

I have all of my databases currently in access. I am going to switch them so the back end is sql and the front end access. What would be the easiest way to go about implementing this? At the moment, I have a "play server" that I am using to learn so I dont screw up anything. My boss would like to see me get the whole system changed before I build anymore dtatabases. Any help is appreciated.

If you are going to upsize to SQL Server a suggestion to you is to at least have a look at the ADP project file format within Access to communicate directly with SQL Server particularly if you have no need to store tables locally on your machine.

You can use an mdb file... of course you can, its back in favour for communicating with SQL server if you are intending to use MS Access 2007 however the ADP format was heavily promoted at one time as the 'way to go' I personally prefer it, as it exposes views and stored procedures and other methods in the frontend interface for retrieving data that is kept on the server, thus reducing network traffic and communicates using UDL (universal data link) as opposed to ODBC which is the way an mdb using 'passthrough' queries fetches its data

As always its a matter of preference and choice

Jim :)

[rcollins]

We will be switching to office 2007 sometime (my boss said that is when he retires hehe) so whatever I do I want to make sure it will work with both. At this moment, I have no training with office 2007 but have MS certs in access and excel. I am not familiar with ADP project file format, do I need to switch all of my databases to this format? Can they all be done at once?

[Jim Doherty]

We will be switching to office 2007 sometime (my boss said that is when he retires hehe) so whatever I do I want to make sure it will work with both. At this moment, I have no training with office 2007 but have MS certs in access and excel. I am not familiar with ADP project file format, do I need to switch all of my databases to this format? Can they all be done at once?

Well to be frank I mentioned it as an option for you to consider it may be that your are so far down the development route that staying with the mdb is your only feasible option I don't know. Converting from one to another is no mean feat, depends how many objects ie forms reports etc you have. Having said that you are going to have to deal with various amendments to your rejig anyway you want to harness passthrough queries.

I know that most of the time when I do a conversion I tend to rewrite the whole thing anyway simply because much of what exists is either redundant, of no real use or simply old hat.

I'd have a serious look at building yourself a simple ADP project you might like it and take the view "Hey this is no big deal and I like the way I can write stored procedures directly fom the GUI tis up to you.

Regards

Jim :)

[rcollins]

So, My boss has been here for over 7 years. The guy who built these databases was before him. These are used company wide, with close to 200 users (give or take) that do differernt jobs and access different parts of the database. Is it possible to do parts at a time? Will it make a difference that most of our databases fall back to one particular database? We really want to change to sql because of our databases just keep growing, in size and in user front ends.

[Jim Doherty]

So, My boss has been here for over 7 years. The guy who built these databases was before him. These are used company wide, with close to 200 users (give or take) that do differernt jobs and access different parts of the database. Is it possible to do parts at a time? Will it make a difference that most of our databases fall back to one particular database? We really want to change to sql because of our databases just keep growing, in size and in user front ends.

Consider seriously the data model for your companies data, basic things like "Actually what is it that we do" is one of the best places to start. Organise your data into it relevant entities, people, places, commodities or products. These entities or items of data usually can be kept separate yet linked together in meaningful way without unnecessary re-keying of data. All of this separation should be agreed and understood by the people involved and allow them a sense of ' Hey I feel I belong to this development and can freely contribute to it'

If you break your data down into its smallest logical parts you'll find its not really rocket science much of it is plain old common sense. You will be saying to yourself things like:
"Why is that guy inputting clients into that 'complaints' database over there on an excel spreadsheet? when someone else is inputting the same client maybe spelt differently into the invoices database right next door (on maybe yet another spreadsheet with no validatory rules and so on) and neither of them speak to each other or pick up the phone?"

If your boss is happy to give you a blank canvas and invest in you as a single developer that is a developers dream. You have the opportunity to rewrite the rule book so to speak and present a more efficient model of how data should properly flow and present a more scalable database structure.

SQL Server should easily handle your companies requirements and thus make your data centralised more manageable and provide for easier backing up of data that is important to you.

As a single developer my advice FWIW would be concentrate on a 'deliverable' it need only be a single area to start with lets say an 'Address' database. The minute you can show that as being searchable across your network accessible by all your users they will will be saying things like "Hey we havent had that before, that looks great can I hook up to that and use it? when the answer is "yes heres the button to link its reference number to your small database system" then I feel someone is going to see the advantage particularly when they haven't had to type it in themselves. This is purely an illustration you realise, but I'm sure you understand what I mean.

The other alternative is to hire a huge company to do it for you who can offer faster bespoke software delivery times, resilience of support (what happens if you are not around) and so on. Is that the sound of big bucks? or the boss galloping off into the distance on his horse? LOL :)

Good luck

Jim :)

[rcollins]

Thank you for all of the information. I know that this is going to be a big step. Have you ever used the upsizing wizard? I heard this is supposed to be pretty easy to use. If I used something like this, then it seems like everyone could still use all of the databases while I work on the changes to the front ends.

[Jim Doherty]

Thank you for all of the information. I know that this is going to be a big step. Have you ever used the upsizing wizard? I heard this is supposed to be pretty easy to use. If I used something like this, then it seems like everyone could still use all of the databases while I work on the changes to the front ends.

The upsizing wizard for me personally is a lazy mans table creator, Its a nice tool but don't rely on it. There is a whole lot to learn with SQL Server than simply upsizing and thinking "hey thats it. I have an MS SQL Server database". YOU DON'T!! short and simple It doesnt stop there, in actual fact having your tables there is a very small part of a successful application.

I'd strongly suggest you familarise yourself with the fundamentals of tables in SQL server. Learn the datatypes, learn the elements of each one what are the pitfalls what are the limitations and so on. How do they differ from those in Access and why!! Why does SQL server store data and time together as one datatype for instance. Access doesnt and so on.

Pay heed to field sizes, correct indexing on optimally sized columns. Let's face it the data is the most important thing in all of this and if you get the foundation correct, it will become hopefully good habit to you.

If you do use the upsizer then watch, for instance, how it makes all text data types NVARCHAR by default. If you have no need for internationalisation in your application then stick with VARCHAR as the datatype. I won't go into the reasons why, unless you want me too you'll find out for yourself soon enough once you get your head round how to use SQL server properly.

Like I say takes things slowly for yourself (you'll speed up soon enough.) Better one person knows a lot than one person knows a little of a lot. Invest in a good technical book and beyond that this is in danger of becoming a sermon :))

Regards

Jim :)