×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

ADODB Connection

ADODB Connection

ADODB Connection

(OP)
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

RE: ADODB Connection

Hi maurelius,

Try this:

CODE

Set cn = conn

HTH
Todd

RE: ADODB Connection

(OP)
Ah! of all the obvious mistakes to make!!

Thanks Todd :)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close