Update Excel records using Access

[Jamel]

I currently link multiple excel spreadsheet with an access database file because I often run update queries to update data in throughout my spreadsheets. This allows me to run update queries based on multiple conditions verse doing this with a long vlookup formula.
Also by doing it this way I can preserve formulas in the spreadsheet that would be lost if I did a full import and export of the spreadsheet.
In 2002 Access discontinue this functionality so I've be stuck using Access 97.
Does anyone have a workaround that allows me update date based on other spreadsheets data.

for example If one spreadsheet has data that includes name address social #account number( which is 4 parts). And the other spreadsheet has Social #and Job # and account number 4 parts.

And I want to update the first spreadsheet to include Job # based on where social # and acct number matches. How can I do this in excel.

Its easy to do in access!

Help

 

[alansidman]

If the spreadsheets are linked to Access, you could run a query in Excel using MS Query where you joined the two spreadsheets in the query on common fields and extract the information to a new spreadsheet with the combined information. Is this what you are trying to achieve? It is the same as if you were to join the two linked spreadsheets (tables) in an Access Query. If not, then some examples, as I am slightly confused by your explanation.

Alan

 

[xenou]

I'm guessing that Access can no longer update linked tables that are Excel spreadsheets? Right? You might enjoy ADO/DAO to work with Excel and Access. There's some good help if you google ADO Excel erlandsen consulting or DAO Excel erlandsen consulting. Also helpful are the ADO pages at w3schools.com

 

[Jamel]

Hello everyone,
Thanks again for the replies. My hesitation in responding right away stems from my life long fear of understanding the actual coding behind the scenes. I'm pretty good at creating pivots table in excel, using select/update queries in access, and creating basic vlookup formulas in excel, but I dont understand anything about visual basic, SQL, or DOA language. I believe I know enough of the logic when it comes to what the database/spreadsheet needs to do, I just don't understand how to write the code to get it to do what I need it to do. So instead, Ive mastered the queries and some formulas already offered in excel and access. From that I've acquired lots of understand of databases. But the code..., it frightens me!

This leads to the reason for my reply. I'm tired of running from learning access coding,visual basic, and sql. I know once I learn it I could do wonders in my job. Its amazing what I've done already with the limited knowledge I have. Everyone around my job really thinks I'm a computer guru. Even folks in our MIS department can't do some of the things I've figure to do by just using queries and linking excel spreadsheets to access.
I know once I learn the code/language thats involved I could really do some incredible things.
Perhaps even change careers which Im desperately feeling the need to do. I just don't have an idea on where to begin this journey.

I feel stuck with the knowledge I've acquired by just doodling around in excel and access. I've taught myself so much without knowing the language. Can anyone give me advice on what to do. I know I can learn this stuff but I dont know where to begin.

I need some advice. Should I take classes somewhere. If so what class? Where do I go?
Any advice would be greatly appreciated.

 

[Ruddles]

I think many of us learned by trial and error - I certainly did! Would you mind if I got you a bit further down the road by offering you a snippet of code which you can run from Access to read from and write to an Excel workbook?

Private Sub ReadWriteExcel()
 
  Dim objExcel As Object
 
  Set objExcel = CreateObject("Excel.Application")
  objExcel.workbooks.Open "c:\link_access.xlsx"
 
  objExcel.activeworkbook.sheets("Main").Range("A1") = "I was here!"
  MsgBox objExcel.activeworkbook.sheets("Main").Range("A1")
 
  objExcel.activeworkbook.Close SaveChanges:=True
  objExcel.Application.Quit
  Set objExcel = Nothing
 
End Sub

 

[xenou]

I read Excel VBA Programming for Dummies by Walkenbach just to get hold of the basics of VBA some years back. It's a nice intro, and he's the author of Excel VBA Power Programming (I may not have the title quite right), which is a highly recommended volume by many members of the board. It's a good idea to take a class on database theory (even community colleges will have one - unless you're sure you're sure you know the fundamentals, such as how to normalize a database, how to resolve many to many table relationships, how to pick good primary keys, what indexes do, and so on). VBA in and of itself isn't typically a career but its a good plus for many positions, as is a good understanding of Access/SQL (steeper learning curve on that side). Warning - it can be addictive. There's some interesting posts on this topic in the general discussion forum from time to time (I mean, leveraging Access/Excel knowledge, not getting addicted to it ). No worries using MSQuery though - there's a nice title by an author named Zapawa that deals with Advanced Excel reporting and makes a lot of impressive use of these tools. I only wish MS showed some interest in improving the product, though I'm seeing Excel 2007 and 2010 in general showing good native support for connecting to Access, SQL server, and other data providers.

 

[Ruddles]

Yes, I definitely agree with Xenou regarding good database design. You'll get away with bad database design at home but if you're designing a serious application, you should be able to normalise your data and identify the tables you need.

Regarding VBA, I have the O'Reilly "VB & VBA In A Nutshell" - I like the O'Reilly books and have several of them. The Web is a fantastic resource - I've lost count of the number of snippets of code I've found via Google, and although I've failed miserably to make some of them work, I've also learned a lot from looking at other people's code. Add 'msdn' to your search keywords to find the Microsoft info page on a given topic.

As regards VBA being "a good plus", yes, the first time you demonstrate that new button to make Excel do a complex series of operations in a few seconds which was previously taking someone half an hour to struggle through, or have Access automatically generate warning emails when stock thresholds are reached, people will look at you in amazement!

 

[Jamel]

Thanks Ruddles and Xenou.

Ruddles, I was able to copy and paste the code you gave me in a new module in access. Tried running it and it didnt work. So then I began looking at the code and noticed it was referencing a particular file and location. So I updated the code to reference an actual file and location and also created an spreadsheet with the same sheet name of "Main". Ran the code and it work. Don't understand code but was able to figure that one out. Hope this shows you how new I am to this. Remember I know none of this. I just always used the wizzards and query functions in access only. Importing and exporting to and from excel.
Is there a way I can modify that code to update an excel spreadsheet based on a relation of an access table and a linked excel file?

By profession Im an accountant that from time to time had to merge information on two different spreadsheets. Or I used update queries to
add information on a spreadsheet or table. That's the extend of my experience. Oh and I also served on I Financial software conversion team,
in which I developed our chart of accounts using access. I develop tables to crosswalk old account number component to knew accounts. Also converted lots of data, to new formats. Stuff like that. No coding.

Being on the conversion team enable me to understand databases. I Understand primary keys, data referential integrity, and how tables need to have a relation.

I'm looking for a new profession. What are some areas I should pursue? Im totally unsure if I could learn this stuff, but Im sure it would be something I
would like. I enjoy giving folks solutions that save time and energy.

Sorry if this is asking a bit much. I feel like Im at a crossroad in my career and I'm in need of some serious advice. Im a middle age seasoned employee who's eager to find something else less stressful.

Hope this isnt asking too much.

Your help will be appreciated.

 

[Ruddles]

I'm sure I'm not qualified to dispense career advice, however hopefully that snippet of code has given you some idea of how few statements are required in order to do something as complex as reading and writing Excel data from Access. You could equally read/write data in Outlook or Word from Excel, or in Access or Excel from Word, etc.

If I can explain one line of code:-

objExcel.activeworkbook.sheets("Main").Range("A1") = "I was here!"

objExcel is an 'object' of type Excel.Application, in other words it's Excel running just as if you double-clicked the Excel icon on your desktop except that the 'object' allows you to manipulate it with VBA.

activeworkbook defines the workbook which is active when you issued an Open command just previously.

sheets is a 'collection' of all the worksheets in activeworkbook and sheets("Main") is specifically the worksheet called Main. Range is a range of one or more cells and Range("A1") is specifically the top left-hand cell (of Main).

It's hierarchical: by stringing those elements together with dots, you're defining a single-celled range in a worksheet called Main which is in turn in the workbook whose name you used when you issued an Open command to the application defined by objExcel.

It's not totally straightforward but it's not impossible to learn this stuff. Eighteen months ago I'd never written a line of VBA in my life, although I'd used other programming languages in the past. Take it slowly - learn the simple stuff first like how to control program loops using variables, using IF..THEN (various forms) and how to navigate around a worksheet, reading and writing the contents of cells. As you learn more, you'll become more confident and more adventurous.

There are loads of resources on the Web - I tend to use Google a lot to fill in the gaps in my knowledge when I'm trying to do something new for the first time. Just enter the keywords for what you're trying to do - you'll soon learn which sites are the most useful, although you've obviously found one of the best already! Add 'msdn' to the keywords and you'll be offered a link to the Microsoft Developer Network which is sometimes difficult to decipher but has the definitve word on any given topic.

As for a career, I suspect there aren't many careers in VBA, although being competent might tip the scales if the other applicant for the job you want has never used it before.

Anyone else have any comments or suggestions?

 

[xenou]

Is there a way I can modify that code to update an excel spreadsheet based on a relation of an access table and a linked excel file?

I've heard somewhere that this is not possible ... linked Excel sheets are basically read-only. My 1 minute test just now confirms that you cannot update the Excel sheet from Access (the table has no "new record" row), and the Excel file is locked by Access while in use. Changes in Excel are picked up the next time you open the database, though. I'm sure you can write code to do this though ... back to the topic of discussion I guess.