Check Database from Excel to Access 2010

[Mohanss82]

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

[June7]

1. Why two tables? What do you mean by automatically? Must be code in some event. The code could be in the procedure that does the import.

2. I use Environ("USERNAME") to capture the network user login

3. Not sure what you want, but review http://allenbrowne.com/AppAudit.html

4. What do you mean by 'Customize Data Collection'?

[Mohanss82]

Thanks..

1. I have these 2 types of Checks, so when I import one single XL file daily to DB, which should automatically move them into 2 tables based on the date.

2. I tried this formula in the Default Value for datatype as both Short text, but didnt work... I am prompted with msg as Unknown function
3. Will have to Look out more on the AppAudit...
4. Instead of entering each fields of Table separately, cant those values copied from excel and emailed in the same layout ?

I am new to VBA so require more help and support.

[June7]

2. Access does not recognize the Environ() function. Use it in VBA. A VBA custom function can use the Environ() function then Access can call the custom function.

4. Are you referring to the Collect Data functionality in Access? I am not familiar with this in Excel so cannot compare. You need to be specific about what you want to do.

[Mohanss82]

Thanks again.
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?

[June7]

1. Access file size limit is 2GB, doesn't matter if it's frontend or backend

2. calling function in textbox DefaultValue property works for me

3. I've never built an audit log. The Allen Browne reference is only one of many on the web.

4. Don't understand. Access has Collect Data feature. Doesn't matter what you have set up in Excel. Build in Access.

[azhar2006]

http://windowssecrets.com/forums/sho...MS-Access-2010

[Mohanss82]

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.

[June7]

You are asking for a lot of info and I am not interested in writing a book in a forum thread. You need to tackle one task at a time and deal with specific issues in individual threads.

1. There are several ways to import data and the 'best' depends on your data structure and business requirements. No idea why your attempts fail.

2. Fine, 3 additional fields to populate with DefaultValue or code

3. I would import directly to the BE tables

4. Without knowing your db and the code, no way to advise on adapting

5. Already addressed

Good luck and when you encounter specific issue, post question in a thread. Give attempted code for analysis. Or provide db.

[Mohanss82]

Thanks for the inputs.
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 simple Search form to check for the records across 3 BE tables. Require suggestions and help on this.

[June7]

'check for the records across 3 BE tables' might not be simple. Are these 3 tables identical in structure (same field names and same kind of data)?

Here is one way to search for records http://www.datapigtechnologies.com/f...tomfilter.html

[Mohanss82]

Thanks again.

I have built a basic search form using a Query and Button to run it for FE table. I am still having 2 issues as below

1. I am still facing issues while importing the data from excel to FE table which then to 3 different BE tables. My Excel has 24 Fields, while DB has 27 Fields, Those 3 fields are Date & Time of Import, Username of Import & Condition status. Either I see the DB Table Structure gets overridden or no import is happening. Pls suggest.

2. I have created a Union Query by consolidating all the Tables (both FE & BE). How should I declare the Parameter, so that I can use it for the Search form which I created ?

[June7]

I suggest building another query that uses the UNION as source. Or instead of an Access query object, just use SQL statement in form RecordSource.

SELECT * FROM [the union query name] WHERE ID = Forms!formname!controlname;

[Mohanss82]

Thanks.

Need ideas on below;
1. Any suggestions on capturing the Date & Time for each Import along with Username for each Import?
2. If I convert the DB to an EXE file to protect the DB from an editable form, Can the Queries, Forms, Reports, Macros & Other Access objects be added to the DB in EXE file?

[June7]

1. If you use the import wizard, run UPDATE query on the new records

2. I do not think so. That is the reason for an executable, so design cannot be modified.