ADODB and DAO access to SQL Server database 1

[PeDa]

My application has a Windows 7/Access 2010 front end to a SQL Server 2008 R2 back end. It generally uses ADODB to access the data, but sometimes (for example when using QueryDefs for Reports) it uses DAO. This works fine for me, but my colleagues have a problem: the ADODB parts work fine for them, but as soon as they try to open a DAO part they get an error message: "Run time error 3151: ODBC-connection to '{SQL Server Native Client 10.0}<servername>' failed".

Public pubQdf As QueryDef, pubServerNaam as String, pubDatabaseNaam as String, pubSqlString as String [defined module level]

Private priDAO_rs As Recordset

pubServerNaam= "MyServerName"
pubDatabaseNaam ="MyDatabaseName"
pubSQlString= "SELECT ...”

Set pubQdf = CurrentDb.QueryDefs("pubQueryDef")
With pubQdf
  .Connect = "ODBC;Driver={SQL Server Native Client 10.0};Server=" & pubServerNaam & ";Database=" & pubDatabaseNaam & "; Trusted_Connection=yes"
  .SQL = pubSqlString
End With

Set priDAO_rs = pubQdf.OpenRecordset(dbOpenDynaset) <=works for me; error message here for colleagues

Our IT department has not been able to come up with a solution. Any suggestions would be welcome.

 

[dhookom]

You should always be more explicit when declaring your objects. Try:

Public pubQdf As [b][COLOR=#EF2929]DAO.[/color][/b]QueryDef, pubServerNaam as String, pubDatabaseNaam as String, pubSqlString as String [defined module level]

Private priDAO_rs As [b][COLOR=#EF2929]DAO.[/color][/b]Recordset

pubServerNaam= "MyServerName"
pubDatabaseNaam ="MyDatabaseName"
pubSQlString= "SELECT ...”

Set pubQdf = CurrentDb.QueryDefs("pubQueryDef")
With pubQdf
  .Connect = "ODBC;Driver={SQL Server Native Client 10.0};Server=" & pubServerNaam & ";Database=" & pubDatabaseNaam & "; Trusted_Connection=yes"
  .SQL = pubSqlString
End With

Set priDAO_rs = pubQdf.OpenRecordset(dbOpenDynaset) <=works for me; error message here for colleagues

Duane
Hook'D on Access
MS Access MVP

 

[PeDa]

Thank you, but unfortunately this didn't help. I do wonder if this sort of aspect could explain why one user (me) has no problems, and all the others get an error message.

 

[dhookom]

I would try changing the driver to SQL Server

With pubQdf
  .Connect = "ODBC;Driver={SQL Server};Server=" & pubServerNaam & ";Database=" & pubDatabaseNaam & "; Trusted_Connection=yes"
  .SQL = pubSqlString
End With

Duane
Hook'D on Access
MS Access MVP

 

[PeDa]

>I would try changing the driver to SQL Server.

That is indeed the solution! (problem presumably caused by the fact that I have Microsoft SQL Server Management Suite on my local machine, and the others don't). If I used another machine with the original configuration, I got the same problem as my colleagues.

Many thanks,

Peter D.

 

[jeffwest21]

This is how I got round this. Works on XP and Windows & machines.

AttachDSNLessTable "DatabaseTableName", "SQLServer Table Name", "ServerName", "SQL Database Name"

'//Name     :   AttachDSNLessTable
'//Purpose  :   Create a linked table to SQL Server without using a DSN
'//Parameters
'//     stLocalTableName: Name of the table that you are creating in the current database
'//     stRemoteTableName: Name of the table that you are linking to on the SQL Server database
'//     stServer: Name of the SQL Server that you are linking to
'//     stDatabase: Name of the SQL Server database that you are linking to
'//     stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'//     stPassword: SQL Server user password
Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
    On Error GoTo AttachDSNLessTable_Err
    Dim td As TableDef
    Dim stConnect As String
    
    For Each td In CurrentDb.TableDefs
        If td.Name = stLocalTableName Then
            CurrentDb.TableDefs.Delete stLocalTableName
        End If
    Next
      
    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
    Else
        '//WARNING: This will save the username and the password with the linked table information.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
    End If
    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td
    AttachDSNLessTable = True
    Exit Function

AttachDSNLessTable_Err:
    
    AttachDSNLessTable = False
    MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description

End Function

'Clever boy...'