Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Lounger
    Join Date
    Jul 2008
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unhappy Check Database in MS Access 2010

    Hi,
    I am from India and developing an Access database set to have 3 Million Check records issued for payment of Loan. This will include both Dated and Undated checks captured with other 10 fields of information about the Loan. These will be periodically imported to Database from excel files having those 10 fields. Require help & support on the below points:
    1. We will import both dated and undated checks into Database once but can it get moved to 2 different tables automatically?
    2. How to capture System Login Names along with Date & Time of the User who import the data?
    3. How to capture Audit Log for each import or other events of modification?
    4. How to Customize Data Collection via email in Access Table in similar to excel

    Thanks
    MSivasa

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,538
    Thanks
    0
    Thanked 23 Times in 23 Posts
    1. We will import both dated and undated checks into Database once but can it get moved to 2 different tables automatically?
    You could import them into a table, then use 2 append queries to populate the 2 different tables...

    2. How to capture System Login Names along with Date & Time of the User who import the data?
    Have you got a for where the user logs into the database or is it the windows login you are after? You just need to create an entry into your login table.

    3. How to capture Audit Log for each import or other events of modification?
    An audit log is just another table to populate, logging modification takes extra code in the AfterUpdate of fields changed or in the AfterUpdate event of the form for those fields where the value is not equal to the Original value of the field.

    4. How to Customize Data Collection via email in Access Table in similar to excel?
    I don't know what you mean by this??

    What you will have to be careful of is the size of the database, it peaks at 2Gb.

    Hope some of this helps
    Last edited by patt; 2014-07-15 at 03:12.

  3. #3
    Lounger
    Join Date
    Jul 2008
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Patt for the revert on this.

    Required help on the below:
    1. I am planning to create a 2 backend tables for Dated & Undated checks, but not sure whether the limitation of Access applies for Table size of 2GB is for back end tables as well ?, if so please advise a better way forward ?
    2. To capture User id I created a Custom Function as below and still unsuccessful to call that function in builder under Default mode ?
    Public Function GetUserLogin()
    GetUserLogin = Environ(“UserName”)
    End Function

    3. Please advise how to set an Audit log for each event by selecting a status from Dropdown in Table & Forms ?
    4. With regards to Data Collection, A record in Table has various fields in a Table. Now the User has to key in each fields for a record separately to collect data via email. My requirement is already in a excel. Please suggest a way of using the ready data from excel for data collection ?

    Let me know if you require more clarity on my requirement?

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,525
    Thanks
    213
    Thanked 854 Times in 786 Posts
    MSivasa,

    This code works for me.
    Code:
    '                         +-------------------------+             +----------+
    '-------------------------|       SystemInfo()      |-------------| 04/12/10 |
    '                         +-------------------------+             +----------+
    
    Function SystemInfo()
    
       Dim zInfo As String
       
       zInfo = "User Name:" & vbTab & Environ("USERNAME") & vbCrLf
       zInfo = zInfo & "Home Drive: " & vbTab & Environ("HOMEDRIVE") & vbCrLf
       zInfo = zInfo & "Home Path: " & vbTab & Environ("HOMEPATH")
       
       MsgBox zInfo, vbOKOnly + vbInformation, _
              "Current System Information"
    
    End Function             'SystemInfo()
    SysInfo.JPG
    Perhaps it is the Uppercase Variable names you need?

    HTH
    Last edited by RetiredGeek; 2014-07-19 at 13:46.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,538
    Thanks
    0
    Thanked 23 Times in 23 Posts
    Required help on the below:
    1. I am planning to create a 2 backend tables for Dated & Undated checks, but not sure whether the limitation of Access applies for Table size of 2GB is for back end tables as well ?, if so please advise a better way forward ?
    You can have many backends, don't know the limit though, each one can be 2Gb
    2. To capture User id I created a Custom Function as below and still unsuccessful to call that function in builder under Default mode ?
    Where were you using code to call this function?
    Public Function GetUserLogin()
    GetUserLogin = Environ(“UserName”)
    End Function

    3. Please advise how to set an Audit log for each event by selecting a status from Dropdown in Table & Forms ?
    This depends on exactly what and where you are trying to save this data. Would you give more info here.
    4. With regards to Data Collection, A record in Table has various fields in a Table. Now the User has to key in each fields for a record separately to collect data via email. My requirement is already in a excel. Please suggest a way of using the ready data from excel for data collection ?
    Would you please explain this in more detail.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    3,871
    Thanks
    7
    Thanked 255 Times in 240 Posts
    I would also collect machine name, IP address and MAC for an audit log.

    cheers, Paul

  7. #7
    Lounger
    Join Date
    Jul 2008
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,
    I have set the Database with 27 Fields while the Excel which I import has only 24 Fields, where the additional 3 fields in Database are calculated fields in Table. I tried the regular import/append table but found not working. Either it overwrites the Table structure or data not getting imported. Request help on the below:
    1. Best way or ways for importing data from Excel to Access table on a daily basis?
    2. Those 3 fields are Date & Time of Import, Username of Import & Condition status
    3. I am planning to create a table for the import data in FE based on dates & condition, these data gets appended or moved to BE tables. Please suggest whether this is fine or other better way?
    4. I happened to see 2 sample forms from web matching my requirement which also has VBA codes. How can I adapt it to my database? Please suggest.
    5. I require an Audit Log for all Checks been modified /deleted post they imported once, with fields like Customer No, Check details, old value, new value, Username who performed this action and Date & time.
    Please do let me know if you require more details.

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,525
    Thanks
    213
    Thanked 854 Times in 786 Posts
    Mohanss,

    1. Link the Excel Table.
    2. Create an Update Query that uses the Excel file as it's source and your Access Table as the Updated Table.


    HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  9. #9
    Lounger
    Join Date
    Jul 2008
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi RG,

    I m not clear with your above Update Query. If you are referring to making Excel as Linked Table, Its not possible as there are 3 Mn records...

  10. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,525
    Thanks
    213
    Thanked 854 Times in 786 Posts
    Mohanss,

    And why is this a problem? Unless your Excel file exceeds 2Gb. You 're only using the linked table long enough to run your update query (which may take quite a while if you don't have a way to extract the new/changed records for successive updates). Now if your data exceeds 2Gb you have a much bigger problem using Access vs SQL Server.

    Maybe a fuller description of your setup would be useful for us to give useable advice verses pulling it out piece meal.
    Things like:
    Fieldnames, Data Types, Length (e.g. your table structure / Excel setup)?
    How are you getting the data into Excel?
    Will you be updating the Access via a limited extract of only current data?

    See where this is going?

    HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  11. #11
    Lounger
    Join Date
    Jul 2008
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts
    As informed earlier, this Access database is set to have 3 Million Check records issued for payment of Loan. This will include both Dated and Undated checks captured with other 20+ fields, with Customer number which is unique but with no primary key, bcoz records can be repeated in few scenarios.
    Fields - DataType
    Customer Number – Number
    Date Fields – Date/Time
    All other fields – Text
    It works like User imports one excel file daily to Access db, it stores in table in FE and splits the records per conditions and move it to 3 BE tables accordingly. These Excel data is mostly filled with manual entry and limited to the import, hence this DB is designed for tracking and maintaining them. Primary purpose of this DB is feed-in only once via import, then it works on its own and no need for any manual updation.
    Attached Images Attached Images

  12. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,525
    Thanks
    213
    Thanked 854 Times in 786 Posts
    Mohanss,

    Is the Front End Db file retained after the records are split off to the Back End Tables? If the FE DB is not retained but regenerated each day I see no need for it as you could just link the Excel file and do your updates from there. Just use the same name for the Excel file and everything should work just fine. Am I missing something?
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  13. #13
    Lounger
    Join Date
    Jul 2008
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts
    RG,

    These FE Tables will be retained and will be empty post splitting the records to the Back end tables.... Excel cannot handle more than 1Mn records where my current data is already 3Mn. I want all my data to be consolidated at one source. So Linking Excel Files will not help my purpose....

  14. #14
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,521
    Thanks
    3
    Thanked 50 Times in 50 Posts
    Sorry for stepping in late, but it appears to me there is a communication problem here. On 9 August you said
    Hi RG,

    I m not clear with your above Update Query. If you are referring to making Excel as Linked Table, Its not possible as there are 3 Mn records...
    yet on 10 August you say
    RG,

    These FE Tables will be retained and will be empty post splitting the records to the Back end tables.... Excel cannot handle more than 1Mn records where my current data is already 3Mn. I want all my data to be consolidated at one source. So Linking Excel Files will not help my purpose....
    I believe that the daily Excel workbook is nowhere near 1 million rows, much less 3 million, so it should be possible to link to the daily Excel workbook, import the records from it to the Access table, run the process to split the records out to the three back-end tables, clear the Access table, and then at the appropriate time link to the next workbook and repeat the process.

    My larger concern would be the size of the tables that contains the 3 million records currently, and will be growing over time. If you have 3 million records, and each record averages 300 bytes - not an unreasonable size with character fields and decimal values and dates, etc, then you are already half full. I would look at a more robust database engine such as SQL Server, Oracle or db2 for the back-end storage. Access is an excellent choice for smaller database, and always my choice for a front-end to large database, but for important data, which this seems to be, I want a database engine with more robust backup capabilities, and less likely to corrupt. Hope this helps clarify things.
    Wendell

  15. #15
    Lounger
    Join Date
    Jul 2008
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Wendell,

    Thanks for the valuable inputs and these are already in the plans in the future.

    I tried to adapt couple of Sample forms to my DB, but unable to adjust its VBA to my requirement. My immediate requirement is build a Search form to check for the records across 3 BE tables. Require suggestions and help on this.

Page 1 of 3 123 LastLast

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
  •