Microsoft Access wizard add-ins

[strucker]

Hello

I would like to start upsizeing a few databases but the Acces project wizard was not installed on my PC. I assume that they did the minimum installation. Is it possible to download the Wizards? Tried looking on microsofts website, but to no avail.


Nolan

 

[LogicalmanUS]

strucker,

If you are considering using the Upsizing Wizard for Access to SQL, DON'T.

For most databases it will be faster to write the SQL tables yourself and then import the data. I have not heard of a programmer or dba who has not got a horror story about this wizard. As per one of my previous replies, this wizard is as useful as a chocolate fireguard (looks nice, acts nice to begin with, but is not up to the job and leaves a complete mess).

I would suggest you look at asking questions in the Access and /or SQL forums as to what conversions you may need to make when creating the tables on SQL server.

Logicalman

 

[strucker]

Thanks!

I'm checking into creating the tables on SQL. Is there a difference between upsizing and creating a microsoft Access project? Could you use Access project to place tables and queries on SQL and then link them to the Access database? What would be the quickest way to convert to SQL. I need to do this asap!!!!!

Nolan

 

[LogicalmanUS]

strucker,

Excellent proposal. I tend to use Enterprise Manager, but I cannot see too many problems, in theory, with doing this via Access Project. You should create the database first, and then link to an existing DB using Project.
You may also need to use EM though to set securiy on the database.

Please keep me informed of your progress, I'm intrigued.

Logicalman

 

[BobJacksonNCI]

I tried...
and tried...
and gave up because the upsizing wizard created tables and moved data just fine, but found reasons NOT to handle any queries. I'm using Access 2000 with SQL 7.

However, I did research this and read that Access XP has improved the wizard a great deal and converts queries to stored procedures. Uh, in case you weren't aware, Access Projects to not have queries, just query capability for selecting data. Upgrading was not in our budget so we're doing as LogicalmanUS suggested.

Naturally, I'm marking this thread for notification.
Bob

Please view Thread181-473997 for helpful hints regarding this site.

 

[LogicalmanUS]

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 -

http://www.microsoft.com/sql/downloads/2000/sp3.asp

), 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