Results 1 to 3 of 3
  1. #1
    gemadan96 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Oct 2012
    Posts
    110

    Asset Management Web Database

    In Access 2007 I've used the Asset Management Database template and customized it for a customer.


    It has five key tables: Assets, Contacts, Incoming, Outgoing, Transactions. I've made it as simple as I can for the majority of users. Their primary responsibility is when an asset is being delivered to a contact that it is assigned to that contact or when an asset is being returned it is checked in.

    The way it works now is when assets are being assigned to a customer a form is opened by selecting a customer. Using a barcode scanner all assets are scanned and entered in the form which is connected to the Outgoing table. When done the employee selects the Process Outgoing button. This runs a macro that updates the status of the asset and its location in the assets table, it appends the transactions table, then deletes the records from the outgoing table.

    When an asset is returned the incoming form is opened and the barcodes are scanned in. The process incoming button runs a macro that updates the asset status and location in the assets table, appends the transactions table, and then deletes the records from the incoming table.

    The customer would like to have this web based so I'm considering migrating it to Access 2010 and setting it up as a web database on Sharepoint. I know that this is limited to select queries.

    The question is how can I accomplish my update, append, and delete queries in a web database? Can it be done through macros? If so how?

    The incoming and outgoing append queries appends the barcode, owner, and date fields of the transactions table.
    The incoming and outgoing update queries update the assets table, based on the barcode, for the location, date, and owner fields.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Never built a web database, didn't know action queries were not available.

    Googled: access web databse insert query

    Review: http://answers.microsoft.com/en-us/o...6-de65021e1de0

    Appears options are to redesign database or maybe upgrade to Access 2010 so DataMacros are available, although I am not sure how DataMacros can help with action queries.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    The customer would like to have this web based so I'm considering migrating it to Access 2010 and setting it up as a web database on Sharepoint. I know that this is limited to select queries.

    That's not entirely true. An A2010 or A2013 web app can be a hybrid, meaning that it can contain both web objects (queries, forms, reports) and client objects. Client objects will only run inside the Access client, but still have the same functionality and design options that you're used to with older versions. Web objects will run in a browser over the www, but are much more restrictive as far as what you can do with them. You have to remember that anything you do with a web object has to be able to be interpreted by a browser (IE, Chrome, Firefox, whatever). The browser has no knowledge of, or way to interpret references to, any of your client objects. So it's not that you can't have action queries in a web app, it's that there is no way to run them from a web form.


    The question is how can I accomplish my update, append, and delete queries in a web database? Can it be done through macros? If so how?

    Well, if your intent is to be able to duplicate this functionality in a web form running in a browser, then you would have to rewrite everything with data macros using For Each Record In / Create Record In or For Each Record In / Edit Record In loops.


    Before you take on this project, you really should sit down with your client and determine what exactly it is they want. If they would be satisfied with using most of the existing functionality from within the Access client and they just want a few forms or certain data available over the web, then it might not be a huge undertaking. On the other hand, if they want the whole thing available on the web then you could be looking at spending hundreds of hours completely redesigning the entire application almost from the ground up. There is no way to convert existing client objects to web objects. You would have to completely recreate all your forms, code, reports, everything except maybe the tables. In that case, you would need to determine whether Access is even the right tool for the job. You might be better off using a real web based platform (.Net or whatever)


    BTW - If you're going to cross post in different forums, then it is considered common courtesy to inform us of that fact so efforts are not duplicated by the volunteers who respond in these forums. I have posted this answer in both forums.

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

Similar Threads

  1. MS Access + Revit = Asset Management?
    By ThatSkepTicGuy in forum Access
    Replies: 2
    Last Post: 07-26-2017, 08:06 AM
  2. Personal Property - Asset or Inventory Database?
    By PoliticalOperative in forum Database Design
    Replies: 1
    Last Post: 05-31-2012, 03:03 PM
  3. Inventory/Asset tracking database.
    By russkris in forum Access
    Replies: 7
    Last Post: 04-26-2012, 02:27 PM
  4. Asset Tracking Database
    By wgroenewald in forum Access
    Replies: 3
    Last Post: 02-13-2012, 08:26 PM
  5. PTO Time Management Database
    By Stanggirlie in forum Programming
    Replies: 1
    Last Post: 03-27-2009, 09:33 AM

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