Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Acquiring a single recordset from Access into Excel
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Acquiring a single recordset from Access into Excel
Thanks a lot!!! I have already done it with parameters in the meantime
but this is a lot easier! -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find the oldest date in an Access recordset | Excel Discussion (Misc queries) | |||
Linking Excel and Access to import a single record from DB | Excel Worksheet Functions | |||
Obtaining a single Access record from Excel input | Links and Linking in Excel | |||
Copy recordset from an Access "make table" query | Excel Programming | |||
Access Recordset with Built-In Function | Excel Programming |