Results 1 to 7 of 7
  1. #1
    Join Date
    May 2008
    Location
    Big Lake, MN
    Posts
    18

    Question Unanswered: Questions regarding a "ticketing system"

    Hey folks,

    I've been given a project, and I'm looking for input on the best approach on how to put it together.

    I will be using Access 2007. I'm good with Oracle SQL syntax, I don't believe Access will be too different. I have a little experience with Access, and also very little (self taught) experience with VB. Please dumb everything down for me

    A little info so you can better understand the goal

    This program is supposed to be a ticketing system of sorts. The criteria in which a ticket would be created is based off data pulled from an Oracle DB. I can get the data from Oracle loaded/appended to a table fine, but after that is where I'm not sure how to approach it. I should also note, this is to be hosted on Sharepoint.

    I don't expect to have many tables. There will probably be one for the tickets themselves, and another table to track performance. We have routes, and each route will have a ticket created for it (and assigned to an individual). I plan to have a table to hold the tickets themselves, and a seperate table, that will get updated once a week, to hold a snap shot of the route's performance at that given time. Having the weekly entry for each route will allow for historical reporting later on.

    Some Questions..

    - User IDs/Logins.

    From what I was told, Access 2007 doesn't really "support" user accounts(compared to 2003), perhaps I have a misunderstanding there. Is the simplest way just to create a users table with enough fields for the ID, password, ect? If so, how does one go about storing what user is logged in at a given time? That would be helpful for any queries/reports for something assigned to that given person.

    - Primary keys/Sharepoint

    Since this will be on Sharepoint, should I expect to run into any issues with table design? IIRC, tables must have the primary key field titled ID. Any tips on playing nice with sharepoint would be helpful.

    - Automatic append?

    Is there a way I could set up something in Access to run at a specific time and append data to a table? I'd like to avoid having to have someone hop into Access and 'kick off' a query.

    - Avoiding duplicates

    There will not ever need to be more than one ticket open for a given route. Each week I'd like to have a row entered into the tickets table for any route ID that isn't currently there, without duplicating any. Should this be as simple as an insert into, where route ID not in([superdupersubquery])?

    If anyone has seen a template that looks along the same lines as this, that would rock. I've done some searching, but I haven't seen anything that I could use for ideas.

    Any help at all would be greatly appreciated!

    - Dan

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    - User IDs/Logins.

    From what I was told, Access 2007 doesn't really "support" user accounts(compared to 2003), perhaps I have a misunderstanding there. Is the simplest way just to create a users table with enough fields for the ID, password, ect?
    *** 07's .accdb format does not support the User Level Security - - though the .mdb format does. But it is a sign that this feature is out looking forward so I would not design a new db using the old format. Thus, yes, you must create your own - - a table is sufficient, and you must realize that this is adequate for casual users and not for any one with hacking intentions....

    If so, how does one go about storing what user is logged in at a given time? That would be helpful for any queries/reports for something assigned to that given person.
    *** somewhat of a different question; you will need to also develop your own log on/off table - pretty easy to do.


    - Primary keys/Sharepoint

    Since this will be on Sharepoint, should I expect to run into any issues with table design? IIRC, tables must have the primary key field titled ID. Any tips on playing nice with sharepoint would be helpful.
    *** I don't really think there are any db design issues per se that impact sharepoint....but sharepoint is a big product area with its own development techniques; so perhaps the more fundamental question to consider is whether this a sharepoint project rather than an Access project.


    - Automatic append?
    Is there a way I could set up something in Access to run at a specific time and append data to a table? I'd like to avoid having to have someone hop into Access and 'kick off' a query.
    *** Access is "Event Driven" by definition. Which means it is intended to have a user at the screen triggering things...your autoexec to trigger your append at start or something at close or some trigger along the way.... Access is desk top - - it is not server based running as a service...again you may want to explore as to whether you actually have a Sharepoint project on your hands rather than Access.


    - Avoiding duplicates

    There will not ever need to be more than one ticket open for a given route. Each week I'd like to have a row entered into the tickets table for any route ID that isn't currently there, without duplicating any. Should this be as simple as an insert into, where route ID not in([superdupersubquery])?
    *** you make the table's field property to be Indexed/No Duplicates and it will not allow duplicate values to be entered. If you use AutoNumber it will generate a unique value automatically when any data is entered into the record. Access is multiuser and any record can be "opened" - in terms of users looking at the same record at the same time - - - this will not cause a glitch. From your description it sounds like you mean that you need a routine, when triggered that will find missing IDs - and create records for them. This is advanced but normal db programming and completely capable with Access. Probably too complex for this type forum however.

    ***Am giving you the vanilla Access perspective - - - of course one can custom program lots of things to go way beyond Access' core offerings - but you need to be a good code jockey.


    If anyone has seen a template that looks along the same lines as this, that would rock. I've done some searching, but I haven't seen anything that I could use for ideas
    *** doubtful

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by HouseMN View Post
    I'm good with Oracle SQL syntax, I don't believe Access will be too different. I have a little experience with Access, and also very little (self taught) experience with VB.
    Just some general advice. People coming into Access/VBA assuming that it "can't be too different" from this language or that language are destined to have a very, very hard time! Access SQL is different from Oracle SQL, in many ways! And despite sharing a common ancestor (QuickBasic 4.5) Visual Basic and Visual Basic for Applications are very different, as well! And to make matters even worse, VB and VBA share many, many functions with identical names that do not work in the same way! Also note that even among different flavors of VBA there are differences. VBA for Access, for instance, is different in many ways from VBA for Excel.

    You will be doing yourself a huge favor if, before embarking on this project, you take the time to find and read a basic primer on Access VBA!

    Good luck!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    May 2008
    Location
    Big Lake, MN
    Posts
    18
    Thanks for the feedback!
    If I had any wisdom to impart, I would do so here.

  5. #5
    Join Date
    May 2008
    Location
    Big Lake, MN
    Posts
    18
    I came across another situation where I have a question on how to best approach this.

    I have a split form, and there are four boxes for the user to set up search criteria, 3 combo boxes, 1 check box.

    The form's record source is just all records from the table it's built on. Applying filters based on what is selected in the boxes should suffice for what I need.

    doCmd.Applyfilter works fine, the issue is that if not all boxes have input, it'll error out if I have just one applyfilter with all boxes included the WHERE clause.

    Before I go and write it to check each box for a value, and then make a long If statement based off of what does and does not have a value, I wanted to see if there is a better way to accomplish that.

    I have code for a "Replace Where Clause" sub, but that seems like just as much work.

    Once again, any thoughts or insight would be appreciated!
    If I had any wisdom to impart, I would do so here.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    as you are using Oracle as your back end I'd strongly suspect you will benefit fromnot jsut a primer in Access VBA but a good quality books such as 'Access Develoepr Handbook' for what ever version you are using.

    theres stuff all point in using a server backend and then developing the application in Access as if it is a JET backend. I'd stronly recommend that you use unbound recordsets/controls and so on. and if you are new to the field (heck if you are wroking in the field) get a goods quality book and spend some time learning about how to develop in Access using unbound recordsets.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    May 2008
    Location
    Big Lake, MN
    Posts
    18
    I did just pick up Access 2007 VBA Programmer's Reference yesterday, I haven't had much time to read up yet.

    A little back story, I'm an Oracle database analyst by job description, so "making stuff" in Access isn't my forte. I just happen to be the guy that might be able to make a band-aid for something we aren't getting any assistance from software development on.

    I'll definitely check out what you mentioned as 'strong recommendations'.

    Thanks!
    If I had any wisdom to impart, I would do so here.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •