MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Extract data from mdb

  Asked By: Alex    Date: Mar 14    Category: MS Office    Views: 5344

I need to extract some data from a mdb file (access) without open it,
and do some operations with that data in excel... have some ideas for
writing the vba code in excel for that???



7 Answers Found

Answer #1    Answered By: Jet Brown     Answered On: Mar 14

How many records are you talking about?
In Excel, you can set up an external data  source and
have it populate an Excel sheet.
Then, you can refresh the data to update it.
Of course, that will only work if you're retrieving 65,000 records or less!
(I used Data -> Import External Data)
hope this helps,

Answer #2    Answered By: Ludkhannah Fischer     Answered On: Mar 14

ok, I get it, thanks!
Now, how can automate reading always from the same mdb?

Answer #3    Answered By: Sairish Kauser     Answered On: Mar 14

This technique sets up a worksheet that ALWAYS reads from the mdb  you've
When you select the sheet with this external data, there is a toolbar that pops
up that
has an "Refresh All" button to update the sheet with the latest data  from the
If you're trying to update it with VBA, then you use:

it's been a while since I designed my sheet (a couple of years), but if I
remember correctly,
you either retrieve the entire Access table, or you retrieve data based on a
query stored in the MDB.

Answer #4    Answered By: Javairea Akram     Answered On: Mar 14

I believe that ADO will do this.... Not sure though... Anyone else??

Answer #5    Answered By: Laura Rodriguez     Answered On: Mar 14

An example using ADO

Sub GetData()
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\bob.mdb"

sSQL = "SELECT * From Contacts"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
If Not oRS.EOF Then
ary = oRS.getrows
MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0)
MsgBox "No records returned.", vbCritical
End If

Set oRS = Nothing
End Sub

Answer #6    Answered By: Spiru Kelly     Answered On: Mar 14

You can also interact with the database itself using normal ODBC SQL
statements. Plenty of examples available with a Google search.

Answer #7    Answered By: Jenny Lopez     Answered On: Mar 14

I wrote the following code:

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=K:\Registro de Planos\SRP
Planos;" _
), Array( _
Access;MaxBufferSize=2048;PageTimeout=5;;UID=admin;")), _
.CommandText = Array( _

some stuff here

FINAL`.`00_PLANOS_V` `00_PLANOS_V`" & Chr(13) & "" & Chr(10) & "WHERE
`00_PLANOS_V`.OBRA = 'numobra'))" & Chr(13) & "" & Chr(10) & "ORDER

Some struff here

End With
End Sub

I need to include some code  to ask me for a string varible
called "numobra", a then the query table must retrieve all the data
coincident with the string "numobra". See where statement.

Didn't find what you were looking for? Find more on Extract data from mdb Or get search suggestion and latest updates.