VBA code compiling problem...

Here is a sample that should do what you want. you will need to adjust the target sheet name, the database and the query SQL.

Code:
Sub TransferTableFromAccess()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field
Dim MyConn
Dim i As Long
Dim ShDest As Worksheet
Dim sSQL As String
Const TARGET_DB = "yourDB.mdb" 'change to suit

Set ShDest = Sheets("TargetSheet") 'change to suit

'adjust sSQL to match your table and field names
sSQL = "SELECT * FROM tblYourTable WHERE YourField = ""Red"""
Set cnn = New ADODB.Connection
MyConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB

With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open MyConn
End With

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open Source:=sSQL, ActiveConnection:=cnn, _
CursorType:=adOpenForwardOnly, LockType:=adLockOptimistic, _
Options:=adCmdText

'clear existing data on the sheet
ShDest.Activate
Range("A1").CurrentRegion.Offset(1, 0).Clear

'create field headers
i = 0
With Range("A1")
For Each fld In rst.Fields
.Offset(0, i).Value = fld.Name
i = i + 1
Next fld
End With

'transfer data to Excel
Range("A2").CopyFromRecordset rst

' Close the connection
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing

End Sub


Denis
_________________
An ignoramus is someone who doesn't know something you learned yesterday.

Back to top

It is good code, however, I couldn't compile it. I put it to excel book, and it complains that "User defined error not defined"...

Setting a reference to the ADO library (Tools>References>Microsoft ActiveX Data Objects 2.x Library) should fix it.

I setup that, however, that doesn't seem work...

The previous problem got fixed, but the new one surface...