Working with Access & Excel

[Guest]

I have an Access project in which I open an Excel workbook. With the workbook
there is code that runs a query and gets information from the db. Everything
rns fine until the user closes the workbook and then the database object
locks up and I must Ctrl-Alt-Delete and close th db to get up and running
again. Below is the primary code from Access and Excel. Any help would be
appreciated. Do I need to close a connection somewhere? Thank you.

Accessrivate Sub cmdCallSheet_Click()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Set xlApp = New Excel.Application
With xlApp
.Visible = True
Set xlWB = .Workbooks.Open("s:\Education\Students.xls", , False)

End With

End Sub

The Excel Code:

Public Sub CreateQueryTables(strDay)

'Create variable to hold path to current directory for strCnn
Dim strPath As String
strPath = Application.ThisWorkbook.Path

'Create variable to hold the quarter for SQL statements below
'gets from Sheet2 cell A1 (note: worksheet set visible = false
Dim strQtr As String
strQtr = Worksheets("Sheet2").Range("A1").Value

Dim strCnn As String, strCmdTxt As String
' Set up connection string.
strCnn = "ODBC;DBQ=" & strPath & "\EducationPro-New.mdb;" & _
"Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS
Access;PageTimeout=15"

strCmdTxt = Empty

Select Case strDay
Case "M"
strCmdTxt = "SELECT DISTINCT [Unit] & [Floor] & [Tier] & [Room] &
[Bed] AS House, tblStudentHistory.DOCNumber " & _
"AS [DOC#], tblStudentHistory.Time, [LastName] & ', ' & [FirstName]
AS NAME, 'MSC Education' AS DESTINATION, " & _
"tblStudentHistory.RM, tblStudentHistory.Instruct1,
tblStudentHistory.Instruct2, tblStudentHistory.Instruct3 " & _
"FROM tblStudentHistory INNER JOIN tblStudents ON
tblStudentHistory.DOCNumber = tblStudents.DOCNumber " & _
"WHERE (((tblStudentHistory.Quarter) = '" & strQtr & "') And
((tblStudentHistory.Days) = 'Daily')) " & _
"Or (((tblStudentHistory.Quarter) = '" & strQtr & "') And
((tblStudentHistory.Days) = 'MW')) ORDER BY " & _
"tblStudentHistory.Time, [LastName] & ', ' & [FirstName];"

End Select

' Create the QueryTable on the ActiveSheet at the range stated.
With ActiveSheet.QueryTables.Add(Connection:=strCnn,
Destination:=Range("A3"))
If .QueryType = xlOLEDBQuery Then .CommandType = xlCmdDefault
.CommandText = strCmdTxt
.RefreshStyle = xlOverwriteCells
.HasAutoFormat = False
.RefreshOnFileOpen = False
.Refresh BackgroundQuery:=False
End With

End Sub












Private Sub workbook_deactivate()
On Error Resume Next
With Application
.CommandBars("CallTBar").Delete
End With

On Error GoTo 0

End Sub

 

[Ken Snell \(MVP\)]

You need to close your references to EXCEL in the ACCESS code by setting
those objects to Nothing (note that this disconnects ACCESS from the EXCEL
application):


Private Sub cmdCallSheet_Click()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Set xlApp = New Excel.Application
With xlApp
.Visible = True
Set xlWB = .Workbooks.Open("s:\Education\Students.xls", , False)

End With

Set xlWB = Nothing
Set xlApp = Nothing

End Sub

--

Ken Snell
<MS ACCESS MVP>

I have an Access project in which I open an Excel workbook. With the
workbook
there is code that runs a query and gets information from the db.
Everything
rns fine until the user closes the workbook and then the database object
locks up and I must Ctrl-Alt-Delete and close th db to get up and running
again. Below is the primary code from Access and Excel. Any help would be
appreciated. Do I need to close a connection somewhere? Thank you.

Accessrivate Sub cmdCallSheet_Click()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Set xlApp = New Excel.Application
With xlApp
.Visible = True
Set xlWB = .Workbooks.Open("s:\Education\Students.xls", , False)

End With

End Sub

The Excel Code:

Public Sub CreateQueryTables(strDay)

'Create variable to hold path to current directory for strCnn
Dim strPath As String
strPath = Application.ThisWorkbook.Path

'Create variable to hold the quarter for SQL statements below
'gets from Sheet2 cell A1 (note: worksheet set visible = false
Dim strQtr As String
strQtr = Worksheets("Sheet2").Range("A1").Value

Dim strCnn As String, strCmdTxt As String
' Set up connection string.
strCnn = "ODBC;DBQ=" & strPath & "\EducationPro-New.mdb;" & _
"Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS
Access;PageTimeout=15"

strCmdTxt = Empty

Select Case strDay
Case "M"
strCmdTxt = "SELECT DISTINCT [Unit] & [Floor] & [Tier] & [Room] &
[Bed] AS House, tblStudentHistory.DOCNumber " & _
"AS [DOC#], tblStudentHistory.Time, [LastName] & ', ' & [FirstName]
AS NAME, 'MSC Education' AS DESTINATION, " & _
"tblStudentHistory.RM, tblStudentHistory.Instruct1,
tblStudentHistory.Instruct2, tblStudentHistory.Instruct3 " & _
"FROM tblStudentHistory INNER JOIN tblStudents ON
tblStudentHistory.DOCNumber = tblStudents.DOCNumber " & _
"WHERE (((tblStudentHistory.Quarter) = '" & strQtr & "') And
((tblStudentHistory.Days) = 'Daily')) " & _
"Or (((tblStudentHistory.Quarter) = '" & strQtr & "') And
((tblStudentHistory.Days) = 'MW')) ORDER BY " & _
"tblStudentHistory.Time, [LastName] & ', ' & [FirstName];"

End Select

' Create the QueryTable on the ActiveSheet at the range stated.
With ActiveSheet.QueryTables.Add(Connection:=strCnn,
Destination:=Range("A3"))
If .QueryType = xlOLEDBQuery Then .CommandType = xlCmdDefault
.CommandText = strCmdTxt
.RefreshStyle = xlOverwriteCells
.HasAutoFormat = False
.RefreshOnFileOpen = False
.Refresh BackgroundQuery:=False
End With

End Sub












Private Sub workbook_deactivate()
On Error Resume Next
With Application
.CommandBars("CallTBar").Delete
End With

On Error GoTo 0

End Sub