Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2007
    Location
    Harrisburg, PA
    Posts
    44

    Unanswered: How to automate a table to add a column and requery every night

    The warehouse portion of the company I work for just enacted a moving inventory system where bin locations for a certain part will change everytime stock runs out. This way, new parts will have room to be located.

    The inventory system we have right now (Sage's MAS200) does not store old locations anywhere. I am tasked with figuring out how to automate something in access that will query the SQL Database for the day's bin locations and what parts are in them, and then store them if they have changed. This would run everyday but may not actually change everyday.

    I feel like this task is very complex so I am looking to other professionals for help. My base table would be something to the effect of:

    Part_Number:_____________Bin_Location(10/29/2007):
    ABC123__________________a-01-a-01
    DEF456__________________a-01-a-02

    How can I add a new column daily (up to 31 columns, one month's worth) that will requery the database for bin locations, create the new column, and then place the data?

    The best I can think of so far is having a maketable query run every night and somehow tell it to make a column eachtime it runs.

    Please Help!!!

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If it's a SQL Server backend, design a DTS package.

    If it's an Access backend, create an mdb with an append query (or make table query). Create an Autoexec macro (or set the startup to run code to do the append query. Set the warnings to false first in the macro or code. Then schedule the *.mdb to run via Windows Scheduler.

    Not sure why you need to create a new column though when this sounds like info that should be populated into an existing column (I'm not a fan of creating a new column/field every time for new data) but you can easily design some code (or your make table query) to make the new column. Seriously though think about designing your table structure so you don't have to make new columns and instead populate the info into an existing field. Creating new fields/columns for new info is not a good technique. Eventually you will come across the maximum # of fields (columns) a table can have and then your dealing with making multiple tables, always modifying your forms, reports, etc..etc... This can be very painful and maintenance heavy. (unless I've misinterpreted what you mean with column verses a new row of data.)
    Last edited by pkstormy; 10-29-07 at 16:06.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Oct 2007
    Location
    Harrisburg, PA
    Posts
    44
    I think you understood me correctly. I am so used to columns because my mind always tells me that the first column or our part number should be the Primary Key. I need to think outside the box a little more.

    I suppose a new row could work, if I stored the part number as a non unique value (not primary key). That way, I could have a row with Part Number, Bin Location, and the Date in a third column. Then, I could sort by date and filter by the part number if I needed to find specific records.

    That sounds like it would mesh, eh???

    Thanks! Ill let you know how it goes.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Yes! That sounds a LOT better verses creating a new column every time. You're on the right track - Good thinking.
    Last edited by pkstormy; 10-29-07 at 17:04.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Oct 2007
    Location
    Harrisburg, PA
    Posts
    44
    Ok, just to cover all my bases here...

    My company has just over 22000 different parts in our database. If I am creating new rows instead of columns, should I be worried about a maximum row number? If so, what is it so I can be ready for that problem.

    Thank you.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I believe the maximum size of an MSAccess table is 1 gigabyte (which could vary on the number of rows depending on how many fields/information you have.) The maximum size of an Access *.mdb is 2 gigabytes.

    1 gigabyte is a lot of information (a few million or so rows) and I'd be really surprised if you ever reached the limit. I've heard about someone who had 23 million records in an Access mdb. If you do reach the limit, you could always upsize to SQL Server.
    Last edited by pkstormy; 10-30-07 at 10:43.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Oct 2007
    Location
    Harrisburg, PA
    Posts
    44
    My company uses a SQL server, but I am not authorized to edit data from it, only read it...

    SOOOO... I have to do it all in Access. I doubt I will reach that number either. Thank you for the info. I am testing it now and it seems to work correctly. Now I just need to figure out how the automation thing works.

    Any suggestions there?

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Oy...

    I would not use Access to do this. A DTS/SSIS package with a designated database on the SQL Server is where this should be happening. Here are a few reasons why:

    • Access cannot be trusted to run as a scheduled job. You can use Windows Scheduler or some other mechanism and pray that you don't run into any issues spooling up Access, or pray that it was already open. Either way, not a solid idea.
    • You could very well hit the file size limitations in an ERP/MRP management system modeled in Access. As mentioned, there is a 2gb limit, and performance suffers well before that point.
    • You are decentralizing where your data lives. You now have inventory management data at a separate, inaccessible, disparate location.


    That's just off the top of my head. Now if you MUST use Access for some reason, I would go with windows scheduler and preferably something to tell you whether or not the job completed properly. Access doesn't have a way to do this, you have to do it on your own. Which is why Access is not suitable for mission-critical scheduled tasks.

    I've personally done this using a small executable whipped up in vb6 (sub language of your choice) that would fire off the the scheduled task in Access, kept track of where it was in the process via a client-side storage mechanism (a table in Access in this particular case) then did data verification to make sure everything went off without a hitch.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If you only have Access tables to work with and you need to automate something (see my 1st post in this thread). Make a new *.mdb which automatically runs an append query on startup (don't forget to set the warning to false before the append query and true after the append query.) - And then exits the mdb. You can link the table from your actual data *.mdb into this *.mdb after you tested everything on a sample table first (i.e. File -> Get External Data - Link Tables.).

    Test this out on a sample table first. Open the mdb and see if it automatically ran the append query and appended the records and then exits the mdb.

    Then as Teddy suggested (and my 1st post suggested), use Windows Scheduler to schedule the *.mdb to run when you want it to run.

    DTS on SQL Server would be the ideal situation but it sounds like you don't have the ability to tap into SQL Server. If you knew vb scripting you could also create a vb script to run and update either the Access table or SQL Server. I attached an example vb script to show you how something like this would be done. I use this script to update the LoginStatus of a user when they login (edit the vb script in notepad to see the code.) As you can see, it's not that difficult. But for now, you can do this easily enough with Access and Windows Scheduler.
    Attached Files Attached Files
    Last edited by pkstormy; 10-30-07 at 12:51.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Oct 2007
    Location
    Harrisburg, PA
    Posts
    44
    Update:

    Thank you, pkstormy and Teddy. I have my computer performing sceduled tasks for the table I was working on. Everything looks to be going smoothly. I will also be using your opinions (as intelligent minds in the field) to sway my mangers to let me have access to the SQL database. Then I get to teach myself a whole new program.

    Fun Fun!

    Thank you again!

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by AndyCJPonyParts
    I will also be using your opinions (as intelligent minds in the field)
    Hehe

    And Andy, I'd like to congratulate you on having a name that makes me smile everytime I write it

    AndyCJPonyParts
    AndyCJPonyParts
    AndyCJPonyParts
    AndyCJPonyParts
    AndyCJPonyPants
    AndyCJPonyParts
    George
    Home | Blog

Posting Permissions

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