Returning data from Access database to Excel

[michan31]

I have database already created, and what I like to do
is when someone type the number (unique id#) in Excel,
it will look up the information in Acess database and
return the information on certain cell in Excel. Do anybody know how to do this?

 

[DonkeyOte]

Say you have a variable for this ID number...

IDNUM = sheets("sheet1").range("a1")
TABLE NAME in Access is FORECAST

This would return the data resulting from the Query to A2.

You should be able to amend this fairly easily to suit your own needs.

NOTE - you will need to go to Excel VBA References and add in DAO 3.6 and Access Object Library (9.0)



Set dbs = OpenDatabase("R:currentFinanceMonth EndFy2003SAGE_TEST.mdb")

Set qry = dbs.CreateQueryDef(A, "SELECT NOMINAL, YYMM, AMOUNT FROM FORECAST WHERE ID = " & IDNUM & "")
Set rst = qry.OpenRecordset()

[a2].CopyFromRecordset rst

dbs.Close

 

[michan31]

My understanding is that I have to type the following to somewhere, but where do I exactly type them? You wrote "you will need to go to Excel VBA References and add in DAO 3.6 and Access Object Library (9.0)", but I don't know how to get to VBA References. I also don't know what DAO and Access Object Library are. Could you please explain little more on these? Thanks.

Set dbs = OpenDatabase("R:currentFinanceMonth EndFy2003SAGE_TEST.mdb")

Set qry = dbs.CreateQueryDef(A, "SELECT NOMINAL, YYMM, AMOUNT FROM FORECAST WHERE ID = " & IDNUM & "")
Set rst = qry.OpenRecordset()

[a2].CopyFromRecordset rst

dbs.Close

 

[DonkeyOte]

OK - you need to go into VB Editor - Tools, Macro - VB Editor. Then View - Project Explorer (note - this may be on already - it will be a list on the left hand sign of the screen that will show you VBA Project (name of your workbbok) - right click on one of the sheets and Insert Module.

You should now get a blank sheet on your right. This is where you enter the code - make sure your first line says

SUB Name Of Macro ()

At the end of the code you need End Sub

Any changes you need to make get done here - i.e. to amend the code to fit your needs.

DAO & Access 9 - are librarys - this code uses some terms that are not basic codes and thus need to be "added in" in order to make it work. So in the VB Editor go to Tools - References, scroll through the list and click DAO 3.6 & Access 9.0 to add them in.

Now you simply to amend your code to pick up the correct columns, Access db name etc..

When you want to run the macro - from Excel go Tools - Macro - Run and select your Macro by name from the list. You can also do this from the VB Editor by hitting the "play" button (looks like the play button on a CD Player etc)

Any more questions just reply to this.

Hope that helps.

 

[michan31]

I'm getting it little by litte, but I still have questions.

1. Can you tell me the meaning of the following code?

Set qry = dbs.CreateQueryDef(A, "SELECT NOMINAL, YYMM, AMOUNT FROM FORECAST WHERE ID = " & IDNUM & "")

2. Do I put the following code in the same place as others?

IDNUM = sheets("sheet1").range("a1")

Thanks.
I really appreciate you helping me.

 

[DonkeyOte]

On 2002-10-23 01:06, michan31 wrote:
I'm getting it little by litte, but I still have questions.

1. Can you tell me the meaning of the following code?


LASW10

The below code is effectively creating the SQL code that you would use to run your Query in Access. It's SQL code - and thus reads:

SELECT NOMINAL, YYMM, AMOUNT FROM FORECAST WHERE ID = IDNUM

IDNUM is simply a variable. So say IDNUM was the ID number that you put in cell A1 on sheet A1 in Excel...

At the top of your Macro (under the Sub MACRO_NAME()

You would specify what IDNUM is - e.g

IDNUM = Sheets("sheet1").range("a1")

So as far as Excel is concerned, whenever you quote IDNUM it will refer to Sheet1 A1

The rest of the code is simply setting up the qry (defining it) - don't worry about it too much - the main thing is to get the SQL right - so NOMINAL, YYMM, AMOUNT & ID are all columns in table FORECAST - and IDNUM will be specified in Sheet1 RangeA1 in Excel and will be one of the ID references in FORECAST table in Access - Column ID.

END MESSAGE


Set qry = dbs.CreateQueryDef(A, "SELECT NOMINAL, YYMM, AMOUNT FROM FORECAST WHERE ID = " & IDNUM & "")

2. Do I put the following code in the same place as others?

IDNUM = sheets("sheet1").range("a1")

Thanks.
I really appreciate you helping me.

 

[michan31]

The following is my code, and it gives me an error saying "Object required" on the line "Set rst = qry.OpenRecordset()". Do you know what is wrong with it? Also, is it possible to write more than one query and put the result in different cells?

Sub labels()
IDNum = Sheets("85x11").Range("b3")

Set dbs = OpenDatabase("D:db1.mdb")

Setqry = dbs.CreateQueryDef(A, "SELECT LabelName FROM LabelInfo WHERE LabelID = " & IDNum & "")
Set rst = qry.OpenRecordset()

[b4].CopyFromRecordset rst

dbs.Close

End Sub

 

[michan31]

Thanks for all your help. I figured it out.

 

[michan31]

Thanks for all your help. I figured it out.