ExcelBanter - Acquiring a single recordset from Access into Excel

Humsel,

While you can do this from the Data menu in Excel, you can also simplify the
process in code.

You'll need to change the path to the database file and the field name.

Sub CreateLink()

Sheets.Add befo=Sheets(1)
ActiveSheet.Name = "DB Output"

With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;" & _
"DBQ=C:\My Documents\Clients.mdb;" & _
"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
)), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT * FROM ClientList WHERE ClientName = 'Robin Hammond'")
.Name = "Client Data"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

Sub ChangeQuery()
Dim vClient As Variant

vClient = InputBox("Enter the client name")
If vClient = vbCancel Or vClient = "" Then Exit Sub

With Sheets("DB Output").QueryTables("Client Data")

.CommandText = Array("SELECT * FROM ClientList WHERE (ClientName='" & _
CStr(vClient) & "')")
.Refresh BackgroundQuery:=False

End With

End Sub

HTH,

Robin Hammond
www.enhanceddatasystems.com

"Humsel " wrote in message
...
Hello people,
I am going nuts here and so hope you can help me! I want an Excel sheet
to acquire Client data from an Access database for a Client name (key
attribute). The idea is to have one field in Excel where I can enter
the Client name and the other fields (status, address...) just fill out
automatically (and also refresh when something is changed in the Access
database). Is it possible to acquire a single recordset from Access in
Excel with MS query? Do I need VBA? I feel so stupid..

---
Message posted from http://www.ExcelForum.com/