Results 1 to 8 of 8
  1. #1
    bcox9 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    10

    Splitting my database.

    I am in need of assisstance, I dabble in database programming and learning more about databases (programmatical, and design aspects) in my free time. I am a 100% amatuer, if there could be a classification below amatuer then that would be me.



    With that being said, I have a single table that contains a list of SKUs and all the details that I want in my program in this table. It has about 11,000 records (not sure if this is relevant knowledge or not).

    I also have a query, a form and a subform. The form has some simple macro's behind it, and the query has a little SQL behind it.

    In a nutshell, the query uses the form's controls to query the table, and the subform pulls the query into datasheet view.

    The user will need to change 3 fields and only 3 fields of the 9 fields in the table, they can obviously do this through the subform. What I don't want is for them changing anything else. I am unsure how to go about doing this though. The computers that will be using this are shared among many people so logins and passwords are out of the question.

    I personally was going to figure out how to keep the table stored on one of our servers, and keep the rest of it stored on each computer in the back where it will be used. However something that I don't know what the effect will be is that, the server I believe has office 2007 on it, while the computers that will use this have 2003. It is in mdb format...finally (I built the database in 2010, because I was told by the IT personnel that the target computers had 2007)

    I realize in a perfect world that VB could be used to make a front end, but I have no idea how to open up or work with a database in VB.

    Any suggestions would be helpful, I'll even take rude comments if they have some informative information in them!

    Also the people who will be using this application will be uneducated, so the more user friendly I can get it the better. (This is just an afterthought)

    Thanks for your time.
    Ben

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Welcome to the forum.

    It really does not matter what software is on the server since all interaction with the data should be through the front end. When you split a database, you end up with the back end file which should only have the tables this is what is put on the server. The front end file will need to be distributed to all computers that will need it. There are settings under options where you can hide the tables so that your users cannot see them.

    You would create your forms, queries and reports in the front end file.

    The user will need to change 3 fields and only 3 fields of the 9 fields in the table, they can obviously do this through the subform.

    You would lock the form controls that are tied to the fields that must not be changed. This is a property setting of the control.

  3. #3
    bcox9 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    10
    Well the subform is in datasheet view, and I didn't think you could edit individual fields as far as locking goes in a subform in datasheet view. I know that in your main form any control's you add you have full access to as to what you want them to do, how you want them to look and when you want them to look or do what you want.

    Right now the main form has 2 fields, SKU and Title (it's a book SKU database). When you hit search it just requeries the subform (in datasheet) and the subform is linked to the query.

    I would just make it one main form, but it will make it more user friendly to them to be able to search by title so let's say the book's title is Cats, well they search cats. There are roughly 26 title's with cats in the name (using a SQL LIKE statement) the subform pulls all that from the query.

    So my question for this is how do I lock the individual fields in my subform? When I pull up all my controls in my drop down in design view I can click subform, but it doesn't list any controls, because they're are none it's in datasheet view.

    Also as to the front end back end, I need all the data entries to be stored on the back end on the server, this may be a rudimentary statement, but I think that access's database splitter just makes read only front ends, or does it just split it and you decide if it's read only?

    Thanks for the information you've provided so far, it helped ease my mind as to how I was going to split the database.

    Ben

  4. #4
    bcox9 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    10
    Ok for some reason in my original database that I made a save of when I had all the forms created, and the sql statements done, the full subform showed in design view, and after I've tweaked everything the way I wanted it as far as design, the subform no longer shows up in design view. In layout view though the full table shows up and I can click each control and lock it how I need to.

    Thanks again for the info you provided earlier, and any links or such you could give me on splitting the database would be much appreciated. Google and me have become best friends here in the last few days.

  5. #5
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Also as to the front end back end, I need all the data entries to be stored on the back end on the server, this may be a rudimentary statement, but I think that access's database splitter just makes read only front ends, or does it just split it and you decide if it's read only?
    The front end should not be read only. Open Access then file-select the file and then there should be a dropdown next to the open button which should allow you to change how Access opens the frontend file.

    You can lock a control in a form that is in datasheet view. Open the form in design view, left click on the control (not the label), then right click to bring up the menu or click on the Property Sheet icon in the ribbon. Click on Properties (not form properties) then go to the data tab and then the Locked property and set it to yes

  6. #6
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I forgot to mention, that it would be best to work on the backend while it is on your local hard drive. In fact, I would keep both files on my local hard drive until I am ready to deploy the database. When you are ready, move the backend to the server and then relink the frontend to the backend using the Linked Table manager.

  7. #7
    bcox9 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    10
    I got the front end, back end functioning. I transfered the backend to my laptop, which sits right next to my desktop monitor and it is working.

    I do have one question though, in 2003 when I open my subform in design view it doesn't show it as datasheet view. It shows it in form view. Well when I lock those controls in form view, it doesn't lock them in datasheet view in the actual form.

    However, in access 2010 if I edit the controls in layout view (layout view doesn't exist in 2003 as far as I can tell). It Does lock the subform controls when I go back to my main form.

    There is probably a simple solution to this, and if nothing else I'll edit the subform in 2010 and import it to my 2003 copy of the database.

    Once again thanks for the info, you've been extremely helpful in getting this thing working properly.

  8. #8
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I'm using 2010, so I cannot test the locking in 2003. You're welcome and good luck with your project.

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

Similar Threads

  1. Splitting a Database - Sort of
    By Paul H in forum Database Design
    Replies: 6
    Last Post: 11-10-2011, 02:07 PM
  2. Error when splitting database
    By blkmagic in forum Access
    Replies: 1
    Last Post: 09-30-2011, 04:55 PM
  3. Splitting Database
    By injanib in forum Database Design
    Replies: 0
    Last Post: 02-25-2011, 11:08 AM
  4. splitting database
    By colotazzman in forum Database Design
    Replies: 2
    Last Post: 02-10-2011, 09:12 PM
  5. Database Splitting
    By Matthieu in forum Access
    Replies: 8
    Last Post: 02-08-2010, 03:37 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