Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    DCactivity is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Location
    Suffolk, UK
    Posts
    8

    Lightbulb Database for office equipment company advice

    Hi all,



    I've been building a database for our company for about a year now. It has gone through several iterations and I'm still miles of getting it to a workable state. I'm sure its not too hard of a task, but time is getting on and our existing Access 97 database is very fragile, so I'm turning to the forums for some help & advice.

    A bit of background about my Access experience: None. Everything I have built so far has been based off of excel knowledge, various forum posts and youtube videos. I work in IT so technical language isn't an issue, I dabble with code too so coding lingo is OK too.

    The old database. Access 97 database which does the following:


    • Stores customer information
    • Machine (computers/printers/Photocoiers etc) information
    • Invoicing
    • Jobsheet creation
    • Contracts


    It is used in a cringeworthy way also. The DB is stored on a central PC, and several users connect in and alter info. Naturally, this has made the database incredibly fragile, to the point where it now corrupts if opened at the same time as another user.

    I am rebuilding the database as a more modern .accdb file, within Access 2019 with proper front-end/back-end usage on a server. What I really need help with is the following if possible:


    1. How do I properly plan a database?
    2. Relationships. I'm not 100% clear how they work, and I'm sure this is why most of my DB attempts failed so far. What do I need to know in this area?
    3. Any best practices?
    4. The DB needs to produce a printable jobsheet for the engineer to take around, get signed etc, which is used to invoice the customer. I cannot get this to work well, I think its a relationships/report issue due to my poor knowledge!


    Thanks for your time, please treat me as an absolute beginner. I do understand various bits of Access but that doesn't mean I won't learn anything new. Any good advice or links to tutorials welcome!

    P.S. Sorry if this is really vague, it's a stressful time with the world's events and trying to balance this DB around everyday work!

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by DCactivity View Post
    Hi all,

    I've been building a database for our company for about a year now. It has gone through several iterations and I'm still miles of getting it to a workable state. I'm sure its not too hard of a task, but time is getting on and our existing Access 97 database is very fragile, so I'm turning to the forums for some help & advice.

    A bit of background about my Access experience: None. Everything I have built so far has been based off of excel knowledge, various forum posts and youtube videos. I work in IT so technical language isn't an issue, I dabble with code too so coding lingo is OK too.

    The old database. Access 97 database which does the following:


    • Stores customer information
    • Machine (computers/printers/Photocoiers etc) information
    • Invoicing
    • Jobsheet creation
    • Contracts


    It is used in a cringeworthy way also. The DB is stored on a central PC, and several users connect in and alter info. Naturally, this has made the database incredibly fragile, to the point where it now corrupts if opened at the same time as another user.

    I am rebuilding the database as a more modern .accdb file, within Access 2019 with proper front-end/back-end usage on a server. What I really need help with is the following if possible:


    1. How do I properly plan a database?
    2. Relationships. I'm not 100% clear how they work, and I'm sure this is why most of my DB attempts failed so far. What do I need to know in this area?
    3. Any best practices?
    4. The DB needs to produce a printable jobsheet for the engineer to take around, get signed etc, which is used to invoice the customer. I cannot get this to work well, I think its a relationships/report issue due to my poor knowledge!


    Thanks for your time, please treat me as an absolute beginner. I do understand various bits of Access but that doesn't mean I won't learn anything new. Any good advice or links to tutorials welcome!

    P.S. Sorry if this is really vague, it's a stressful time with the world's events and trying to balance this DB around everyday work!
    Who wrote the existing app
    What is its file extension mdb or mde
    Do you need the new app to do anything that the old one didn't
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    with proper front-end/back-end usage on a server
    have you tried simply upgrading your old system then splitting it? Also not clear from what you have said, but each user should have a copy of the front end on their local machine, sharing an access file is what causes corruption.

    Suggest take a look at some of the similar links at the bottom of the thread. You need to understand the principles of data normalisation before you start. Best practice - write out and detail the processes required to be able to create the outputs (reports) required, then design the tables and relationships around this. Once you have this done, you can start on designing forms and reports. The more you do in this early stage, the less you will need to do later.

    Also, do not be tempted to apply excel logic to databases - they are different animals and will result at best a very inefficient and difficult to maintain app and at worst an app that simply doesn't work

    Relationships are defined by a primary key (PK) on the parent table and a family or foreign key (FK) in the child table. A primary key uniquely identifies a record and is typically an autonumber. The value has zero meaning for any part of your business. That number is repeated in the child records as the FK. Neither field should be visible to the user, they are the string used to 'tie' data together.

    Think of this mantra 'A parent can have many children' - a one to many relationship

    So an invoice header (parent) can have many lines (child)

    A customer can have many invoices

    a product can be on many invoice lines

    Then think of this, 'A child can have many parents and parents can have many children' - a many to many relationship

    an employee can work on many machines, and a machine can be used by many employees

    for this you need a joining table - so a table for employees, another for machines and a third to join them - this contains the FK for employee and machine and perhaps some other data such as a date or time spent using the machine.

    It's a wide subject so I'll stop here. Good luck with your project

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Lots of reading.... basic relational database info that you should know:


    You should know about /understand Normalization

    What is Normalization?
    =======================
    What Is Normalization, Part I: Why Normalization?
    What Is Normalization, Part II: Break it up.
    What Is Normalization: Part III: Putting It Back Together
    What is Normalization: Part IV: More Relationships
    What Is Normalization: Part V: Many-to-Many Relationships




    The Normal Forms
    =========================
    The Normal Forms: Introduction
    The Normal Forms: First Normal Form (1NF)
    The Normal Forms: Second Normal Form (2NF)
    The Normal Forms: Third Normal Form (3NF)
    The Normal Forms: In a Nutshell






    Some suggestions:
    Use only letters and numbers (exception is the underscore) for object names.
    Do not use spaces, punctuation or special characters in object names.
    Do not begin an object name with a number.
    Do not use Look up FIELDS, multi-Value fields or Calculated fields in tables.




    Maybe you would make a COPY of the dB, delete the data, do a "Compact and Repair", compress it (I use WinZip) and post it so we can see the current tables/relationships?

  5. #5
    DCactivity is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Location
    Suffolk, UK
    Posts
    8
    Thank you all for your replies, just some extra info.

    The current 97 DB is an MDB file and I have tried converting up to accdb. However, I get VBA code errors and the DB corrupts. The way I tried to convert was first using Access 2007 as it seems to handle both .mdb and .accdb OK, then 2010 after that failed. I did try going up 1 version at a time, going Access 2000 > 2002 > 2003 > 2007 > 2010 > 2016, but that corrupted as soon as it became an accdb with VBA errors.

    The old DB is full of useless data and functions that either aren't needed or are inefficient. It's around 1.8GB in size, because I believe its stored PDF versions of invoices dating size the start of the database. all those images over time adds up!

    I'll get looking through those links today, thank you Steve, I also hope I've provided a bit more info.

  6. #6
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Can you post a copy of the db
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  7. #7
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    If you can zip the file and then upload we can take a look for you.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by DCactivity View Post
    The old DB is full of useless data and functions that either aren't needed or are inefficient. It's around 1.8GB in size, because I believe its stored PDF versions of invoices dating size the start of the database. all those images over time adds up!
    I think that the dB is corrupting because the size is so close to the maximum 2 GB limit.

    Try this:

    Create a new folder named something like "TestdB". In that folder make 2 copies of the dB. Name one with FE (front end) in the name. Name the other with BE (back end) in the name.
    Open the dB with BE in the name and delete all objects EXCEPT the tables.
    Open the dB with FE in the name and delete ONLY the tables.

    Do a "Compact and Repair" on both the FE and BE, then close and save each one. Now you have a split dB.

    Now, using Access 2007 or greater, create 2 new empty databases. Again, name one with FE in the name and the other with BE in the name.
    Open one of them, click on EXTERNAL DATA, click on ACCESS, then follow the prompts. Make sure the radio button for IMPORT is selected. If you opened the new dB for the FE, select all objects EXCEPT the tables.
    Do the same for the BE - select only the tables.
    Do a "Compact and Repair" on both the FE and BE. The dB should now be in accdb format.


    Last thing to do is open the FE and link to the BE tables.

  9. #9
    DCactivity is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Location
    Suffolk, UK
    Posts
    8
    Thank you for your replies, Steve, I will do this and report back!

  10. #10
    DCactivity is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Location
    Suffolk, UK
    Posts
    8
    Quote Originally Posted by ssanfu View Post
    Open one of them, click on EXTERNAL DATA, click on ACCESS, then follow the prompts. Make sure the radio button for IMPORT is selected. If you opened the new dB for the FE, select all objects EXCEPT the tables.

    Click image for larger version. 

Name:	vberror.png 
Views:	30 
Size:	10.2 KB 
ID:	42911

    Same error appears on the FE. BE data is fine :/

  11. #11
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    That error is telling you the VBA project in the FE is corrupted. I have had lots of experience with that. I used to set a breakpoint, then edit the code. That is a big no-no.

    I could still see the VBA code (even though is wouldn't execute), so my solution was to copy all of the VBA code to text file(s), keeping track of where the code belongs: which form or which standard module.
    Next, delete all of the VBA code.
    Do a "Compact and Repair".
    Create a new blank dB.
    Import all objects - Forms. Queries and Reports.
    Do a "Compact and Repair" on the new dB. Do a save.
    Now paste the VBA code from the text file(s) to the proper modules (form and standard).
    Do a "Compact and Repair".
    Relink to the tables.


    Good luck..........

  12. #12
    DCactivity is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Location
    Suffolk, UK
    Posts
    8
    Silly question, how do you access the VBA editor on Access 97? I've tried ALT+F11, no dice. I know I've been in the VBA window before! XD

  13. #13
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I've forgotten how now, but perhaps open a form in design view, select an event (with or without code behind) which should take you into the editor from where you can navigate to all the modules

  14. #14
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    Use Ctrl + G
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  15. #15
    DCactivity is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Location
    Suffolk, UK
    Posts
    8
    Hi Mike, that brings up a blank debug window, no code to be found in there from what I can see :/

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Need advice on linking remote offices back to main office
    By ChuckColeman1812 in forum Import/Export Data
    Replies: 2
    Last Post: 03-05-2019, 09:30 AM
  2. Need advice on building DB for company
    By f15e in forum Access
    Replies: 4
    Last Post: 03-07-2016, 05:00 PM
  3. Replies: 6
    Last Post: 02-01-2015, 11:37 PM
  4. Equipment Database Design
    By cap.zadi in forum Database Design
    Replies: 3
    Last Post: 02-26-2013, 02:20 AM
  5. a question about Equipment Repair Database
    By Nokia N93 in forum Forms
    Replies: 1
    Last Post: 03-05-2011, 12:31 PM

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