Solved: Excel to get Data from an Access Database

[DaveR]

I am currently attempting to get Excel to get some information from an Access File (currently set to Read Only).

The Access databse has just 4 columns. Postcode, Postcode2, X_Coord, Y_Coord.

What I want do is to VLOOKUP from an Access database.

Is this possible?

The reason for this, is that I'm not keen on putting the 1,778,023 * 4 cells into an Excel page. It would be technically feasable (26 columns of 65535), but I suspect that Excel would crawl to a halt.

Help!

[acw]

Hi

I don't think you can do a VLOOKUP into the access database, but you can bring in data from access to excel that matches your criteria. Can you give more detail on what you data looks like, and what it is you are trying to bring in from Access to match the excel data. Do you have a series of items in a column that you want to match? Or is it only one item?


Tony

[debauch]

"Data" > "Import External Data" > "New Database Query" ... Select MS Access Database * and the wizard will help walk you through it. // You can also limit the data you choose to bring over .

[DaveR]

My data in access has 4 columns

Postcode Posctcode2 X_Coord Y_Coord
nn1 2xb NN12xb 432564.31 675423.65
nn1 2xc NN12xc 432568.67 675428.67


What I am trying to do is to have a postcodes in excel that os then matched in Access and puts the X,Y details accross

Does that clarify it?

[acw]

What does excel hold? Both Postcode and Postcode2? Also where are these detail(s) located in excel?

[DaveR]

The Postcode data in Excel is in Column AL.

What I am trying to do it lookup the corresponding X,Y details from the Access database (which are held in X_Coord and Y_Coord)

Currently, I do this with a sub set (Outcodes) of the full postcode data.

i.e. =VLOOKUP(AL3,'Postcode data'!$A$2:$C$3221,2) for the X Co-ord
and =VLOOKUP(AL3,'Postcode data'!$A$2:$C$3221,3) for the Y Co-ord

Additionally, I work out the distance between two postcodes with

=IF(AL3="",0,(SQRT((VLOOKUP(LEFT(AL3,4),'Postcode data'!$A$2:$E$3221,2)-VLOOKUP(LEFT(AJ3,4),'Postcode data'!$A$2:$E$3221,2))^2+((VLOOKUP(LEFT(AL3,4),'Postcode data'!$A$2:$E$3221,3)-VLOOKUP(LEFT(AJ3,4),'Postcode data'!$A$2:$E$3221,3))^2))/1000)/1.6093)

Ideally by accessing the complete postcode database, it won't need to be imported into Excel.

Additonally, I was exploring the 'Import External Data' option in Excel yesterday and this seems to work , however it's limited to the 65535 rows. I couldn't see any way to split the data into 'chunks of 65535.

I think ( and hope) that just referencing the data from Access will be somewhat quicker than importing the 5.1 million bits of data I would have to import in Excel for a full list of postcodes.

Hopefully, it's clearer?

[acw]

Dave

Assumptions:
1) postcode in the spreadsheet in column A starting in row 2
2) Access database is c:\temp\test.mdb
3) Table in access called Pcode
4) Pcode has 4 fields (Postcode Posctcode2 X_Coord Y_Coord
)
5) Data in Pcode as per example above.

In the VBE, go tools references and select microsoft DAO... for whatever version you have.

[VBA]
Sub ddd()
Dim wj As Workspace
Dim db As Database
Dim rs As Recordset

Set wj = CreateWorkspace("", "admin", "", dbUseJet)
Set db = wj.OpenDatabase("c:\temp\test.mdb")

For Each ce In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
Set rs = db.OpenRecordset("select [pcode].[x_coord], [pcode].[y_coord] from [pcode] where [postcode] = '" & ce.Value & "'")
ce.Offset(0, 2).Value = rs.Fields("x_coord")
ce.Offset(0, 3).Value = rs.Fields("y_coord")
Set rs = Nothing

Next ce

Set db = Nothing
Set wj = Nothing


End Sub
[/VBA]

The above code will cycle through the entries in column A and bring back the relevant x / y coordinates.

HTH

Tony

[DaveR]

Thanks for you help.

I'll try it a bit later when I'm at work (it's just before 7am here!) and will report back on how it goes.........

[DaveR]

I have made some modifications to the code so it fits in with my particular circumstances. The code is now
[vba]

Sub ddd()

Dim wj As Workspace
Dim db As Database
Dim rs As Recordset

Set wj = CreateWorkspace("", "admin", "", dbUseJet)
Set db = wj.OpenDatabase("C:\Documents and Settings\dtrignall\Desktop\whole postcode with GR.mdb")

For Each ce In Range("A3:A" & Cells(Rows.Count, 1).End(xlUp).Row)
Set rs = db.OpenRecordset("select [whole_postcode_with_GR].[X_coord], [whole_postcode_with_GR].[Y_coord] from [whole_postcode_with_GR] where [Postcode] = '" & ce.Value & "'")
ce.Offset(0, 4).Value = rs.Fields("X_coord")
ce.Offset(0, 5).Value = rs.Fields("Y_coord")
Set rs = Nothing

Next ce

Set db = Nothing
Set wj = Nothing


End Sub

[/vba]

But on running I'm getting 'Run time error 1004 - Application-defined or object-defined error'.

Any ideas or clues?

[acw]

Dave

Did you set the reference in the VBE as per

In the VBE, go tools references and select microsoft DAO... for whatever version you have.

Tony

[DaveR]

Yes.......

[acw]

Dave

Hmmm. I created the directory structure as above, created a database with that name, a table with the fields and it all worked for me.

Can you attach a spreadsheet with your structure, and if size permits (maybe zip things) a copy of the database as well.


Tony

[Ken Puls]

Hi guys,

I have an article at my site that shows the ADO version of doing this, here.

Don't know if it helps, but it is an alternative to the DAO route.

Cheers,

[DaveR]

Due to size limitation I can't.

Would it be OK to email it to you? If you PM your email, I can then send the full files.

[DaveR]

Sorry for the delay in emailing the files over to you......

I have tried to reduce the Access Postcode database down to something more manageable, as it's currently 103MB. However, when I delete the unwanted data, the size stays the same!

I will endeavour to solve this difficulty and then send you the files...

Anyone know how to get Access to 'forget' the deleted data?

[acw]

Dave

Do a compress on the database. Tools, Database Utilities, compact....


Tony

[DaveR]

After trying again with real postcodes (and not just the outcodes I was using originally!) the code works as described!

Can a nice moderator please mark the posting as SOLVED.

The difficulty I experienced was because of my own stupidity!.

As a final thought, the code doesn't 'behave' when there is no match so I have added

[vba]
Private Sub Workbook_Open()

Application.ScreenUpdating = False 'turn off screen updating

Dim wj As Workspace
Dim db As Database
Dim rs As Recordset

Set wj = CreateWorkspace("", "admin", "", dbUseJet)
Set db = wj.OpenDatabase("C:\Documents and Settings\dtrignall\Desktop\whole postcode with GR.mdb")

On Error Resume Next

For Each ce In Range("B3:B" & Cells(Rows.Count, 1).End(xlUp).Row)
Set rs = db.OpenRecordset("select [whole_postcode_with_GR].[X_coord], [whole_postcode_with_GR].[Y_coord] from [whole_postcode_with_GR] where [Postcode] = '" & ce.Value & "'")
ce.Offset(0, 2).Value = rs.Fields("X_coord")
ce.Offset(0, 3).Value = rs.Fields("Y_coord")
Set rs = Nothing

Next ce

Set db = Nothing
Set wj = Nothing


Application.ScreenUpdating = True 'turn on screen updating

End Sub

[/vba]


The 'On Error Resume Next' function is used as this nicely sidesteps the vba error code! If anyone wants to suggest a 'better' solution then please suggest away.

Again, I would like to thank ACW (Tony) for his patience with me and for all his help and advice in this matte.r