This code leaves Access open (can see it in task manager).
Is there a better way to close it? Am I leaving out a statement?

Thanks, Mark

Private Sub CommandButton3_Click()
'engineering projects import
Worksheets("PROJ").Range("A1:L50000").Clear
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object
Dim recArray As Variant
Dim strDB As String
Dim fldCount As Integer
Dim recCount As Long
Dim iCol As Integer
Dim iRow As Integer
strDB = Worksheets("input").Range("B22")
Set xlApp = Application
Set ap = CreateObject("Access.Application")
ap.OpenCurrentDatabase (strDB)
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDB & ";"
xlApp.Sheets("PROJ").Select
rst.Open "Select * From [Seller Review]", cnt
Set xlWb = ActiveWorkbook
Set xlWs = xlWb.Worksheets("PROJ")
fldCount = rst.Fields.Count
For iCol = 1 To fldCount
xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
Next
xlWs.Cells(2, 1).CopyFromRecordset rst
rst.Close
cnt.Close
Set ap = Nothing
Set rst = Nothing
Set cnt = Nothing
Set xlWs = Nothing
Set xlWb = Nothing
Set xlApp = Nothing

Sheets("INPUT").Select
End Sub

did you try

ap.quit

--
Regards,
Tom Ogilvy

"Mark" wrote in message
m...
This code leaves Access open (can see it in task manager).
Is there a better way to close it? Am I leaving out a statement?

Thanks, Mark

Private Sub CommandButton3_Click()
'engineering projects import
Worksheets("PROJ").Range("A1:L50000").Clear
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object
Dim recArray As Variant
Dim strDB As String
Dim fldCount As Integer
Dim recCount As Long
Dim iCol As Integer
Dim iRow As Integer
strDB = Worksheets("input").Range("B22")
Set xlApp = Application
Set ap = CreateObject("Access.Application")
ap.OpenCurrentDatabase (strDB)
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDB & ";"
xlApp.Sheets("PROJ").Select
rst.Open "Select * From [Seller Review]", cnt
Set xlWb = ActiveWorkbook
Set xlWs = xlWb.Worksheets("PROJ")
fldCount = rst.Fields.Count
For iCol = 1 To fldCount
xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
Next
xlWs.Cells(2, 1).CopyFromRecordset rst
rst.Close
cnt.Close
Set ap = Nothing
Set rst = Nothing
Set cnt = Nothing
Set xlWs = Nothing
Set xlWb = Nothing
Set xlApp = Nothing

Sheets("INPUT").Select
End Sub

Tom,
Thanks for the suggestion.

I added ap.quit before the ap = nothing statement, however it looks like
the database is still open after running the procedure (still see Access
in my task list processes).

Mark

Mark Roach
Vice President Technical Resources
The Oil & Gas Asset Clearinghouse
Houston, Texas

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi Mark,

Is there a reason that you are explicitly opening the Access application? I
haven't run into situations where that was necessary to retrieve data. You
should be able to take the CreateObject and OpenCurrentDatabase lines out of
there (as well as the references to the Excel Application object, as you
don't need it from within Excel).

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Mark wrote:
This code leaves Access open (can see it in task manager).
Is there a better way to close it? Am I leaving out a statement?

Thanks, Mark

Private Sub CommandButton3_Click()
'engineering projects import
Worksheets("PROJ").Range("A1:L50000").Clear
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object
Dim recArray As Variant
Dim strDB As String
Dim fldCount As Integer
Dim recCount As Long
Dim iCol As Integer
Dim iRow As Integer
strDB = Worksheets("input").Range("B22")
Set xlApp = Application
Set ap = CreateObject("Access.Application")
ap.OpenCurrentDatabase (strDB)
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDB & ";"
xlApp.Sheets("PROJ").Select
rst.Open "Select * From [Seller Review]", cnt
Set xlWb = ActiveWorkbook
Set xlWs = xlWb.Worksheets("PROJ")
fldCount = rst.Fields.Count
For iCol = 1 To fldCount
xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
Next
xlWs.Cells(2, 1).CopyFromRecordset rst
rst.Close
cnt.Close
Set ap = Nothing
Set rst = Nothing
Set cnt = Nothing
Set xlWs = Nothing
Set xlWb = Nothing
Set xlApp = Nothing

Sheets("INPUT").Select
End Sub