First I appreciate your input. I did however decide to create a userform in Excel which allows the user to select the Businees group he/she wants to view. I then wrote some code to import the data and update a graph automatically. This may not be the prettiest code but it works quite well.
Private Sub CommandButton1_Click()
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim fld As Field, n As Integer
Dim strpath As String, strSQL As String
Dim strconn As String, Item, i As Long, strbu As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("Data").Select
Cells.Clear
Range("A1").Select
strbu = frmmain.cmbareas.Value
n = 1
strpath = "\\xxxxxx\public\Safety\Audit Database\Audit_Database.mdb"
strconn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strpath & ";"
Sheets("Data").Select
strSQL = "SELECT * FROM QRY" & strbu
cnt.Open strconn
Set rst = cnt.Execute(strSQL)
For Each fld In rst.Fields
Cells(1, n) = fld.Name
n = n + 1
Next fld
Range("A2").CopyFromRecordset rst
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing
Range("1:1").HorizontalAlignment = xlCenter
Range("A:C").EntireColumn.AutoFit
Call Update_Graph 'Updates chart
Sheets("Chart").Select
Application.ScreenUpdating = True
End Sub
I did record a macro to import data like you suggested Skip but that had a lot of stuff I didn't need.