Excel to Access Import.

[thisisntwally]

I'd like to preface my question with a brief introduction to my situation. Since its creation, use of my mdb has been hesitant and spotty at best. I attribute this to my own failure to streamline the input features. The important part here is that some of the analysts have been choosing to log their information into excel files. Rather than forcing everyone to update this backlog one entry at a time into the database, I would like to see about doing this for them...

I plan on giving them until a future date to transfer their logs into standardized excel files to make this easier on myself. I will combine these so that im working with a single and ideally laid out spreadsheet.

From there I would like to import the files into the existing database, which is properly normalized, and has already seen some use.

There seem to be two approaches i've seen.
1. import the spreadsheet into a temporary table, then use an append query to relocate the cells into the proper tables.
2. use a VB script to import the data directly from the excel files into the appropriate tables. (can i do this, or do they all have to go into the same table?)

Q1 Are these my only options (neither sounds too terrible) or do any of you have a different technique by which one might come at this?

Q2 If not, and supposing that i use the programatic approach, does anyone forsee a problem in appending the data to the proper tables with VB? (I'm still exploring how this is done, and want to make sure im not wasting my time)

 

[gemma-the-husky]

i tend to use route a) you suggest, although i am sure either method is appropriate. there may be practical issues you come across, eg finding records already there, and you need some way of establishing a primary key to avoid such duplications of data.

the user interface is generally important - in order to get maximum acceptance. it needs to be as easy to use as possible

offhand, you dont want to have multiple identical data tables - you want to put everything intpo a single table distinguished by eg, the user id.

having said that, making sure that users cannot see each others data is not necessarily a trivial task - depends how used you are to coding techniques

 

[thisisntwally]

Thanks G-dawg! Your post brings up an issue that I had yet to consider, the probablility of duplicate entries. This is a problem, because there is no natural PK, only an autonumber established within the database. Beyond that, it is only the unique combinations of certain fields none of which are unique onto themselves.

I'm leaning towards the programatic route, because it is looking like the import process may be a repeated task and thus i would like to automate the process as much as possible. Some of my more aged users have been tracking the necessary info for many years and don't have a stong incentive to adapt (something over 80% are within 5 years of retirement).

im thinking that once i have the data in the database (Q3 am i correct in my assumption that I can only import data into one table?) I can cross check the entries for uniqueness, and append the entries which pass. I'll then submit the remainder to further inspection.

 

[gemma-the-husky]

no, you can import into whatever table you want

the command is something like (including these parameters)

docmd.transferspreadsheet (fromspreadsheet, totable, rangetoimport)

but you THEN need to process the import - if you have a standard query, you need to get it to use this new table - you can either do this directly with vba, or alternatively copy the just imported table to another table with a standard table name for processing - then you can use a stored query

 

[thisisntwally]

having said that, making sure that users cannot see each others data is not necessarily a trivial task - depends how used you are to coding techniques

Is this regarding Access or Excel? While my coding skills are still in a developmental level, i seem to pick it up a brisk rate, so complex coding is by no means out of the question, in fact i rather enjoy being in well over my head.

Q4 Can an excel file be scripted so as to provide different individuals see 'different' spreadsheets? just curious...

RockettoRussia.xls
Marky sees field1 field2 field3
Joey sees field2 field3 field4 field5
Johnny sees field1 field3 field4 field5

Dee-Dee, being the awesome one saves me tons of time and inputs his data directly into the DB.

 

[thisisntwally]

no, you can import into whatever table you want

the command is something like (including these parameters)

docmd.transferspreadsheet (fromspreadsheet, totable, rangetoimport)

but you THEN need to process the import - if you have a standard query, you need to get it to use this new table - you can either do this directly with vba, or alternatively copy the just imported table to another table with a standard table name for processing - then you can use a stored query

Ok. So as long as im cross-checking as i import, I can divert the failures to a temp-table, and drop the rest into their final tables. This is begining to look like fun!