Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2013
    Posts
    23

    Post Unanswered: DAO to ADODB Conversion

    I currently have an access database that is being converted to strictly a Front-End and SQL as the back-end. Can someone please get me started with this simple code conversion from DAO to ADODB? I would be very grateful for the infinite wisdom that resides within these boards.

    Option Compare Database
    Public LngLoginId As Long

    Function LogMeIn(sUser As Long)
    '/Go to the users table and record that the user has logged in
    '/and which computer they have logged in from

    Dim Rs As DAO.Recordset

    Set Rs = CurrentDb.OpenRecordset("SELECT * FROM [Tbl_Users] WHERE PKUserID =" & sUser)
    If Not Rs.EOF Then
    Rs.Edit
    Rs.Fields("fldLoggedIn").Value = True
    Rs.Fields("fldComputer").Value = StrComputerName
    Rs.Update
    End If
    Rs.Close
    Set Rs = Nothing

    End Function

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you are going to use Access strictly as a front-end, then I would recommend that you create and Access Data Project (.adp file), point it at the sql server database, and not mess with linked tables and OpenRecordset.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Oct 2013
    Posts
    23

    Access 2013 is dropping support of ADP

    I heard that Access 2013 will be dropping support of ADP and thus would make my project obsolete. Is that a correct notion? If so, I still need help with getting started on Recordset. Thanks for your time!

  4. #4
    Join Date
    Oct 2013
    Posts
    23

    Does this conversion look right?

    Function LogMeIn(sUser As Long)
    '/Go to the users table and record that the user has logged in
    '/and which computer they have logged in from


    Dim con As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim Rs As ADODB.Recordset


    '/Use the ADO connection that Access uses
    Set con = CurrentProject.AccessConnection

    '/Create an instance of the ADO Recordset class, and set its properties
    Set Rs = New ADODB.Recordset
    With Rs
    Set .ActiveConnection = con
    .Source = "SELECT * FROM [Tbl_Users] WHERE PKUserID =" & sUser
    If Not Rs.EOF Then
    Rs.Edit
    Rs.Fields("fldLoggedIn").Value = True
    Rs.Fields("FldComputer").Value = StrComputerName
    Rs.Update
    End If


    'close connections
    con.Close
    Set Rs = Nothing
    Set cmd = Nothing
    Set con = Nothing


    End Sub

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Moving to the Access topic
    George
    Home | Blog

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Why would you want to convert to ADODB? You can easily use DAO in an Access front-end connected to a SQL Server.
    Have a nice day!

  7. #7
    Join Date
    Oct 2013
    Posts
    23

    I have to get this, it is killing me, boggin me down

    According to my DBAteam that I intermittangly get support from, I have to use ADODB. I am to new to this to even think about how to stay DAO. All I know is the password/login works great when the tables are still in Access, but now that I am utilizing the SQL Server, I get errors left and right. Now I have changed the code to this below, but it is tripping up on the WHERE clause, long-story short is that I am inserting data in the Users Table to record that they are logged in. So, I have to find the USERNAME and insert:

    Function LogMeIn()
    '/Go to the users table and record that the user has logged in
    '/and which computer they have logged in from

    Dim con As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim strSQL As String
    Dim sUser As Long

    Dim StrComputerName As String, strLoggedIn As String

    'passing variables
    strLoggedIn = "True"
    StrComputerName = fldComputer


    'Declaring what table you are passing the variables to
    strSQL = "Insert into dbo.tTbl_LoginUsers (fldLoggedIn, fldComputer) Values ('" & strLoggedIn & "','" & StrComputerName & "')"
    WHERE PKUserID = " & sUser;"

    'connect to SQL Server
    Set con = New ADODB.Connection
    With con
    .ConnectionString = cSQLConn
    .Open
    End With

    'write back
    Set cmd = New ADODB.Command
    With cmd
    .ActiveConnection = con
    .CommandText = strSQL
    .CommandType = adCmdText
    .Execute
    End With

    'close connections
    con.Close
    Set cmd = Nothing
    Set con = Nothing

    End Function

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you print out the value of the variable strSQL as the syntax doesn't look right to me.
    George
    Home | Blog

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Nothing in there that forces you to use ADODB:
    Code:
    Function LogMeIn()
    
    '/Go to the users table and record that the user has logged in
    '/and which computer they have logged in from
    
        Const cSQLConn As String = "ODBC;DRIVER={SQL Server};SERVER=ServerName;DATABASE=DatabaseName;Trusted_Connection=Yes;"
        
        Dim qdf As DAO.QueryDef
        Dim sUser As Long
        Dim StrComputerName As String
        Dim strLoggedIn As String
        Dim strSQL As String
        
        'passing variables
        '
        strLoggedIn = "True"
        StrComputerName = fldComputer
        
        
        'Declaring the SQL expression to be executed by the server.
        '
        strSQL = "Insert into dbo.tTbl_LoginUsers (fldLoggedIn, fldComputer) Values ('" & strLoggedIn & "','" & StrComputerName & "')" & _
                 "WHERE PKUserID = " & sUser & ";"
        
        'Create the querydef, connect it and execute the SQL expression.
        '
        Set qdf = CurrentDb.CreateQueryDef("")
        With qdf
            .Connect = cSQLConn
            .SQL = strSQL
            .Execute
        End With
        
        ' Clean up.
        '
        Set qdf = Nothing
    
    End Function
    Have a nice day!

  10. #10
    Join Date
    Oct 2013
    Posts
    23
    Quote Originally Posted by gvee View Post
    Can you print out the value of the variable strSQL as the syntax doesn't look right to me.
    Used SQL to look at Syntax and figured it out, thanks for the prompt!

Posting Permissions

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