How similar is SQL Server to MS Access?
-
Hi all. I am in the process of self learning SQL Server. I have 7 years DBA experience with Access. So far, I see alot of similarities. Am I correct? What are some of the major differences? Any downfalls? Any suggestions for me?
Thanks!
-
There are numerous differences. If you have an Access background I would recommend Alison Balter's "Mastering Access 2002 Enterprise Development". The book is written from the Access perspective and a large portion of the book is about converting to SQL Server.
-
The main difference is that access is a file-based database. There's no server-sde components, everything runs at the client.
SQL Server is a full database server. Even if there are no users there are still processes running. Queries run at the server and results are sent to the client. You can schedule jobs t un when you want.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Top 10 differences between SQL Server and Access:
10) Memory management
9) SQL can handle bigger size databases
8) Extra server side components for SQL Server (i.e.like Analysis services, DTS, etc)
7) You can backup up SQL server db while in use
6) You can really "performance tune" SQL server
5) SQL has a server side component
4) SQL has more security features
3) Performance
2) SQL built for multiple users
1) "SQL Server" on your resume looks cooler.
Have a good day,Norene Malaney
-
The best book for somebody with your background:
Microsoft Access Developer's Guide to SQL Server, by Chipman and Baron, published by Sams. . .
http://www.wvmitchell.com/library/details/243.html
-
Everyone's talking differences, and they are all good points, but one very important similarity: the "rules" for determining tables, primary and foreign keys, etc, are the same.
-
For me, personally, the biggest sticking point was understanding the differences between views, stored procedures, and user-defined functions. In Access, these are all accomplished with either a generic query, or, in some cases, through a bit of VB code and a recordset.
Along similar lines, T-SQL is *much* more powerful than Access SQL. But, there are some syntax differences. Single quote versus double quote, the differences in ISNULL, etc. You will likely get frustrated at some point with forgetting which syntax you're supposed to be working with.
You'll also need to break yourself of a few habits. For instance, SQL Server queries can't see your front-end, so you have to pass the values of textboxes on your form as parameters, rather than just referencing them directly (e.g. Forms!frmMain!txtUserName).
-
Thanks guys for all of your help! Lot's of good points. I am in the beginning stages and so far so good.
Also, thanks for pointing out the fact about "referencing textboxes" Marshall. Ya, that will suck a little compared to VBA, but I guess I will get used to it.
I guess what I meant to say is that all in all, database management is the same, but the syntax will be a little different. And ya, I will probably be mixing up VBA and T-SQL for a while!
Once again, Thanks everyone!
-
That's exactly my issue how do I pass parameters to SQL Server that are currently referenced in forms. A bit of code snippet would be helpful for me. I have many years programming and developing MS-Access Applications. Any help would be appreciated. New to this forum and SQL Server Express which I have already successfully installed and used the MS-Access migration tool which did not convert many of my access queries. I have spent 1/2 day converting iif statements to case and trying to figure out this parameter issue. If more detail is needed from me please do not hesitate to ask me.
Thanks,
~Annette
-
I am needing to learn more about parameters too but have found some MSDN Articles:
http://msdn2.microsoft.com/en-us/library/aa196138.aspx
-
annette_curtis (2/29/2008)
That's exactly my issue how do I pass parameters to SQL Server that are currently referenced in forms. A bit of code snippet would be helpful for me. I have many years programming and developing MS-Access Applications. Any help would be appreciated. New to this forum and SQL Server Express which I have already successfully installed and used the MS-Access migration tool which did not convert many of my access queries. I have spent 1/2 day converting iif statements to case and trying to figure out this parameter issue. If more detail is needed from me please do not hesitate to ask me.Thanks,
~Annette
Hey, Annette. I hope, even with its frustrations, that you're enjoying what you're doing. Access is fun, and I use it all the time, but the power of SQL Server is just awesome when you get used to it.
The technique that I use to link Access forms to SQL Server queries is to have the form reference a SQL Server "parameter" table that contains a single record: Access updates that record, the SQL view or stored procedure uses that table as a date range or whatever. I find it works pretty well, though I'm posting a question on it right now as my VB Kung Fu is very weak.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply