read access file from within excel

stokak

New Member
Joined
May 20, 2007
Messages
14
need to open/import an access file that has been selected by the user, and import only the date range selected...want to do this using vb...the rest of the process to get the info is in vb....
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
All doable, but will this be from a table or query?
Where will the user select the date range? InputBox or cells on a worksheet?
Will the date range change regularly?

Denis
 

stokak

New Member
Joined
May 20, 2007
Messages
14
it will be from a table.
i wanted the user to be in excel and enter date ranges in named cells.
the tabel to access will be from a drop down attached to an excel cell
the dropdown will refer to some cells that translate the file name to the actual entire path to the file to be found in access. then i want the code to go to the access file and select only those rows that are within the date range.
thanks.
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Normally I do the following:

1. Define range names on the worksheet for any parameters that you want to use in the query
2. Create a query in Access that has all of the fields that I need, but not the filters.
3. In code, build the required SQL to use the values provided by the user
4. Pass the final SQL to Access and download the data.

Here is some code adapted from a routine I used a couple of years back:
Code:
Sub GetMainSummary()
  Dim WSOrig As Worksheet
  Dim WSTemp As Worksheet
  Dim FinalRow As Long
  Dim sType As String
  Dim sBU As String
  Dim sRegion As String
  Dim shDest As Worksheet
  Dim sDataSource As String
  Dim MyConn
  Dim dtFirst As Double
  Dim dtLast As Double
  Dim i As Integer
  Dim LastRow As Long, lOffset As Long
  Dim fld As Field
  Dim Rws As Long
  
  fPATH = "the full path to the .mdb file"
  sFileID = ActiveWorkbook.FullName
  dtFirst = CDbl(Range("FirstDate").Value)
  dtLast = CDbl(Range("LastDate").Value)
  
  shDest = "Main"
  sDataSource = "qryDataToImport" 'import data by month
  
  Set WSOrig = Sheets("Setup")
    sBU = Range("State").Value
    sVersion = Range("Version").Value
  
    sSQL = "SELECT * FROM " & sDataSource
    
'original Business Unit filter
    If sBU = "" Then
      sSQL = sSQL
    Else
     If InStr(1, sSQL, "WHERE") > 1 Then
      sSQL = sSQL & " AND BusinessUnit=" & "'" & sBU & "'"
     Else
      sSQL = sSQL & " WHERE BusinessUnit=" & "'" & sBU & "'"
     End If
    End If
        
'filter by date: uses two defined ranges in the workbook
    If i = 1 Then 'main data
        If InStr(1, sSQL, "WHERE") > 1 Then
         sSQL = sSQL & " AND Fcst_Date BETWEEN " & dtFirst & " AND " & dtLast
        Else
         sSQL = sSQL & " WHERE Fcst_Date BETWEEN " & dtFirst & " AND " & dtLast
        End If
    End If
        
    Debug.Print sSQL
    MyConn = fPATH
    
    Set cnn = New ADODB.Connection
    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
    
    Application.ScreenUpdating = False
    
    Set WSTemp = Worksheets(varSources(i, 1))
    WSTemp.Activate
       
    Range("A1").CurrentRegion.Offset(1, 0).Clear
    
    With Range("A1") 'create field headers
    lOffset = 0
        For Each fld In rst.Fields
            .Offset(0, lOffset).Value = fld.Name
            lOffset = lOffset + 1
        Next fld
    End With
     
     Range("A2").CopyFromRecordset rst
   
    rst.Close
  
  Sheets("Setup").Activate
  Application.ScreenUpdating = True
  
End Sub

Denis
 

Forum statistics

Threads
1,181,648
Messages
5,931,212
Members
436,784
Latest member
amuljono

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top