[AccessD] Connection Strings for SQL2012R2

[David Emerson]

Tracked the problem - it was permissions on the folder and database that
caused the read only error.

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
David Emerson
Sent: Monday, 23 April 2018 4:18 p.m.
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Connection Strings for SQL2012R2

Hi Listers,

I have an Access accdb which connects to an SQL database.  Currently I
connect to an sql database in SQL2008 which runs fine.  The connection
strings are:

Set gADODBConnection = New ADODB.Connection
gADODBConnection.ConnectionString = "Provider=sqloledb;Data
Source=SWLGWCLUT12;Initial Catalog=StockData5;Integrated Security=SSPI;"


Dim strTempQueryName As String, qdefTemp As DAO.QueryDef
strTempQueryName = "qryTempQuery1"
Set qdefTemp = CurrentDb.CreateQueryDef(strTempQueryName)
qdefTemp.ReturnsRecords = True
qdefTemp.ODBCTimeout = 300
qdefTemp.Connect = "ODBC;DRIVER=SQL
Server;SERVER=SWLGWCLUT12;DATABASE=StockData5;Trusted_Connection=Yes"

I am trying to set up the database on an RDS server (being 2012 R2) but
according to the IT people the problem is that we cannot install the x86 SQL
driver that access needs as this seems to be unsupported.

We have tried bypassing this initial issue by creating a DSN using the "ODBC
Driver 11 for SQL Server" and using this code for the global connection:

gADODBConnection.ConnectionString =
"DSN=StocksData5;Trusted_Connection=yes;"

This seems to work but when we try to attach tables we get an error message:

Error 3027: Cannot Update. Database or object is read-only.

Public Function AttachDSNLessTable(stLocalTableName As String,
stRemoteTableName As String, stServer As String, stDatabase As String,
Optional stUsername As String, Optional stPassword As String, Optional
stKeyfield As String)
'//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
'//     stKeyfield: Name of field if stRemoteTableName is a view. This is
used to set up an index so data can be edited
    
    On Error GoTo Err_AttachDSNLessTable

    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
    
    If Len(stKeyfield) > 0 Then
        CurrentDb.Execute "CREATE UNIQUE INDEX 'PrimaryKey' ON " &
stLocalTableName & " (" & stKeyfield & ") WITH PRIMARY"
    End If

Does anyone know what connection string I can use?

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand




-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com