Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2009

    Unanswered: Where do I put this

    I am reading my Access with vba book and the topic is ADO and I am "learning"

    Dim cnnX As ADODB.Connection
    Set cnnX = CurrentProject.Connection
    Dim myRecordSet As New ADODB.Recordset
    myRecordSet.ActiveConnection = cnnX

    myRecordSet.Open SQLstatement

    ' cnnX = variable that defines the connection, you can leave it as 'cnnX(good '
    'as any other name)
    ' myRecordSet is the name you want to give your record set(usaly left as 'myRecordset)
    ' SQLstatement is a valid that is NOT an ACTION query

    But where do I put this. I tried under a button click event with a simple SELECT statement, but recived an error. Do I put the first part in a module an then call it with Any help is appreciated. Tim

  2. #2
    Join Date
    Aug 2003
    Bulgaria, Plovdiv


    Public Function GetLastLoginName() As String
    On Error GoTo GetLastLoginName_Err
    ' Get Login Name Attached to current Location

    GetLastLoginName = "NONAME"

    Dim conConnection As ADODB.Connection
    Dim rst As ADODB.Recordset

    Set conConnection = CurrentProject.Connection

    StrSQL = " SELECT LoginName FROM [Users]"
    StrSQL = StrSQL & " WHERE [LocationName] = '" & mLocation & "'"
    StrSQL = StrSQL & " AND [RememberMe] = " & 1

    Set rst = New ADODB.Recordset
    rst.Open StrSQL, conConnection, adOpenStatic, adLockReadOnly, adCmdText

    If Not rst.EOF Then

    GetLastLoginName = Nz(rst(0), "NONAME")

    End If


    Set rst = Nothing
    Set conConnection = Nothing
    Exit Function

    If Not rst Is Nothing Then
    If rst.State = adStateOpen Then rst.Close
    End If
    If Not conConnection Is Nothing Then
    If conConnection.State = adStateOpen Then conConnection.Close
    End If
    Call LogMsgError(Err.Number, Err.Description, "clsUser", "GetLastLoginName")
    Resume GetLastLoginName_Exit
    End Function

  3. #3
    Join Date
    Apr 2009
    Danisapfirov, Thank you for this it HELPED. I worked on this ADO stuff yesterday and with the assistance of your examples(the second example is still over my head, but I going to try and puzzel it out) I now have a fledgling working understanding of how to write a ADO statement. Thank you again for your examples. Tim

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts