Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2009
    Posts
    4

    Question Unanswered: Access+SQL Server: Current opinions

    Dear all,

    I wanted to ask you for your current opinions regarding MS Access frontend - SQL Server backend solutions.

    I was browsing through a couple of forums looking for the best solution when it comes to performance and sustainability. As it looks like, there are two groups of opinions,
    - one recommending .accdb/.mdb frontends with ODBC connections to MSSQL (since MS apparently kind of dismissed .adp/ADO in Access 2007/2010),
    - one recommending .adp frontends with ADO connections to MSSQL (since MS is apparently planning to reinforce .adp/ADO again in Access Version 15).

    Starting with a new application I'm wondering which way in terms of performance and (much more) in terms of sustainability I should use....

    Looking forward to your comments!

    Regards,
    ehansi

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I work for a local branch of a multinational company and I develop applications using MS-SQL Server backends/MS Access front end since 1997 (at that time it was the couple SQL 6.5/Access 97) plus some using MS-SQL/Visual Basic (beginning with SQL 6.5/VB 5.0).

    Before that it was either Access backend/frontend solutions (Access 2.0) or other solutions involving C/C++ connected to other database engines (in DOS, Windows, Unix and VMS environments). Though ADO was introduced with Access 97 (let's not bother with the clumsy and unusable Access 95), the standard interface was DAO using either Jet or ODBCDirect.

    With the issue of Access 2000 (in the year 2000) Microsoft flooded its customers with alarming messages warning that DAO was now obsolete and will be discontinued soon and that ADO was the path to the future as well as a far better solution. At that time ADO became the default declared reference in VBA (i.e. if in a procedure you wrote Dim rs As RecordSet, rs was implicitly an ADODB.RecordSet object). You had to manually include DAO in the references of an Access database, and were given advice not to do it.

    I began testing ADO by converting a rather straightforward stock management program from DAO to ADO. It worked rather well and the conversion process was not very hard but the performances of the ADO version were terrible. Moreover some applications already in production were simply impossible to convert, mainly because they worked with transactions among several databases: something you cannot do with ADO. After a six month period of evaluation and tests, it was decided to keep working with DAO as long as this interface is available.

    Now in 2011 SQL Server 6.5 has become a mix of SQL Server 2005 and SQL Server 2008 and existing applications are maintained in Access 2003 while new ones are developed in Access 2010. DAO is still the almost only used data interface, the only evolution being that ODBCDirect is not supported anymore. On the other hand, several applications were ported in VB/C# .NET and use the ADO.NET data interface that is native for this development environment.

    I don't know what will be packed in the future versions of Access but I'm very confident that DAO (then ODBC) will still be part of them.
    Have a nice day!

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    We are using Access as a frontend and SQL Server as the backend for all of our Access projects. We are using ADPs and MDBs. We just installed Access 2010 and have not gotten into accdbs yet. But based on my experience ADPs and MDBs can work equally well.

    I am not sure I would recommend using an ADP since I keep hearing that Microsoft is getting rid of them. So I supposed I would go the MDB route. There are a couple of things to keep in mind when you are using an MDB with SQL Server.

    1. Use connection strings to get at the SQL Server database data instead of using linked tables. If you try to get the data from the linked table using CurrentProject.Connection the query performance will be alot slower than opening a connection to the SQL Server database and then querying SQL Server directly. Keep in mind you need to use SQL Server SQL to get the data.

    2. With an MDB frontend you can add Access tables for frontend data storage. I do this quite a bit. I will store the date a user enters in a table and then include that in a local query for reporting. You can also create local queries that use linked tables from SQL Server. When reporting on linked tables, the query performance can be pretty good when using well organized tables.

    3. You will get the best performance by putting code in SQL Server versus in Access. Stored procedures will run faster than queries on linked tables. Keep in mind you can link to a SQL Server View as well. Which can be useful when doing reports.

    If you are not familiar with SQL Server it will be a learning curve. I am still learning. It also adds a little complication. Debugging gets trickier when SQL Server does not tell Access an error occured. But the backend management is much better. You won't have to worry as much about growing databases. The databases and the log files will grow, but you don't have to worry about hitting a 2GB limit.

  4. #4
    Join Date
    Jul 2011
    Posts
    63

    Connection string to MSSQL

    Quote Originally Posted by DCKunkle View Post
    1. Use connection strings to get at the SQL Server database data instead of using linked tables. If you try to get the data from the linked table using CurrentProject.Connection the query performance will be alot slower than opening a connection to the SQL Server database and then querying SQL Server directly. Keep in mind you need to use SQL Server SQL to get the data.
    Hi there, I'm new to Access and was wondering if you can describe how to use a connection string to get at the SQL Server database. I've tried using Google but can only find examples using DSN.

    Thank you!

  5. #5
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Here is the code I use to connect to different databases, for security I have changed the names and passwords:

    Code:
    Option Compare Database
    Option Explicit
    
    Public Enum appConnectionString
        appSQLServerBackend = 2
        appWebSQLServer = 3
        appIntranetSQLServer = 4
        appSQLServerDataPrep = 5
        appSQLServerFMB = 6
    End Enum
    
    Public Function GetConnectionString(lngConnection As appConnectionString) As String
    
        Dim strComputerName As String
        
        strComputerName = windowscomputername()
        
        Select Case lngConnection
        
            Case appSQLServerBackend
            
                GetConnectionString = "Provider=SQLOLEDB.1;Initial Catalog=DB1;Data Source = Server1;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Trusted_Connection=Yes"
                
            Case appWebSQLServer
            
                GetConnectionString = "Provider=SQLOLEDB.1;Initial Catalog=DB2;Data Source =something.com,4506;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;UID=User;PWD=Password"
             
             Case appIntranetSQLServer
            
                GetConnectionString = "Provider=SQLOLEDB.1;Initial Catalog=DB3;Data Source=Server3;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;UID=User;PWD=Password"
        
            Case appSQLServerDataPrep
            
                GetConnectionString = "Provider=SQLOLEDB.1;Initial Catalog=DB4;Data Source = " & strComputerName & "\SQLEXPRESS;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Trusted_Connection=Yes"
     
             Case appSQLServerFMB
            
                GetConnectionString = "Provider=SQLOLEDB.1;Initial Catalog=DB5;Data Source = Server5;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Trusted_Connection=Yes"
    
        End Select
                
    End Function

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you're used to work with linked tables and do not want to totally change the structure of your program, you can begin by replacing these linked table by queries (everything you do with a linked table can be done with a query too) that you can create "on the fly", just when you need them. From there, you can transfer SQL commands and logic from your database to the database on a server if you work with one (SQL Server for instance).

    Here's the code of a class that can do that:
    Code:
    '
    ' Class Module Cls_SQL_PassThrough
    ' --------------------------------
    
    Option Compare Database
    Option Explicit
    
    Private m_dbs As DAO.Database
    Private m_qdf As DAO.QueryDef
    Private m_strConnection As String
    Private m_strSQL As String
    Private m_strQueryName As String
    
    Private Sub Class_Initialize()
    
        Set m_dbs = CurrentDb
        Set m_qdf = m_dbs.CreateQueryDef
    
    End Sub
    
    Private Sub Class_Terminate()
    
        Set m_qdf = Nothing
        Set m_dbs = Nothing
    
    End Sub
    
    Public Property Get Connection() As String
    
        Connection = m_strConnection
    
    End Property
    
    Public Property Let Connection(ByVal ConnectionString As String)
    
        m_strConnection = ConnectionString
    
    End Property
    
    Public Function Execute(Optional ByVal ConnectionString As Variant, _
                            Optional ByVal SQLString As Variant, _
                            Optional ByVal QueryName As Variant) As Long
    
    
        On Error GoTo Err_Execute
    
        If Not IsMissing(ConnectionString) Then m_strConnection = ConnectionString
        If Not IsMissing(SQLString) Then m_strSQL = SQLString
        If Not IsMissing(QueryName) Then m_strQueryName = QueryName
        
        If Len(m_strConnection) > 0 And Len(m_strSQL) > 0 Then
            With m_qdf
                .Name = m_strQueryName
                .Connect = m_strConnection
                .SQL = m_strSQL
                .ReturnsRecords = (Len(m_strQueryName) > 0)
                If .ReturnsRecords = False Then
                    .Execute
                Else
                    If DCount("*", "MsysObjects", "Name='" & m_strQueryName & "' And Type=5") <> 0 Then m_dbs.QueryDefs.Delete m_strQueryName
                    m_dbs.QueryDefs.Append m_qdf
                End If
                .Close
            End With
            Execute = True
        End If
    
    Exit_Execute:
        On Error GoTo 0
        Exit Function
    
    Err_Execute:
        Execute = Err.Number
        Err.Clear
        Resume Exit_Execute
        
    End Function
    
    Public Property Get QueryName() As String
    
        QueryName = m_strQueryName
    
    End Property
    
    Public Property Let QueryName(ByVal QueryName As String)
    
        m_strQueryName = QueryName
    
    End Property
    
    Public Property Get SQL() As String
    
        SQL = m_strSQL
    
    End Property
    
    Public Property Let SQL(ByVal strSQL As String)
    
        m_strSQL = strSQL
    
    End Property
    You can test the class with:
    Code:
    Sub Test_Cls_SQL_PassThrough_Query()
    '
    ' This creates a permanent query into the current database.
    '
        Dim cls As Cls_SQL_PassThrough
        
        Set cls = New Cls_SQL_PassThrough
        With cls
            .QueryName = "Qry_Procedure_Names"
            .Connection = "ODBC;DRIVER={SQL Server};SERVER=SOLIMAN;DATABASE=Scoreboard;TRUSTED_CONNECTION=Yes;"
            .SQL = "SELECT * FROM Tbl_Procedure_Names WHERE Inactive = 0;"
            .Execute
        End With
        Set cls = Nothing
        
    End Sub
    
    Sub Test_Cls_SQL_PassThrough_Command()
    '
    ' This executes a SQL command into the remote database.
    '
        Dim cls As Cls_SQL_PassThrough
        
        Set cls = New Cls_SQL_PassThrough
        With cls
            .QueryName = ""
            .Connection = "ODBC;DRIVER={SQL Server};SERVER=SOLIMAN;DATABASE=Scoreboard;TRUSTED_CONNECTION=Yes;"
            .SQL = "DELETE FROM Tbl_Procedure_Names WHERE Inactive = 1;"
            .Execute
        End With
        Set cls = Nothing
        
    End Sub
    Have a nice day!

  7. #7
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    One of the best ways, in my opinion, to get connections strings is to link to a table, then use Design View to view the linked table. If you right click you will get to Properties. The Description property will show you the connection string Access used to connect to the table.

Tags for this Thread

Posting Permissions

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