locked
Build a Form to Append Table RRS feed

  • Question

  • Hey guys, you are in luck. I'm very new to Access development, and I now have an opportunity to build a somewhat complex Access database from the ground up! I'll be relying on some of you to help me. Of course I'll be taking ALL the credit, but..let's talk about that later. I'm more or less an expert in Excel/VBA development, so the learning curve is not that great. I understand MS development concepts, VBA for Excel, etc. I'm certain most of what I know already is portable to Access. 

    So..my first step was to create a table to contain daily uploads of data from Excel. I did this in the designer so I could create a date stamp field and a primary key based on two fields from the data. NOW..I need to learn how to upload the data to the table and attach that process to a form. Ultimately I'd like an admin-level user to use the form to upload the data. Ideally I'd like the form to prompt for the path and file name of the Excel file and upload the data from there. The format will be exactly the same for every form. I supposed I could incorporate some kind of error handling, but right now I just need the basics. 

    So..somebody walk me through it. Please. 

    tod

    Monday, November 16, 2015 11:51 PM

Answers

  • Hey guys, you are in luck. I'm very new to Access development, and I now have an opportunity to build a somewhat complex Access database from the ground up! I'll be relying on some of you to help me. Of course I'll be taking ALL the credit, but..let's talk about that later. I'm more or less an expert in Excel/VBA development, so the learning curve is not that great. I understand MS development concepts, VBA for Excel, etc. I'm certain most of what I know already is portable to Access. 

    So..my first step was to create a table to contain daily uploads of data from Excel. I did this in the designer so I could create a date stamp field and a primary key based on two fields from the data. NOW..I need to learn how to upload the data to the table and attach that process to a form. Ultimately I'd like an admin-level user to use the form to upload the data. Ideally I'd like the form to prompt for the path and file name of the Excel file and upload the data from there. The format will be exactly the same for every form. I supposed I could incorporate some kind of error handling, but right now I just need the basics. 

    So..somebody walk me through it. Please. 

    tod

    Hi Tod,

    Your question is rather broad and has a lot of steps involved.  As DBGuy suggests, you should first understand the fundamentals of relational database design and have a solid table structure developed before you do anything else.  Your table structure is the heart of your application.  If it isn't right, everything else will be wrong and you will spend countless hours trying to work around the resulting problems.

    Next, you might spend some time in the Help files learning about transferring data from Excel.  The first command you might want to get familiar with is DoCmd.TransferSpreadsheet.

    Once you have an understanding of how to manage application automation and the actions for transferring data, you can then begin to learn about designing your interface to handle your desired tasks.

    DB Guy has given you one good resource.  There are many others on the web which you can easily find using a Google search. 

    Though MS Help is not as good as it use to be, don't discount its worth.  Many of the answers you will seek are right at your finger tips.

    One thing I personally consider important is naming conventions.  When you begin designing your system, you need to have a solid naming convention established for naming the parts and objects of your application.  The most important rule to keep in mind is that object names should be brief, understandable to others, and should not contain any spaces or special symbols, and always be consistent.  As a best practice, names should appear in Camel-Case notation, where the first letter of each word is capitalized, e.g. MyTableName, MyFieldName, etc..  The following link will give you some additional information about naming conventions typically used in MS Access.  There is a lot of other good information to found at the site as well.

    http://access.mvps.org/access/general/gen0012.htm

    Best of luck with your application.

    RM


    • Edited by RunningManHD Tuesday, November 17, 2015 4:05 PM
    • Marked as answer by todtown Tuesday, November 17, 2015 10:28 PM
    Tuesday, November 17, 2015 3:55 PM

All replies

  • Hi tod. One good thing about knowing how to do things in VBA is that you can pretty much make the program do anything you like. However, Access is not the same as Excel - meaning, to use it properly, you'll have to build a "relational" design. In Excel, you treat the data as a flat file. In Access, it works better if the data is treated as a "relation." So, if you don't have any background in relational database design, may I suggest that you take a look at some of the resources listed on this page at UtterAccess. Good luck!
    • Proposed as answer by Tony---- Tuesday, November 17, 2015 2:41 AM
    Tuesday, November 17, 2015 1:44 AM
  • thanx
    Tuesday, November 17, 2015 3:39 AM
  • thanx
    Just wanted to make sure you were aware what you're getting into. Good luck!
    Tuesday, November 17, 2015 1:54 PM
  • Hey guys, you are in luck. I'm very new to Access development, and I now have an opportunity to build a somewhat complex Access database from the ground up! I'll be relying on some of you to help me. Of course I'll be taking ALL the credit, but..let's talk about that later. I'm more or less an expert in Excel/VBA development, so the learning curve is not that great. I understand MS development concepts, VBA for Excel, etc. I'm certain most of what I know already is portable to Access. 

    So..my first step was to create a table to contain daily uploads of data from Excel. I did this in the designer so I could create a date stamp field and a primary key based on two fields from the data. NOW..I need to learn how to upload the data to the table and attach that process to a form. Ultimately I'd like an admin-level user to use the form to upload the data. Ideally I'd like the form to prompt for the path and file name of the Excel file and upload the data from there. The format will be exactly the same for every form. I supposed I could incorporate some kind of error handling, but right now I just need the basics. 

    So..somebody walk me through it. Please. 

    tod

    Hi Tod,

    Your question is rather broad and has a lot of steps involved.  As DBGuy suggests, you should first understand the fundamentals of relational database design and have a solid table structure developed before you do anything else.  Your table structure is the heart of your application.  If it isn't right, everything else will be wrong and you will spend countless hours trying to work around the resulting problems.

    Next, you might spend some time in the Help files learning about transferring data from Excel.  The first command you might want to get familiar with is DoCmd.TransferSpreadsheet.

    Once you have an understanding of how to manage application automation and the actions for transferring data, you can then begin to learn about designing your interface to handle your desired tasks.

    DB Guy has given you one good resource.  There are many others on the web which you can easily find using a Google search. 

    Though MS Help is not as good as it use to be, don't discount its worth.  Many of the answers you will seek are right at your finger tips.

    One thing I personally consider important is naming conventions.  When you begin designing your system, you need to have a solid naming convention established for naming the parts and objects of your application.  The most important rule to keep in mind is that object names should be brief, understandable to others, and should not contain any spaces or special symbols, and always be consistent.  As a best practice, names should appear in Camel-Case notation, where the first letter of each word is capitalized, e.g. MyTableName, MyFieldName, etc..  The following link will give you some additional information about naming conventions typically used in MS Access.  There is a lot of other good information to found at the site as well.

    http://access.mvps.org/access/general/gen0012.htm

    Best of luck with your application.

    RM


    • Edited by RunningManHD Tuesday, November 17, 2015 4:05 PM
    • Marked as answer by todtown Tuesday, November 17, 2015 10:28 PM
    Tuesday, November 17, 2015 3:55 PM
  • Thanx RunningManHD. DoCmd.TransferSpreadsheet was the seed I need to grow my idea! 

    I fully understand that the way I'm going about this is not text book, academic, know everything before you start. I never learn anything well with that approach. I learn more by breaking sh#t and then fixing it. :0) I don't doubt that my first workbook will be less than stellar. So..I'll throw it out and build a new one. At the very least I'll know a whole lot more than I did when I started, and that's more than I can say from reading a tutorial on how to build a database.

    Thanx. I'll certainly be back with a billiondy gazillion questions. I've asked and answered that many in the Excel developer forums.

    tod

      

     

    Tuesday, November 17, 2015 10:21 PM