BobJacksonWcom,
Thanks for your input, and you're right, it is rumoured that the Upsizing Wizard was improved, unfortunately, from previous experience I am not in the habit of making the same mistake again (once bitten .. etc)
The main problem we had with the upsizing wizard were the data types.
For text, Access uses the Text data type. This is limited to 255. If you want more, you go to to Memo, but this acts like a BLOB (aka Bloody Large OBject!!) and, like SQL does with it's Text data taype, the actual item stored is simply a reference to where the data is actually stored.
SQL utilizes a larger range of data types for both numerical and character data, and therefore you can design a more specific schema for your tables.
In respect of the queries and Project, again you are right in that the SQL equivilent to Queries are Views. But the one thing that sets SQL well apart from Access are the Stored Procedures, SProcs. These are very, very powerful pieces of code. In a similar fashion to writing VBA code, TSQL code can create hash tables when dealing with data. In Access we need to cerate Recordset Objects to handle the same thing.
Then comes the security aspect. Whereby Access security, when implemented fully use local MDW files, SQL stores all it's security data on the server, and, with SQL2K, you can now assign permissions for logins right down to column level. This is a useful feature for being able to store confidential data together with non-confidential data, in the same table (for example SSN together with Last names), and only allow logins that are members of a particular group permissions to view the SSN column, but allow all other logins to view the names.
The above items, together with things like SQL Agent, DTS etc, demonstrate the difference between a full Enterprise Database and a local (or desktop) database.
Access was the leading db for users in the 90's, now MS are moving us into the SQL server era with their limited desktop edition (still free -
), and, I have no doubt, will stop support for access in the not too distant future (by that I mean no more editions), and let Access die off naturally.
I have used Access (both 97 and 2K) for Corporation Use, and allowing access via the Company Intranet, together with a SQL backend, we were able to get round the problem of multiple Access users slowing down the database. We can have, literally, hundreds of users accessing the same front end (virtually) without any impact on performance.
In any case, welcome all Access users who take the (inevitable) plunge into SQL Server. Your efforts will be rewarded with a much more stable system, a learning curve (not always a bad thing), and pure enjoyment as you see and learn the power of THE best Enterprise DBMS.
And remember, if you have ANY questions at all, just ask in the SQL Forums, no question is too simple, we all had to start somewhere!
Logicalman
