ADODB Connection

[maurelius]

I am writing a macro for excel that will retrieve some data from an access database but am having some difficulty with the connection code.

What is supposed to happen is a connection is made when the worksbook is opened and sets a global variable which is used when the selection changes. The problem I get is that when I change selection on a

sheet I get an error here:
rs.Open selectQuery, cn, adOpenDynamic, , adCmdText
saying:
"The connection cannot be used to perform this operation. It is either closed or invalid in theis context"

looking at the global connection variable, it does appear to be active

This is the code I have so far:

Const databaseLocation As String = "C:\XP.mdb"
Public cn As ADODB.Connection

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'Clean up!!
cn.Close
Set cn = Nothing

End Sub

Private Sub Workbook_Open()

Dim conn As ADODB.Connection
Dim strConn As String

Set cn = New ADODB.Connection

Debug.Print "Start opening connection to database " & Format(Now, "hh:mm:ss")
'Create connection
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & databaseLocation & ";Persist Security Info=False;"
Set conn = New ADODB.Connection
conn.Open strConn
Debug.Print "Finished opening connection to database " & Format(Now, "hh:mm:ss")

cn = conn

End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Dim rs As ADODB.Recordset
Dim selectQuery As String
Dim selectedRow As Integer
Dim selectedColumn As Integer

'When a selection changes, update the row information only selected row is below header block
'and is in column 1!!
selectedRow = Target.Row
selectedColumn = Target.Column

If (Not (selectedRow >= 10) Or (Not (selectedColumn = 1))) Then
Exit Sub
End If

'Create recordset query
Debug.Print "Start opening recordset on database " & Format(Now, "hh:mm:ss")
selectQuery = "SELECT tblParts.PartNo, tblParts.Description, tblParts.`Spares Category` FROM tblParts WHERE ((tblParts.PartNo) = '51-85-6')"

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open selectQuery, cn, adOpenDynamic, , adCmdText
Debug.Print "Finish opening recordset on database " & Format(Now, "hh:mm:ss")

'Fill in the cell information from the returned data
If (rs.RecordCount > 0) Then
'There are records
ActiveSheet.Cells(selectedRow, 1) = rs.Fields("PartNo")
Else
'There are no records for the given part number
ActiveSheet.Cells(selectedRow, 3) = "Part Number not Recognised"
End If

'Clean up!!
Set rs = Nothing

End Sub


Does anyone have any ideas?

Thanks

 

[TCARPENTER1]

Hi maurelius,

Try this:

[b][purple]Set[/purple][/b] cn = conn

HTH
Todd

 

[maurelius]

Ah! of all the obvious mistakes to make!!

Thanks Todd