read access file from within excel

[stokak]

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....

 

[SydneyGeek]

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]

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.

 

[Derek Brown]

Your question is similar to the following:
[url]http://www.mrexcel.com/board2/viewtopic.php?t=283064&highlight=
[/url]
and you may find my reply to that useful.

 

[SydneyGeek]

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:

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