Results 1 to 15 of 15
  1. #1
    Join Date
    May 2019
    Posts
    10

    Is it possible in Access? Beginners question about a potentially complex database...

    Good morning.



    I'm very new to Access, and as such have already viewed several excellent start-up videos on YouTube, but haven't been able to get a feeling that what I want to achieve with Access is possible or not. So I'd very much appreciate some input as to my requirement below.

    Essentially, I head up the tech side in a small CAD bureau (or drawing office if you prefer) and I'm looking to have a database that keeps track of all the drawings we issue to our clients. The information stored will be relatively basic, such as the revision number, date issued, who issued it and the drawing status. The intention is to link an Access database into AutoCAD via VBA (which is perfectly doable and I'm proficient in both AutoCAD and VBA) and allow the user to add and read information regarding to current and previous projects through a Userform such as in the one below that I've cobbled together.

    Click image for larger version. 

Name:	drgiss1.JPG 
Views:	34 
Size:	38.2 KB 
ID:	38272

    What I'm struggling with, is how to set up the database properly. In the image above, the Client combo box is populated from a list of Client named directories on the server. When that changes the Project combo box is populated from a list of Project named sub folders within the Client folder. Similarly, the discipline list is populated by the next level of sub folders (namely 'electrical', 'mechanical', 'drainage', etc), and a change to that list fills the Drawing Number box with the files in the discipline folder, all of which are named exactly as the drawing they contain (one drawing per file).

    What I'd like to happen, is for the user to be able to click on one of the drawings in the list and the boxes to the right populate with all the historical info about that drawing. Drawings can go through any number of revisions so it's very important that we can keep track of it all.

    On the flip side, the other part of the Userform automatically takes information from the Drawing (as listed above) plus the user login name, and once the form is satisfied and the user click a button, the drawing is prepared for issue to the Client. At this point I need the userform to update the database accordingly with the new information.

    So there we have it. Creating a simple database in Access to store 'Drawing Info' is easy enough, just by following the tutorials. But what I'm struggling with is how to add the necessary sub-layers (for want of a better word) for Clients -> Client Projects -> Project Disciplines (this field is a fixed list of options) -> Discipline Drawings (taken from available filenames) -> Drawing Info. So that whenever a combo box is changed, the code can go back up the hierarchy of the database levels and repopulate the form accordingly. Incidentally, I already have a program that generates new Client / new Project folders and then creates all the standard sub-folders required for a job, so the database needs to be able to cope with adding new clients and projects, as well as drawing and drawing info.

    I hope the above is clear enough. Just to clarify a point, this inquiry is about creating the Access database, not the VBA code to read and write to it.

    All and any help with this would be very much appreciated.

    Kind regards

    Graham

  2. #2
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    What you are suggesting is very achievable in Access, your combo's are known as Cascading Combo's.
    It's a very common use of them to drill down into data in the way you describe.

    You can also use routines to populate a list with filenames in folders, I would look here http://allenbrowne.com/ser-59.html
    And on Allen's site in general for some seriously good tips and examples, including the use of combo's.

    Edit: In general design the tables first based on your output requirements. Your outputs will determine what inputs you need and how to store them.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Yes Access can do this.
    I find some folks use too much code where it isnt needed and tend to over-complicate the project. Most everything can be done with forms and queries (but sometimes code is needed here & there).

    Your project will mostly be storing the path to the drawings and adding keywords to a list to find said drawing.

  4. #4
    Join Date
    May 2019
    Posts
    10
    Thank you very much for the swift replies. I've just had a quick look at that link (thanks Minty) and note that it's referring to VBA inside Access. I've been using VBA inside AutoCAD, primarily because I've set up a number of buttons on a palette on a server so that all PC's can access new content and programs as soon as I've finished them. Should I be looking at this more form the Access side of the fence? I'm sure I can hook into Access as a program from AutoCAD which would give me full access to Access and subsequently a database file. Done it with Word and Excel in the past anyway. I'm not sure at this moment in time, how much access the dbconnect feature in AutoCAD has over a database file...

    Ranman, do you think this is simpler than I've been expecting? I see that your suggestion to store the full path would mean I could scan all records and filter out those that do not contain the keywords supplied by 'Client', 'Project' and 'Discipline' using InStr functions. However, that would mean scanning every record every time. I was hoping to go directly to a group of drawings who's records were compartmentalised within the database. Am I miles off with that??

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    it is doable with cascaded combos as other people here recommended. But in your case another approach is also possible. Look at solution I adviced for this post https://www.accessforums.net/showthread.php?t=76681

    You'll need an unboubd main form with combo to select client (you can also have it in page of tab control). Then at top of main form or tab page in main form you have a combo to select client, or a little subform based on continuous or single form where you can select a client and add new clients too - what to use depends how much room other forms in top of form/page need). Then you add at top of form/page other subforms to select and register projects for selected client, and to add and register disciplines for selected project of selected client, then in same style a subform for drawings.

    Now to history part. You need a DravingHistory/DrawingVersions/DrawingWhatever table, where all history for every drawing is stored. Probably you need there a status field also, where one row for drawing has status actual, and all other a historical records. Based on this table you create a continuous form, and insert it into main form/page in main form with other subfoms on it, below them, as subform.

    In main form you also need text boxes - one for every subform except for last one, where the value of active primary key of according subform is stored. And to this text box will be linked hierarchically next subform.

    When all is done properly, you get a robust working app, where only code you need are single-code-row Current events for forms (except for last one).

  6. #6
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I wouldn't store the full path, maybe just the document title.
    From your description your files are stored in a structured fashion, so lets make some assumptions. I've obviously simplified the names / numbers sequences etc.

    Your files are on a file server somewhere - we'll call it \\MainShare\Files\

    Under that are the client folders ? \Clients\Acc12345

    Then the drawings? \Acc12345\DrawNo1\Revis1.drg , \Acc12345\DrawNo1\Revis2.drg etc etc ?

    So in a table called MySystem you would have a field called TopLevelFiles with \\MainShare\Files\ stored as a string, on opening the database you load this into a public variable to be used throughout your application.

    If your file structure is logical as above it becomes simple to lookup the client list, then their drawing numbers , then the revisions. Or any other number of layers.
    If you ever move your document storage, but keep the same logical layout, you simply change one record in your database to point to the new location, and everything keeps working.
    Does this make sense and mimic your workflow a little ?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    Join Date
    May 2019
    Posts
    10
    Hi Minty

    Yes, that's predominately correct. I've included an example of our folder structure below. All drawings are naturally kept in the subfolders of '02 - Drawings'.

    Click image for larger version. 

Name:	drgiss2.JPG 
Views:	29 
Size:	27.5 KB 
ID:	38273

    I understand what you're saying about storing the first (and generic) part of the every file path within a field and assigning it to a public variable, but at the moment I'm approaching this from the other (and possibly incorrect) side. In my code within AutoCAD VBA, I already store that prefix of the path as a public constant along with "\02-Drawings", then I have public variables for the client name, project name and discipline. These variables are then compiled in order whenever I need the full path.

    What I'm a bit confused about is the number of tables I would require within the database. Do I need one for Clients, one for Projects, one for Disciplines and one for Drawings? If so, how should they relate to each other?

    As things stand, when I run my code in AutoCAD VBA and populate the listbox with all drawings in the compiled path, I somehow need to relate the individual variables to the database. I'm probably demonstrating extreme ignorance here, but I was expecting to open the database, find table matching 'Client Variable Value', find linked table matching 'Project Variable Value', find linked table matching 'Discipline Variable Value' and then find the records for 'Drawing Variable Value' (taken from listbox selection). I'm probably completely wrong about this, but I was expecting/hoping/praying that an Access database could be structured in exactly the same way as the directory tree shown above.

    Thanks to all for the continued assistance. I will read all links provided in full as soon as I can.

  8. #8
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    but I was expecting/hoping/praying that an Access database could be structured in exactly the same way as the directory tree shown above.
    It can with the use of foreign keys in the tables.
    Give me about an hour and I'll knock up an example, assuming nobody beats me to it.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  9. #9
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Do I need one for Clients, one for Projects, one for Disciplines and one for Drawings?
    You also need one for revisions. Do you know how to form the relationships in access and what primary Keys are?

    Just some very basic guidance about creating a relationship in this example:
    In your drawings table you will have an auto number field which will be the primary key for that drawing. The number itself doesn’t matter at all so don’t worry about what the number is. It's just for relationship purposes. Let’s call this field Dwg_ID.

    In your revisions field you also have a primary key for every record but you now also need a foreign key for DWG_ID (this is the number of the drawing to which the record relates.) you then have the other fields which contain whatever data you want to put in there.

    So lets day drawing 001 has 5 revisions. In the revisions table you have something like this:
    Click image for larger version. 

Name:	Screenshot_4.png 
Views:	28 
Size:	1.9 KB 
ID:	38277
    In that example all details for the drawing are held on the drawing table and they are linked to revision 1-5 via the DWG_FK. Then Details for the revision are in this table. I hope this makes sense, you may already know all of this.

    It seems overwheling at first but once you understand how it works it's pretty straight forward.

  10. #10
    Join Date
    Apr 2017
    Posts
    1,673
    You need:
    1. tblClients: ClientID, ClientName, ...;
    2. tblProjects: ProjectID, ClientID, ProjectName, ... (ClientID here is foreign key. Unless you have joint projects where you have several clients for same project, there is no need for separate joint table tblClientProjects);
    3. tblDisciplines: DisciplineID, DisciplineName.
    4. tblProjectDisciplines: ProjectDisciplineID, ClientProjectID.
    5. tblDrawings: DrawingID, ProjectDisciplineID, DrawingNumber, ...;
    6. tblDravingVersions: DrawingVersionID, DrawingID, VersionNumber, ....

    You also can drop tblProjectDisciplines, and use Discipline field in tblDrawings instead, and in history subform, order records by Discipline, and then by DrawingNumber. Then the end part of structure will be
    3. tblDrawings: DrawingID, ProjectID, DrawingNumber, DrawingDiscipline, ...;
    4. tblDravingVersions: DrawingVersionID, DrawingID, VersionNumber, ....

  11. #11
    Join Date
    May 2019
    Posts
    10
    Thanks all. Beginning to see a glimmer of light at the end of this one. An example database I could take apart and examine would be highly beneficial Minty, thanks!

    I've come across the Primary Keys during the videos I watched, and kinda get what they're about I think. Not come across Foreign Keys though... There a literally hours and hours of good videos on youtube, but I admit to preferring a more hands-on approach to find out how things work. Just need a decent starting point which is why I'm very grateful to all who have assisted thus far.

  12. #12
    Join Date
    May 2019
    Posts
    10
    Afternoon All

    So, I've been going over your suggestions and having a play this morning, and I think I may have made some progress. However, I tried to create an input form so I could see if it's actually working, and I came a bit unstuck. I used the form wizard to create it, but it wouldn't let me enter data in half the boxes.

    I've uploaded the Access file to Dropbox and put the link below. If anyone would be so kind as to have a quick butchers over it and see where I've cocked up, I'd be most grateful.

    https://www.dropbox.com/s/9n8ugyhvcx...ker.accdb?dl=0

    Incidentally, I don't need a fancy input form in this database as I need to read from it and write to it via VBA, so just need a way of testing what I've done to make sure it works. Then I can set about attacking it through code.

    Thanks very much!

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

    When you set Referential Integrity the Default Option to select is Option 1 and Not Option 2. This applied to all your relationships.

    In the Modified very rough db attached I have included the related Primary /Foreign Keys with Colour Coding which I then Hide from the user when the Data Input is proven to work.

    Also, you should name your Objects with their respective prefix. You are naming Forms with the prefix of "tbl" and it should be "frm"
    Attached Files Attached Files

  14. #14
    Join Date
    May 2019
    Posts
    10
    Thanks for doing that Mike. It's very helpful to see the coloured boxes with the Foreign ID's in them.

    With regards to that link option, I was following an online example (obviously the wrong sort of example) and couldn't find any help in the help file when I clicked on the ? button on the link options dialog. It just took me to an overall help file, where a search didn't turn up much either.

    Your version seems to be working perfectly, now I need to see about accessing it via VBA.

  15. #15
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Glad to help

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

Similar Threads

  1. Replies: 1
    Last Post: 10-13-2016, 02:28 PM
  2. Replies: 3
    Last Post: 03-18-2015, 09:38 AM
  3. Replies: 5
    Last Post: 04-01-2012, 12:50 PM
  4. Beginners question
    By nashr1928 in forum Access
    Replies: 4
    Last Post: 07-19-2010, 11:20 AM
  5. a beginners question
    By bluelondon in forum Access
    Replies: 0
    Last Post: 07-12-2007, 04:54 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