Results 1 to 10 of 10
  1. #1
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156

    Moving an existing Access Database backend into a SQL Server?

    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!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Your program should work as access backend or SQL backend.

    To flip the switch and make it work from SQL/oracle server, just attach all the SQL tables to the access backend and once in, rename the tables to the same names as the access tables were.
    It works just fine. I do that.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Only way I've ever connected to a non-Access backend is with ODBC set up and linked tables. Data entry/edit should not require IT - that would defeat the whole purpose of enterprise database - but modifying the backend structure probably would. This is why we went with Access as both frontend and backend but we will never hit the 2GB size limit.

    Suggest you develop with Access frontend and backend until you have the backend structure stable and mods are rarely needed then migrate backend to SQL.

    If you aren't the one setting up and managing the SQL backend, not sure how helpful the book will be. I use SQL only for building queries in Access. Never seen a book. The Access query builder has been a big aid in learning essential SQL. Often visit this site http://www.w3schools.com/sql/default.asp
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156
    Ranman256, forgive me if this is a dumb question, but I have no server experience, and am going off of what I just read online today. Do you mean there's a way to parallel the Access back-end with the back-end in the SQL server?

    June7, you mean if I need to change the structure of the tables, or add tables, I'd have to have IT do it? Is that just a permissions issue, or is that something I could learn for myself? If I'm in my own "instance" of SQL, would that mean I would have complete control and authority within it? The IT guy I talked to this morning said I'd be the only one here able to get into it (excluding corporate, of course), so I thought that meant I'd have control, and didn't think to ask about it. He did say too that while the corporate DB guys will help me if I have trouble connecting to the SQL server or something that's an actual problem with the server, but if it's just me needing some help figuring things out or something, that they won't.

    I'm building the database in Access first, and then splitting it and using the Wizard to move the back-end into the SQL server. I'm going to have most of the tables done soon, but there are a few that I'm having to wait to complete until I can see how one of the reports I'm going to have to import data into my database from is going to look, and I can't get my hands on it until that system launches (in I think November?). So my initial plan had been to go live with the bulk of the database ASAP so the department can go ahead and start entering the data that we can right away, and then once I've had a chance to look at these reports and develop what I need from them, patch the new features in. So I was hoping that was something I'd be able to even after the back-end has moved. There will also be things that change later on as rules change, so I know tables will need some tweaking every now and then, anyway.



    I did just have a thought though. Part of what's going to eat away at that 2GB limit is archival data; some of what we need for reporting purposes goes back as far as the 90's. It probably sounds odd to put such old data in there, but we need those figures for calculations on reports that are the main reason this database is being built. I had also read about splitting the database, but with TWO back-ends, but the downside was there was no way to enforce referential integrity (which is extremely important throughout the database). However, if I were to talk my boss into not stressing the referential integrity on the historic data we're going to be manually keying, could I potentially have one main back-end with the bulk of the data and the more current information that's coming in through the reports, and then a second back-end that stores just the historic tables with manually entered figures? And could forms and reports pull from and do calculations with both of these? Like if I wanted to see an account's total claims figure, going back to 1998, would the form/report be able to take the appropriate figures from the archival data tables in back-end B for 1998-10/2014 AND the figures from the tables in back-end A for 11/2014+, and do calculations with them?

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    If IT wants to grant you carte blanche on the server, lucky you, I guess. I just know in our org it means giving up your firstborn for non-IT staff to get any kind of administrative privileges on server. SQLServer and Oracle are designed as enterprise databases and managing them is a whole different can of worms from an Access configuration.

    We had the option of incorporating our little db into the org Oracle implementation which sits on a server 600 miles away. Any mods would have to be requested and probably take weeks. I do have a db that links to that system and performance seems ok. SQL and Oracle were just overkill for our needs and we would not have direct management control.

    My db was a conversion from old DOS dBase IV. It is laboratory data, not financial or personnel tracking. I could easily do a cutoff and keep the historic data in the old dBase tables and link to those tables. The new db begins with 2009 data. I am able to build queries that incorporate the old and new data. I have since imported all the dBase tables into Access tables because MS is removing support for dBase.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156
    Oh, I'm not going to be on the main server! The way I understand it is they're sectioning off a little piece for my project; I'd be the first to tell them it was a mistake if they were going to try to turn me loose, because I know I'd break things. And you say lucky, but I'm terrified. I'm excited to learn all this, but I went from learning Access, to VBA, and now SQL; frankly, I'm scared I'm going to walk in Monday, and find out I need to learn something else too.

    I went in and pitched my idea about the 2 back ends to the boss, but she's all about the server. Talked to the IT guy, and he confirmed that I will have control over the tables, to add and edit as needed, without having to get corporate to help. They're going to install the studio on my computer, and give me a terabyte of server space that's mine. I'm happy that I don't have to go to corporate any time I need to work on the tables, but now I'm afraid of how I'm supposed to do it. Will they still look and function about the same for editing as they do in Access, or is this going to be completely different?

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    There are some differences between Access (Jet/ACE) and SQL Server. One of which are field types. Another is that SQL Server doesn't support look up fields or MVF (hope you didn't use either).

    You can link to SQL server tables like Access, but you have to first set up an ODBC connection. There will be differences that require code to be different, so you might have to have 2 versions of the Access FE - one for Jet/ACE and one for SQL Server. Speaking of linking, SQL Server tables are usually prefixed with "DBO_". When you link the Access FE to the SQL Server BE, the linked name will also be prefixed with "DBO_". Not to worry... you can rename the linked table name in Access. There is also code to automatically rename the linked tables. (search this forum).

    If you want to dip your toes in, you can install SQL Server Express edition on your home computer or laptop. (I'm up to my big toe so far.)

    To download SQL Express 2008 R2 SP2

    Microsoft® SQL Server® 2008 R2 SP2 - Express Edition
    http://www.microsoft.com/en-us/downl....aspx?id=30438


    To download Microsoft SQL Management Studio Express edition for 2008, go to
    https://community.shavlik.com/docs/DOC-23132


    SQL Express 2012 requires Win 7 or later..... Win XP won't run SQLE 2012.

  8. #8
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156
    Thanks, Ssanfu! I didn't know about the lookup fieldsand MVFs not working; luckily, I hadn't used either yet, and now I know to keep it that way.

    I thought that one of the advantages to keeping the front end out of SQL was that all the code would be attached to it, and would be alright. How much tweaking is there to do? My grasp of VBA is still very shakey.

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    As I said, I have just dipped my big toe in as far as Access conversion SQL Server Express (SQLSE). However, there are differences to be aware of in field types and SQL syntax. (SQLSE used T-SQL)
    The Access up sizing tool only converts (up sizes) the tables. You have to change/edit the queries, forms, reports and code to work with SQLSE .

    If you are going to be using SQL Server (which it sounds like), you might want to use Access/Jet/ACE to get the tables/fields/relationships correct, then switch the BE to SQLSE on your personal computer. Finish developing the queries, forms and reports. THEN move to SQL Server.

    I don't think you will be able to have one Access FE for both a JET/ACE BE and a SQLSE BE.


    Here is a brief article about the history of JET/ACE:
    http://en.wikipedia.org/wiki/Microso...atabase_Engine

    Other info:

    SQL Server Express vs MS Access

    http://stackoverflow.com/questions/5...s-vs-ms-access


    Data Structure Differences between Access and SQL Server
    http://www.databasejournal.com/featu...SQL-Server.htm


    Access to SQL Server Migration: Understanding Data Type Conversions
    http://blogs.msdn.com/b/ssma/archive...nversions.aspx

  10. #10
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156
    That sounds like a solid plan.

    Thanks for the links! I need all the help I can get.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 06-25-2014, 06:57 AM
  2. Replies: 1
    Last Post: 12-09-2013, 01:25 PM
  3. Replies: 4
    Last Post: 09-05-2013, 08:02 AM
  4. Replies: 2
    Last Post: 02-16-2013, 12:35 PM
  5. Moving Backend
    By shariq1989 in forum Access
    Replies: 1
    Last Post: 08-05-2012, 02:46 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums