May 19, 2009 at 8:58 am
Hi All,
First post here....be gentle 😛
I am a data analyst who is in charge of adminning a large Access database that's used by several teams, (25-50 users, local & remote).
This database has many forms but most are tied in some manner to the "Master" table which currently contains approx. 325K rows. Approx. 5-10K new rows get added each week. Forms are slow to open but work ok once open. Queries are slow, remote access via VPN is slow to unusable. Remote desktop works much better but is not a permanent solution for dedicated work at home staff.
Anyway, I'm trying to learn about SQL from the standpoint of someone who is well versed in Access & VBA. There are so many aspects of SQL server, many of which I'm pretty sure I don't need to learn about right away. I'm not looking to be a DBA. I'm not looking to work in an IT role. I know that initially I want to migrate all my tables to a SQL server BE. I will then link to the BE with the existing Access FE. As time allows I would then migrate my Access queries to stored procedures. Eventually, down the road, I'd like to write a web-based FE but right now I just want to learn to do in SQL server what I currently do in Access. Everything in SQL seems very complicated.....instances, clusters, etc. :crazy: although Access was the same way when I first started learning it.....I can learn the fancy stuff later.....just help me learn what I need to know right now to be productive.....;-)
May 19, 2009 at 9:43 am
That's very similar to the situation I had when I started out. Access as the front-end to SQL as the database works quite a lot better than just plain Access, and it's pretty easy to build once you get over the initial hurdles.
Here's what I recommend:
Get a copy of SQL Server Developer Edition.
Install it on your desktop computer. Use the default options - pretty much just keep clicking Next. If you use a Windows domain for security of the network, select that as your security option, since that keeps things simple. It's going to be easier than you're worried about.
Take the Access database, make a copy of it on your local machine, and use the Upsizing Wizard to have it build the database for you.
If you run into problems with that, post the specifics on this site, and we'll help walk you through them.
Once you've got the database working, connect an Access Data Project (.adp file) to the database, and import your forms and such into it. Work your way through, asking for help as needed, till you have exactly what you need.
It took me about 2 weeks to do that with a pretty complex database, back when I didn't know anything at all about SQL Server and just barely knew anything about Access. Microsoft's wizards made the whole thing much easier than I deserved.
Once you've worked that out, get a copy of whatever version of SQL Server you're going to use (probably Standard, for the scale you're writing about), and go through the same installation process, but on the machine that's going to be the actual server.
Copy the database from your desktop machine (where you have Dev Edition) to the actual server.
Change the Connection in the Access project (it's on the Files menu) to the server. You may need to create a Windows Data Source (DNS file) for this, but Access will also walk you through that.
Test the whole thing.
It's almost certain to be easier than you're worried about to get it up and running.
Once you've done all that, set up backups on the SQL Server by going through the Maintenance Plan wizard.
Doing it this way won't get you a perfect set up. It will do a few things that experienced DBAs generally avoid. But none of them matter that much, and it will get you up and running quite easily.
Over time, you'll want to fine-tune the server and the database. You'll want to expand and fine-tune the Access front-end, or replace it with something web-based (probably C#.NET). But just to get started, the wizards in Access and SQL Server Management Studio will be "good enough".
If you really need something better than "good enough" right at the get-go, you'll need to hire someone to handle it for you. An experienced DBA and Access Dev should be able to get the whole thing done in a couple of days, once you have the server physically available and the SQL Server installation disks on-site. Spending that money might be better than doing it yourself, especially since you don't intend to make it into a career.
If you wanted to make it your job/career, then I'd definitely recommend doing it yourself. It's how I got started in SQL, and I enjoyed the challenges and the learning. Since you don't, renting someone else's expertise will probably be better than other options.
But if you do decide to do it on your own, go the route I outlined above. It'll be the easiest way to get it done, and it will be good enough.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 19, 2009 at 9:52 am
Thank you very much Gsquared for your reply. I very much appreciate your taking the time to put your thoughts to words ! I do want to make it my career but right now I need to be productive. I have purchased SQL 2008 Dev edition and have it installed at home but I can't get a copy of our Access db "legally" home so I've put in a request to my have copy of 2008 Dev installed on my work laptop so I can work with good data.
I had looked for but couldn't find 2005 Developer Edition. Our company is currently working with SQL 2005. Will there be any problem with converting our Access db to Dev Edition 2008 and then to 2005 ?
Regarding ADPs, I thought Access was moving away from supporting ADPs ? I was thinking of either an mdb or mde and linked tables for now ??
Thanks again for your reply !! 😀
May 19, 2009 at 11:40 am
Linked tables may be the way to go now. What I was doing was Access 2000, then 2003, on SQL 2000, then 2005. I haven't worked with later versions of Access.
If you design the database in Compatibility 90, instead of 100, it should work on a 2005 server. In Management Studio, right-click the database and go to Properties, Options, and set it to Compatibility 90. That's SQL 2005. 80 is SQL 2000, 100 is SQL 2008.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 22, 2009 at 1:59 pm
Hi jdowski,
I'm a newbie too. I've been working with sql for over a year, just doing simple lookups, but until just recently the requests have become much more complicated. I was a python scripter prior....
I took a free sql exam (http://www.w3schools.com/SQL/sql_quiz.asp) to see where I was technically and then tried to concentrate what I did really poorly on (which was almost everything :-P)
I have found that this forum has been really great since I joined and the folks here are more than willing to help out and explain things.
Michelle
May 22, 2009 at 2:38 pm
Thanks Michele !
My experience has been primarily with Access and it's a tool I'm like and am fairly proficient with. However, as I move up in the "corporate" world I find my projects frequently bumping up against the limitations of Access, primarily in size and number of users. SQL server seems to be the next logical step......:-P
mmunson (5/22/2009)
Hi jdowski,I'm a newbie too. I've been working with sql for over a year, just doing simple lookups, but until just recently the requests have become much more complicated. I was a python scripter prior....
I took a free sql exam (http://www.w3schools.com/SQL/sql_quiz.asp) to see where I was technically and then tried to concentrate what I did really poorly on (which was almost everything :-P)
I have found that this forum has been really great since I joined and the folks here are more than willing to help out and explain things.
Michelle
May 22, 2009 at 2:50 pm
Hi jdowski,
So many people have helped me through the way that I feel like I should try to pass it on. Do you have a request from an end user that you're currently working on that you can share? I've never really used Access believe it or not, I just went straight to sql. Are you comfortable with the basic structure of a statement and using different functions like cast, coalesce, count and stuff like that?
Michelle
May 27, 2009 at 12:03 pm
Not that I have tried this but would be curious to know if you use it and how it works.
Microsoft has put out a free app that will assist you in migrating a Access database into SQL Server:
http://www.microsoft.com/Sqlserver/2005/en/us/migration.aspx
One thing I read that it does, it will take the queries in your Access database and create them as views in SQL. It also allows you to define primary keys if it comes across a table that does not have one already.
There are some links off the link above that provide some training videos that walk you through doing a small one.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
May 27, 2009 at 12:09 pm
Interesting... I've never tried it. I've always just used Intergration Services. Is the Migration Assistant just as robust as IS?
Michelle
May 27, 2009 at 12:09 pm
Thanks Shawn,
I will look into it. Right now I am waiting to hear from IT as to what they will install for me for SQL Developer Edition, 2005 or 2008. I suspect 2005. I really liked GSquared's advice and am planning to follow it pretty closely. I need to be able to leverage my current knowledge in Access as much as possible initially so as to maintain productivity with the database itself while I come up to speed about SQL server itself.
J. :hehe:
shawn.melton (5/27/2009)
Not that I have tried this but would be curious to know if you use it and how it works.Microsoft has put out a free app that will assist you in migrating a Access database into SQL Server:
http://www.microsoft.com/Sqlserver/2005/en/us/migration.aspx
One thing I read that it does, it will take the queries in your Access database and create them as views in SQL. It also allows you to define primary keys if it comes across a table that does not have one already.
There are some links off the link above that provide some training videos that walk you through doing a small one.
May 27, 2009 at 12:55 pm
Not that I have tried this but would be curious to know if you use it and how it works.
Microsoft has put out a free app that will assist you in migrating a Access database into SQL Server:
http://www.microsoft.com/Sqlserver/2005/en/us/migration.aspx
One thing I read that it does, it will take the queries in your Access database and create them as views in SQL. It also allows you to define primary keys if it comes across a table that does not have one already.
There are some links off the link above that provide some training videos that walk you through doing a small one.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply