Hi, all!
I'd like to preface this with the fact that I am not a career tech person--I'm an insurance lady who got voluntold. I've only been learning Access since June of this year, and VBA as of last month, and now I've learned that I'm going to need SQL, as well. Sorry if the technical terms fly over my head; I am still VERY new to this!
So I have a database project for work that's getting larger and larger every time we have a team meeting about it, and I've realized that we will very quickly outgrow the 2GB that Access offers. I've already gotten with the IT department here, and been told that I can have my own instance in the company SQL Server (we use 2008), and that size is no object. I'm the only one here who uses Access though, so I'm having a little trouble mapping it all out in my head, between different things I'm reading online, and things I'm hearing. So I wanted to run this by the pros, and see if I have this all straight.
My plan right now is to complete the setup of the core of the database, and then split it into the front and back ends. I've read that it works faster if the queries happen in the SQL server, so they'll be back there with the tables, and my forms, modules, and reports will be in the front, which will be housed on the main drive that everyone has access to. Everyone gets their front end, and the front end pulls from the back in the server.
Here's where it's confusing me though. My husband is a networking/security guy, so I do ask him IT questions. He doesn't do Access, and he works in Oracle, rather than SQL. He said I may need to write scripts to make the front and back communicate, in this setup? Is that true, and does that mean SQL statements? He also feels like while we will be able to view data easily, that we wouldn't be able to create, delete, or edit without help from the corporate DB guys. Is that going to be the case? Because we're going to need to input and edit data on a regular basis. Or is that more of an Oracle and things-other-than-Access sort of deal?
Also, there are other functions that I want to "patch" in later, as I complete them; they'll consist of whole new tables, forms, and reports. Can I still make those in my test/dev database that will remain strictly in Access, and then transplant those tables into the back-end that's already in the SQL Server? Or will that be a problem? Should my test/dev database also be converted to a front/back setup like this?
Also, I'm getting a copy of the Dummies all-in-one desktop reference for SQL 2008 to go ahead and start reading up on it. I have this project, and then one other that I need to do this way. Would you say that the desktop reference is all I'm going to need, or do I need to REALLY study SQL like I am VBA? And if so, what resources would you suggest to a noob?
Thanks so much!