[MylesAR]

Hi,

I have been struggling with a problem for a while and am hoping someone can help me. I am working with ADO and also Access objects to transfer data between Excel and Access. I am experienced in Excel but newish to Access and very new to ADO etc.

Long story short when I open a connection I can't seem to close it.

Below is an example of the code (with queries taken out to simplify):

Sub TESTCONNECTION()
Dim Cnct As String
Dim Connection As ADODB.Connection
Dim dbPath As String
Dim DB_Name As String
Dim DBFullName As String
Dim DBTblName As String
Dim DBTblPath As String


DB_Name = Range("DBase_Name").Text

' Database information
If Range("config_DB_InDir").Value = True Then
DBFullName = ThisWorkbook.Path & "\" & DB_Name & ".mdb"
Else
DBFullName = Range("Database_Path").Text

End If

'Set database name here

Cnct = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBFullName & ";"
'Open connection
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct
OpenCurrentDatabase (DBFullName)


Connection.Close
CloseCurrentDatabase
Close
MsgBox "Complete"
End Sub

I've tried a range of close methods but the db seems to remain locked open.

NB I am using DoCmd.OpenQuery and also ADODB.Recordset type queries.

Any help would be great.