Inserting Records In The Middle Of An Access Table

  • Hi Access VBA Gurus,

    I am a beginner to Access 2002, let alone Access VBA. I have a quick question. Attached is a database of just a single, sample 'Month-End Outstanding Accounts' table. There are 3 customer ID's, and each month I need to INSERT end-of-month balances into this table at the respective, logical slots (provided end-of-month balances for a given customer are positive).

    Assume CustomerID 2 has a balance of $150,000.95 as at 30/06/2006, while CustomerID 1 and 3 each have zero balances as at this same date. I will thus need to insert just a balance of 150000.95 as a new record for CustomerID 2, with Date field entry as 30/06/2006, as record number 10 of 12 in the attached 'AcctRcvLog' table.

    How do I do this? Note that each month-end 'source data' is contained in an Excel spreadsheet with a 3-column structure similar to the attached 'AcctRcvLog' table. So, the Excel source file dated 30Jun06 will have just a 2-row table, with first row being a header row with entries [CustomerID, Date, Amount O/S], and second row being [2, 30/06/2006, $150,000.95]. Please note that as balances for CustomerID 1 and 3 as at 30/06/2006 are zero each, these do not appear in the Excel source data file.

    If anyone could advise, this humble Access VBA newbie would be eternally grateful.

    Thanks a million in advance.


  • Re: Inserting Records In The Middle Of An Access Table

    Hi there,

    In terms of adding new records to a table in an Access Database, why do you care about the order? Surely, as long as the relevant ID field has the appropriate value, the order of the record in the database should not make a difference?

    Later on, you can always tweak the order all you want in the queries and reports based on this data.


  • Re: Inserting Records In The Middle Of An Access Table

    well, you can import the zero balances in as well, then create delete query to remove the zero balances.

  • Re: Inserting Records In The Middle Of An Access Table

    Hi there "Humble Access VBA Newbie".

    The first issue you need to address is the structure of your table.

    You should set a key and in this instance, in order to maintain multiple records per client, you will need a composite key made up of CusomerID and Date. If the key were to be just CustomerID, you would be only able to maintain one record per customer and just update date and amount - unless this is what you are trying to do, although this is not how I have interpreted your post. So with the composite key, you will be able to maintain an entry for each customer, for each period that they have an outstanding amount.

    Next we address getting the data into the table and this raises a question. Are you wanting to do this programmatically, or are you happy to do it manually?

    Manually, as long as the columns in the spreadsheet are in the same order as the table ie CustomerID, Date, Amount, even if there are other columns in the spreadsheet, you can copy and paste the data straight into your table (leaving out the column headers). I am hoping that the previous poster was taking your post title a little too literally as you will be adding these records to whatever records already exist ie to the end not inserting into the middle somewhere. Access can take care of the sequence, especially if you use the composite key I have suggested above. When retrieving the data in a report or query, it is simple enough for the result to be ordered by customer and date.

    If you are wanting to do it programmatically, this humble, somewhat experienced but not expert Access/VBA programmer suggests that you are perhaps aiming a little high when it comes to chosing a first project for learning Access/VBA. If you are in a hurry to resolve this then I would suggest you recruit a professional to do it for you.

    If you are not in a hurry, but are happy to utilise the manual method I have outlined for a while, I am considering producing a "template" Access app which will demonstrate how data can be moved between Access and Excel. Either getting data from Excel or pushing it to Excel seems to be a pretty common issue and I think deserves this sort of attention. I would seek to perhaps have this sample added as a "stickie" or at least post it into the sample code forum on this site.

    I have stated I am considering this as I am both a student and a tutor at one of the local universities. This, plus maintaining a small number of private clients, means that spare time is somewhat precious. It may be that someone else has already done this so I suggest a troll through the forum here which offers sample code - I will certainly do so before embarking on building a sample of my own.

    Hope the above helps.



Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!