Linking Excel to Access data

[JohnnyT]

I have an Access database for customers with Customer Name, Customer Address (blah blah, all the usual fields)

I also have an Excel sheet for projects which requires the inputting of the customer name and address.

My question is this...

Is there any way to link the Excel customer name box to the Access database so it would provide me with a drop down box to select the Customer ?

In an ideal world, this would also automatically fill in the customer address fields...

Is this possible ?

I'm new to both Excel and Access so any help you could give me would be greatly appreciated.

All the best

John

 

[FacilitiesCAD]

Do you need to use excel? I'm new to access and and using excell for more than a calculator with many cells to store/put data. But I just learned how to make pull down tabs in Access to do what your talking about in acess alone. If you say it will help I'll try to explain how to do that.

Tim

 

[JohnnyT]

Thanks for the reply.. unfortunately I do need to use Excel, the reason being that the project sheet also costs the jobs done to that particular project (ie. hours spent on job at whatever rate, parts bought etc etc)

I also want to expand this Excel sheet to automatically generate our invoices.

So.. unfortunately, we need to do this in the Excel. I just want the facility to have the Access as the central point for all the customer details.

Thanks.

John

 

[FacilitiesCAD]

The other things you mention could be done in access but I see your point. Good luck.

Tim

 

[JohnnyT]

Thanks for your help Tim. Anyone else got any ideas about this ?

 

[cbiber]

I use financial software (QuickBooks) to do what you're after -- but probably not to the complexity you need. Plus you'd have to move the database manually if it's in Access... never mind!

 

[JohnnyT]

cbiber

I haven't started populating the database as yet. Although I've done the work on the Excel sheet. Where could I get hold a copy of QuickBooks or have a look at what it can do? Is there a website ?

Cheers

JT

 

[cbiber]

Try quickbooks.com. I got mine at my local computer & office equipment superstore. It was about $250 if I remember correctly and it'll save you a whole lotta programming...

 

[JohnnyT]

cbiber

Thanks for the info.. I'll give it a look.

Cheers

JT

 

[cpretty]

I have done something similar to this pulling data out of access. I used the query tool in excel to query the database on another sheet, then put the data across using some simple VBA.

Record a macro of pulling data in as a query. Open up the VBA editor and have a look at the code that was created. The name that you are looking up can be replaced in the sql statement by a variable taken from the initial spreadsheet. You will probably need to change whether it updates the range, or overwrites it (one of the options after the statement.

Link this macro to a button on the main sheet.

I will endevour to pull my code out for you if you need more help on this, but it isn't too hard, and is a useful trick to know how to do for all sorts of other things.

Cheers,
Craig

 

[JohnnyT]

Craig

Thanks for the help mate. I'll try that and see how it goes.

Cheers

JT

 

[tomatge]

There are relatively simple ways of getting data from Access (and other database sources) into Excel.

From the Excel toolbar, choose Data > Get External Data > New Database Query.

From the list of data sources, select Access, then follow the fairly simple MS-Query route to obtaining the data you require. The end result will be an area of your spreadsheet that is linked with the data. You can then choose various options for that area, such as when to update the query etc.

This will only get you a relatively inflexible link to your database, but is is quick and simple. You can, of course, make the link much more flexible and conveneient by coding in VBA (as suggested above). But VBA database coding is not that straightforward, particularly for the novice. There are numerous books on the subject, my choice being "Mastering Database Programming with Visual Basic 6" by Petroutsos.

 

[JohnnyT]

tomatge

I had looked into that method of linking data to access but, as you say, its a little inflexible for my application.

I'll give the VB way a go and make a macro from a button as suggested. If it all goes Pete Tong, I'll turn to using QuickBooks as mentioned earlier by cbiber.

Thanks for all your help lads.

Cheers

JT

 

[cpretty]

If you send me a copy of your database

cpretty"remove"@talk.co.nz

I will have a go over the weekend. Sounds like a neat little project that will help my skills.

Cheers,

Craig

 

[JohnnyT]

No worries, I'll get a copy over to you. Although I don't think it will be tomorrow because I'm out on site. I'll certainly get it over to you before w/e though and you can have a look.

Cheers

John
(webmaster"StopSpam"@biscuitsbrown.com)