Tricky question

[Xipooo]

So I'm trying to do something a bit out of the norm. I've built a class to work with DAO and establish a connection to a SQL Server without using the local DB.



I create a database in memory and set with an OpenDatabase command that points directly to a SQL server. Then I have an "OpenRecordset" method on my class which returns a recordset.

When I set the combobox.recordset = db.OpenRecordset, data shows up in the combobox, but when a user selects something from the combobox it acts as if it just had a .requery run on it and whatever the user selected is now unselected.

If I use an ADO recordset this behavior does not happen.

Here is the DAO class I've built:

'---------------------------------------------------------------------------------------
' Module    : GTSDAO
' Author    : Steve Bishop
' Date      : 3/13/2014
' Purpose   : DAO connection that interacts with the SQL server directly
'---------------------------------------------------------------------------------------


Option Compare Database
Option Explicit


Public ODBC As String
Private p_Server As String
Private p_Database As String
Private p_Trusted As Boolean
Private p_UserID As String
Private p_Password As String
Public SQL As String
Private settings As New settings
Public db As DAO.database


Public Property Let Server(value As String)
    p_Server = value
    UpdateODBC
End Property
Public Property Let database(value As String)
    p_Database = value
    UpdateODBC
End Property
Public Property Let Trusted(value As Boolean)
    p_Trusted = value
    UpdateODBC
End Property
Public Property Let UserID(value As String)
    p_UserID = value
    UpdateODBC
End Property
Public Property Let Password(value As String)
    p_Password = value
    UpdateODBC
End Property


Public Property Get Server() As String
    Server = p_Server
End Property
Public Property Get database() As String
    database = p_Database
End Property
Public Property Get Trusted() As Boolean
    Trusted = p_Trusted
End Property
Public Property Get UserID() As String
    UserID = p_UserID
End Property
Public Property Get Password() As String
    Password = p_Password
End Property


'---------------------------------------------------------------------------------------
' Procedure : DLookup
' Author    : Steve Bishop
' Date      : 3/13/2014
' Purpose   : Lookup a value from a specific field on a table
'---------------------------------------------------------------------------------------
'
Public Function DLookup(Field As String, Table As String, Optional Criteria As String) As Variant
    Dim rs As Recordset
    Dim sSQL As String
    sSQL = "SELECT " & Field & " FROM " & Table & IIf(Criteria = "", "", " WHERE " & Criteria)
    Set rs = db.OpenRecordset(sSQL, 4, 64)
    If rs.EOF Then
        DLookup = Nothing
    Else
        rs.MoveFirst
        DLookup = rs.Fields(0).value
    End If
End Function
'---------------------------------------------------------------------------------------
' Procedure : DSum
' Author    : Steve Bishop
' Date      : 3/13/2014
' Purpose   : Perform a sum on a particular field of a table
'---------------------------------------------------------------------------------------
'
Public Function DSum(Field As String, Table As String, Optional Criteria As String) As Variant
    Dim rs As Recordset
    Dim sSQL As String
    sSQL = "SELECT sum(" & Field & ") as FieldSum FROM " & Table & IIf(Criteria = "", "", " WHERE " & Criteria)
    Set rs = db.OpenRecordset(sSQL, 4, 64)
    If rs.EOF Then
        DSum = Nothing
    Else
        rs.MoveFirst
        DSum = rs.Fields(0).value
    End If
End Function
'---------------------------------------------------------------------------------------
' Procedure : DCount
' Author    : Steve Bishop
' Date      : 3/13/2014
' Purpose   : Perform a count on a specific field in a table
'---------------------------------------------------------------------------------------
'
Public Function DCount(Field As String, Table As String, Optional Criteria As String) As Variant
    Dim rs As Recordset
    Dim sSQL As String
    sSQL = "SELECT count(" & Field & ") as FieldCount FROM " & Table & IIf(Criteria = "", "", " WHERE " & Criteria)
    Set rs = db.OpenRecordset(sSQL, 4, 64)
    If rs.EOF Then
        DCount = Nothing
    Else
        rs.MoveFirst
        DCount = rs.Fields(0).value
    End If
End Function


'---------------------------------------------------------------------------------------
' Procedure : OpenRecordset
' Author    : Steve Bishop
' Date      : 3/13/2014
' Purpose   : Opens a recordset for a query from the SQL server
'---------------------------------------------------------------------------------------
'
Public Function OpenRecordset(SQL As String) As Recordset
    Dim rs As Recordset
    Set rs = db.OpenRecordset(SQL, dbOpenDynaset, dbSeeChanges)
    Set OpenRecordset = rs
End Function


'---------------------------------------------------------------------------------------
' Procedure : Execute
' Author    : Steve Bishop
' Date      : 3/13/2014
' Purpose   : Execute a SQL command on the SQL server
'---------------------------------------------------------------------------------------
'
Public Function Execute(Optional SQL As String)
    If Nz(SQL, "") <> "" Then
        Me.SQL = SQL
    End If
    db.Execute Me.SQL, dbSQLPassThrough
End Function


'---------------------------------------------------------------------------------------
' Procedure : class_initialize
' Author    : Steve Bishop
' Date      : 3/13/2014
' Purpose   : Class constructor to set the default settings
'---------------------------------------------------------------------------------------
'
Private Sub class_initialize()
    p_Server = settings.ViewSetting("SQLServer")
    p_Database = settings.ViewSetting("SQLDatabase")
    p_Trusted = CBool(settings.ViewSetting("WinAuth"))
    p_UserID = settings.ViewSetting("SQLUserID")
    p_Password = settings.ViewSetting("SQLPassword")
    UpdateODBC
End Sub
Private Sub Class_Terminate()
    Set db = Nothing
End Sub


'---------------------------------------------------------------------------------------
' Procedure : UpdateODBC
' Author    : Steve Bishop
' Date      : 3/13/2014
' Purpose   : Sets the ODBC connection string and then connects to the database
'---------------------------------------------------------------------------------------
'
Private Sub UpdateODBC()
    If Me.Trusted Then
        ODBC = "ODBC;DRIVER=SQL Server;SERVER=" & Me.Server & ";DATABASE=" & Me.database & ";Trusted_Connection=Yes"
    Else
        ODBC = "ODBC;DRIVER=SQL Server;SERVER=" & Me.Server & ";DATABASE=" & Me.database & ";UID=" & Me.UserID & ";PWD=" & Me.Password
    End If
    Set db = OpenDatabase(Me.database, dbDriverComplete, True, Me.ODBC)
End Sub

I just perform a Set comboboxName.recordset = db.OpenRecordset(SQL) to bind my recordset to the combobox.

Is there something I'm missing with my recordset or is a combobox just never going to get along with a DAO database using an opendatabase function that points to SQL server?

[Xipooo]

Nevermind, fixed my own problem.

I realized my openrecordset wasn't configured correctly. Switched it to:

Public Function OpenRecordset(SQL As String) As Recordset
    Dim rs As Recordset
    Set rs = db.OpenRecordset(SQL, dbOpenSnapshot, dbReadOnly)
    Set OpenRecordset = rs
End Function

[orange]

Steve,

Does it work? Can you show us some scenario for getting some data from a SQL server database?
Just curious why you named your methods similar to the Access terms?
It may be helpful to readers if you told us more about why you did this? What are the benefits etc?

I am interested in classes, but have been retired for a while and only dabble now.

Good stuff, I always like "other" approaches. There's always more to learn.

[Xipooo]

Sure Orange.

I named the methods similar to Access because this class will be implemented across a good many of our products. I wanted to make the implementation as simple as possible for the other programmers to utilize. One of the BIGGEST complaints programmers have (as you know) is lack of consistency. By naming my methods, DCount, DSum, DLookup, OpenRecordset, and Execute.. I am ensuring the same practices my co-workers have for using the currentdb object matches with my DAO class.

The benefits of my DAO class is pretty much two fold.

1) I am no longer relying on the ACE engine to translate my queries into T-SQL and back. This reduces the resources required by MSAccess to perform queries. Essentially everything is a "Pass-Through" query.

2) It is much harder for someone to open the database and look at the tables. My back end is hidden. They cannot just open the .accdb file and view every table in it. They would have to know T-SQL language and/or have direct access to the SQL server.

Yes, the code works great! Essentially I've turned my front end into strictly being the UI similar to how an ADP project works. MS decided to do away with ADP's, one of the best features of Access, for some ungodly reason.

Classes are very important for anyone who wants to move into the .NET arena. Everything in .NET is strictly OOP and classes are the first step. Many of the benefits of OOP are available to us in Access but very few people utilize classes. Access was essentially designed to be the pinnacle of procedural coding in a Rapid Application Development environment and so classes seem a bit out of place. However, if they are used properly they can do a great many things to improve portability of an application, and production across all of your applications.

When you build a class, it is a self-contained mini code snippet that you can bring over to your other Access databases. This one class I built will work on any future databases I develop and no special adjustments will be required to make it work. I can just copy it in and start changing all of my "currentdb" references to an object that uses this class instead. So instead of:

dim db as database
set db = currentdb

I use:

dim db as new GTSDAO

and I use it in EXACTLY the same way I would use the currentdb object (with a few minor tweaks since I don't need some of the parameters that currentdb requires). My OpenRecordset, Execute, DSum, DLookup, and Dcount methods all work the same way as they do when you use the currentdb object.

There is one small caveat currently to my class in that the OpenRecordset is read-only right now. I could make it dynamic but then I run into some problems with setting it as the record source for my objects. That was the problem I had initially when I made this thread. This isn't really a big deal since I can just use INSERT and UPDATE queries with my Execute function to do any data changes. If I REALLY wanted to I could add a parameter to my OpenRecordset method where I pass in the dbOpenDynaset or dbReadOnly constant the same way currentdb has. But I'm starting to get a little off topic here.

Bottom line, you can use classes to manipulate the functionality of what is already available in Access. I could write the procedure of opening up my SQL server connection and passing along a command to it for every event in Access that would require it... but you can imagine how much unnecessary copying and pasting that would take. Since I would need to do it in multiple places simultaneously, a Module won't suffice.

I guess a simpler way of thinking about a class is like a table which also has methods on it (like stored procedures). You can hold data in it temporarily and then do some sort of action based on that data. In my case I am storing all of the information needed to make a connection to my SQL Server, then calling functions based on that connection.

Sorry for my long windedness. Congratulations to anyone who made it all the way through.

[orange]

Thanks Steve. All good stuff. I don't mind the "long" answer -- I can always learn.
I have built some classes over time, but Access was more of a hobby than work.
I haven't worked with SQL server, but do understand pass through queries. Did a lot with Oracle at one time.

In your courses/ videos do you have/plan anything on Classes?

[Xipooo]

Thanks Steve. All good stuff. I don't mind the "long" answer -- I can always learn.
I have built some classes over time, but Access was more of a hobby than work.
I haven't worked with SQL server, but do understand pass through queries. Did a lot with Oracle at one time.

In your courses/ videos do you have/plan anything on Classes?

Yes I definitely want to cover classes. Right now I'm trying to get the essentials out of the way first. The videos are my hobby. Access and .NET are my work.

I'm glad to help. I love helping others when I can.