Results 1 to 5 of 5
  1. #1
    Fixer is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2015
    Posts
    7

    Moving from programming in Excel to programming in Access

    For the past year I have been working on automating the processes for my employer in Excel to reduce the amount of 'human error' analysts put into the data when they move it around. I am over 250 pages of code at this point and am being asked to move it into Access because we are also hitting well over 800k rows of data and might hit the 1 million mark by this time next year.



    What sort of differences should I expect to have to learn to code for Access as opposed to Excel?

    What the code will need to do:

    SQL Data pulls
    Forward results into Excel

    (the rest I will need to know I already know how to do in Access, so these are the only two new items)


    I am presuming I can do these in Access, and can try to guess how they might be done, but I want to hear from those who have already done so if I am going to have a rough time of it or not. Right now I am using SQL drivers in Excel but would LOVE to move to an ADO data pull structure to remove the need for those drivers (if possible).

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Generally speaking in Access you'd have linked tables so to pull the data you'd simply have a query that pulled the data out the way you wanted. To push that to Excel, you can use OutputTo, TransferSpreadsheet or Excel automation. You could certainly use an ADO recordset to pull the data and use Excel automation to populate Excel.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Two methods of coding in Access - macros and VBA. Macros in Excel are really VBA procedures. Macros in Access are very different.

    What do you mean by 'SQL Data pulls'?

    If by 'Forward results into Excel' you mean export data - that can be fairly easy.
    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.

  4. #4
    Fixer is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2015
    Posts
    7
    Quote Originally Posted by June7 View Post
    Two methods of coding in Access - macros and VBA. Macros in Excel are really VBA procedures. Macros in Access are very different.
    Ok, I will avoid macros, then. I am much better with VBA.

    Quote Originally Posted by June7 View Post
    What do you mean by 'SQL Data pulls'?
    SELECT
    FROM
    WHERE
    GROUP BY
    HAVING

    SQL queries

    Quote Originally Posted by June7 View Post
    If by 'Forward results into Excel' you mean export data - that can be fairly easy.
    Ok, good. Just have to learn how to do it in VBA.



    Quote Originally Posted by pbaldy View Post
    Generally speaking in Access you'd have linked tables so to pull the data you'd simply have a query that pulled the data out the way you wanted. To push that to Excel, you can use OutputTo, TransferSpreadsheet or Excel automation. You could certainly use an ADO recordset to pull the data and use Excel automation to populate Excel.
    Thank you. I was fearing I was going to have to learn .NET to pull this off. That is not currently in my skillset.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Access has built-in query building aid.

    As Paul noted, tables would likely be linked - this means a split database design.
    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.

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

Similar Threads

  1. Need help in programming in ms access 2007
    By ericjw316 in forum Access
    Replies: 2
    Last Post: 10-23-2014, 10:42 AM
  2. Why MS-Access programming?
    By vincent-leeway in forum Programming
    Replies: 10
    Last Post: 07-29-2013, 06:44 AM
  3. Importing from Excel (programming part)
    By dsaxena15 in forum Programming
    Replies: 1
    Last Post: 10-15-2012, 07:47 AM
  4. Programming Access - is there a quicker\better way?
    By shabbaranks in forum Programming
    Replies: 2
    Last Post: 08-25-2012, 10:42 AM
  5. New to Access programming
    By pushpm in forum Programming
    Replies: 1
    Last Post: 02-20-2009, 03:03 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