Results 1 to 11 of 11
  1. #1
    burt1215 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    34

    How to Finish Off a Database

    Hello,


    So I've been working on a database for my company for the last two months, and it's pretty much done/wrapping up.

    I was just wondering what some best practices for finishing it off are.

    I want to essentially make it look like a custom app (hide pretty much everything except the main form window). I know this can be done by making the main form a pop up, and then throwing in some code to minimize everything else. That's not really what i'm looking for. I don't want the user to be able to pull up anything, or edit anything, without going through the buttons to do so through my forms.
    So yeah, the big thing is making it uneditable.

    There's also 3000+ lines of code in it, and this will be distributed to over 200 different sites within my company.
    Is there anything I need to do to make it universally ready for just about any environment?

    I appreciate all of your help in advance. I've never written a database before this.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I was just wondering what some best practices for finishing it off are.
    I can throw in a few suggestions based on my outdated 2007 version:
    - always have back up files (consider a nightly backup of the back end tables)
    - if you didn't split it at the beginning you should have. Not too late.
    - compile the front end into an accde (assuming that's still the current compiled extension)
    - specify the main form to be loaded on startup
    - give the app a title (spec'ing an icon adds a nice touch, but was always a pain for me since network gods archived unused files; i.e. bmp files used by db)
    - uncheck 'use Access special keys'
    - uncheck display Navigation pane
    - have an About form with a not obvious label (say version number) that accepts a double click to cycle the AllowBypass key property
    - link the back end tables with a password (not iron clad - connection password can be found if you know how) but it keeps most people from seeing the tables
    - I use 'overlapping documents' setting if I want to control the form size
    - If each user has their own FE (preferred method) a startup routine should check that their FE copy is current (by checking the version number you update in a BE table after each design modification to the FE) and have the db download the new version - or at least, don't let them use outdated versions.
    Is there anything I need to do to make it universally ready for just about any environment?
    That's a tough one. Anything can happen from not having the db in trusted locations to users who don't have proper network permissions to locations of code libraries (references) to bad or slow connections to...
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Additionally,
    this will be distributed to over 200 different sites within my company
    200 sites?? That means 200+ users? Is this on a LAN or a WAN?

    You might want to think about using MySQL or SQL Server Express/SQL Server Standard.


    Do not allow wireless connections - a sure recipe for corruption.

  4. #4
    burt1215 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    34
    Quote Originally Posted by ssanfu View Post
    200 sites?? That means 200+ users? Is this on a LAN or a WAN?
    Sorry, I should have been more clear.
    The application will be distributed to 200+ sites, where each site will use it as their own copy of the database.
    The database will not be linked between sites.
    Each site will have at most, a manager, and two or three supervisors using the database at any given time via a local server.

  5. #5
    burt1215 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    34
    Thanks man, I'll definitely give all this a shot.

    Quote Originally Posted by Micron View Post
    - if you didn't split it at the beginning you should have. Not too late.
    Let's say I don't know what this means... could you explain it?

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Generally speaking, all tables go into one back-end database (myDatabaseBE.accdb) and all forms, reports, queries, standard modules go into the front end (myDatabaseFE.accde).
    I might have the file extensions wrong for version 2016, but I'm indicating the FE should be compiled (commonly referred to as an executable, but it's not in the true sense). Sometimes some temporary tables (not that the table is temporary, rather the data is) go into the fe, but only if that data needs to be local on the user's pc or if there's some other compelling reason. Google 'ms access split database' or something like it or search this forum. There's tons of information, including videos, on the subject. In addition, you should check out 'ms access prepare database for distribution' or similar. Lots of suggestions, reasons and instructions on that also. Again, when ensuring users have the current version, I'd rely on startup code checking the version number in a BE table and not allowing entry if it doesn't jive, rather than changing the filename each time and screwing up everybody's shortcuts. No one should be accessing the db folder to launch it. In fact, I got our IT guy to make the db folder not visible but still accessible to the users. No idea how they do that.

    For some strange reason, 200 copies is what I envisioned, but I just as easily could have been wrong about that and shouldn't have made that assumption. Good catch Steve.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    burt1215 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    34
    That makes a lot of sense. Thanks a bunch.
    Seriously, So much help.
    I really don't know much about Access...
    This all started two months ago when I was like "Hey, I want to learn Access" and my boss was like "We'll pay you, if you make something and we like it, we'll use it."
    3000 lines of code later, and it's going to be distributed to 200+ sites...
    So I kind of don't want it to blow up after distribution.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I for one am very curious as to how someone can learn Access in 2 months and write 3000 lines of vba code. You must have an IQ 3x as high as mine, so you're somewhere around 150! Wish I could see inside of it...

  9. #9
    burt1215 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    34
    Naw, I'm not that smart.
    I knew programming going into it. C++, Java, Python, MATLAB, so learning VB wasn't too hard. (It's seriously amazing the sort of stuff you can do, writing subs that edit the SQL of queries... and other things like that.)
    I picked up a book, Access 2007 Vba Programmer's Reference, read the first six chapters, which was enough to get the basics down. Learn forms, reports, queries, tables, and all that. And then the basic syntax for VB.
    The rest just came from lots and lots of Google. As well as messing up and eventually finding better ways to do things.
    Bottom line, I'm more or less self taught... So my style is probably really different than the standard norm.

    I seriously wish I could send you what I have so far... But I'm pretty sure that would be highly frowned upon by my company, as they basically own the rights to this database.

  10. #10
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I seriously wish I could send you what I have so far
    I guess you could always ask them. I'm retired and have no interest in whatever it is your db is about, other than to learn from others and sometimes offer suggestions. My remuneration lies in what I learn, so what I'm saying is that I've nothing else to gain by looking at it from a copyright perspective. If it's about privacy, it's not too hard to do a Find/Replace on records that were dumped into Excel (to hide names or personal info) then paste append them as records into those tables. As noted, there is no guarantee I'd be able to open it. Should you decide to proceed, I suppose the moderators here would not mind you using a drop box and pm'ing me the link since you're not posting a question about your design. I believe doing so for soliciting paid help is frowned upon, but as I said, I'm not looking for any at the present.

  11. #11
    mbenton's Avatar
    mbenton is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2015
    Posts
    73
    Congratulations! Do yourself (and others) a big favor and document your work, if you haven't already.

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

Similar Threads

  1. Replies: 2
    Last Post: 12-20-2015, 02:23 PM
  2. Waiting for shell to finish
    By friendlyfriend in forum Access
    Replies: 3
    Last Post: 09-10-2013, 07:29 PM
  3. How to finish it
    By Abarency in forum Access
    Replies: 1
    Last Post: 09-12-2012, 06:08 PM
  4. Wait for query to finish
    By MikeDS in forum Programming
    Replies: 1
    Last Post: 01-28-2011, 10:17 AM
  5. Need some support to finish DB
    By Estyl in forum Access
    Replies: 0
    Last Post: 04-22-2008, 04:34 AM

Tags for this Thread

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