Importing a range of data from excel into existing Access table and overwriting data already in that table

[NewbieMan]

I have an Access macro which basically opens an Excel spreadsheet and then copies a range of data from that spreadsheet. I want to then import that range of data into an already existing Access table overwriting the info already there.

There are just a couple of caveats...1) In the existing Access table, I want to keep the first record untouched and overwrite everything after it with a range of equal size in terms of rows and columns 2) the first column in the Access table cannot be touched.

I tried the below ....

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Coaches", "C:\A\B\saves\ImportingCoaches.xls", True, "A1:BG1408"

but this appears to be rather stiff in that you can tell it what range you want to copy from Excel but cannot customize it to paste into Access avoiding the first column and begin one row down from the first record.

Does anyone know if this is even possible? or have an idea how I would import the range I specify in Excel to an existing Access table while leaving the most left hand column untouched and pasting one row below the first record in that table.

Any help and or thoughts with this would be greatly appreciated...if my full code in both Access and Excel are needed I would be happy to post.

Thanks again in advance

 

[Joe4]

I am kind of confused by what you mean. I think you may be mixing up your "row" and "column" references in your explanation.
Note that in Access speak, you do not have "rows" and "columns". Rather you have "records" and "fields".

If you wanted to keep only the first record in the current Access table, then you would first run a Delete Query to delete everything except for that row.
Then you would insert your data into the Access table to add the new data.

Is that what you are after?
If not, try explaining again, using a data sample if it helps.

 

[NewbieMan]

Thanks Joe for the response. Let me try again and I apologize for my "loose" lingo when it comes to my question.

Lets use an Access table of 3 Fields and 3 records. The 3 Field headings might be something like ID, First Name, Last Name. Record 1 might be as follows: 1, Joe, Smith. Record 2 might be: 2, Sam, Mitchell and the last record might be: 3, John, Jones. The data I am copying over from Excel however would be 2X2 comprised of First Name and Last Name only - excluding the ID field as well as excluding the first record so: 1, Joe, Smith would remain. However the First Name and Last Name for records 2 and 3 would be overwritten by the new First Name and Last Name imported from Excel but the ID value of 2 and 3 under the ID field would not be overwritten so the 2 and 3 would not be touched.

Does this help you visualize things better? Your idea about deleting is an interesting one. I would like to give that a shot however if my description now helps a little more any help you or others can provide would be greatly appreciated. My data however is much larger then 2X2 or 3X3 its more like 60 fields with 1500 records that I would be pasting under the first record.

Thanks again to all.

 

[Joe4]

This seems to be a rather odd request, so I hope to understand it better, as it affects how we want to approach this.

Can you explain why you would keep the ID, but want to overwrite all the other data? Is the ID arbitrary, or does it relate to something important (what does it represent)?
Does order of the records matter?
Another way of looking at my question is, if all the other data is changing, what must ID stay the same?

Also, will the number of records being imported always match the number of records you are replacing (minus your first record)?

 

[NewbieMan]

sure...the ID is attached to several links and tables and experience has taught me that even trying to append a record is problematic with respect to the ID field. In the past I have manually copied and pasted the data from Excel to Access (without the use of macros) and overwriting the ID in each of the records results in all sorts of errors. But copying and pasting the remaining part of the record works fine. I did not originally create this large project and breaking it down and redesigning would be a huge undertaking. But again, I am aware that by copying and pasting the remaining part of all the records causes no difficulty.

As far as your last question about the number of records being imported always being fixed, the answer is yes, its about 1508 records or so and never changes.

Thank you.

 

[Joe4]

Is the ID field an Autonumber field?
If so, and there is no reason why certain records must have certain IDs, the easiest (and cleanest) thing to do is to delete the entire record, and just import your data and let Access assign it a new ID number (do not try to assign it in your import or append - let Access handle it).

If you must keep using the same ID, that suggests that the following must be true:
- the number of records does not change
- there is a correlation between each specific record being deleted and each specific record being imported/added, so order matters (for example, the record with ID number 100 being deleted corresponds to record number 100 being imported)

Is this the type of relationship you have?
Or are the ID fields really arbitrary and have no real meaning and correlation with the records being deleted?

 

[NewbieMan]

Hi Joe et al,

After frustrating myself with this yesterday I decided to finally give up lol. Instead I got my macro in Excel to get to the point where 90% of the work has been automated. The only step I will need to do manually is physically highlight the range of cells in Excel and Copy and Paste them in the appropriate location in the Access table. This saves alot of time no doubt. Its really unfortunate that what I was attempting to do involves so much trouble and time.

Thank you again for all your efforts as well as anyone else that might have taken time to contemplate how best to resolve this, perhaps down the road I will revisit this.

Cheers,

 

[Joe4]

I definitely think we could have accomplished what you were trying to do, we just needed a greater understanding of your data structure and relationships between your data. All those questions I was asking are key to understanding that. It is important for us to understand all that in order to propose the best method of accomplishing what you need to accomplish. Quite commonly, new Access users get hung up on something because they don't really have a complete understanding of how Access and relational databases work.

By the way, I would HIGHLY recommend NOT copying/pasting data from Excel into Access. That can be flaky and unreliable. It is better to either import and link your Excel data in to Access.

I will gladly help you work through it if you like, but we really need to understand this ID field you have, its significance and the relationship of it between Excel and Access.

 

[NewbieMan]

I definitely think we could have accomplished what you were trying to do, we just needed a greater understanding of your data structure and relationships between your data. All those questions I was asking are key to understanding that. It is important for us to understand all that in order to propose the best method of accomplishing what you need to accomplish. Quite commonly, new Access users get hung up on something because they don't really have a complete understanding of how Access and relational databases work.

By the way, I would HIGHLY recommend NOT copying/pasting data from Excel into Access. That can be flaky and unreliable. It is better to either import and link your Excel data in to Access.

I will gladly help you work through it if you like, but we really need to understand this ID field you have, its significance and the relationship of it between Excel and Access.

Joe, you are 100% correct in everything you have noted. I think the biggest impediment is honestly my lack of knowledge at this point. I think in order to have a more constructive dialog I need to spend a little more time with Access before I ask more questions. I sincerely appreciate your help and being gracious with your time to continue to offer to help. I just think I need to do my work on my end so as not to create confusion or miscommunicate. I have made progress in my project, granted not fully, however when I feel more comfortable with the lingo and database operations etc I will be back Just dont want to waste too much of your time with non clarity on my part.

Again sincerest thanks...in a couple of weeks I will be ready to tackle this again

Cheers,

 

[Joe4]

You are welcome.

Feel free to post any questions you may have (that is what we are here for ).