Solved

Read-only problem with SQL Server back end in Access

Posted on 2011-03-04
11
655 Views
Last Modified: 2015-08-18
I'm using this piece of code to attach my Access front end database to an SQL Server back end.
 
For Each td In CurrentDb.TableDefs
 If td.name = stLocalTableName Then
 CurrentDb.TableDefs.Delete stLocalTableName
 End If
 Next

 stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
 Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
 CurrentDb.TableDefs.Append td
 AttachDSNLessTable = True

Open in new window

If I use this code, the SQL Server database becomes read-only to the Access front end. If I set up the links using ODBC the front end becomes read / write, which is what I want.

I've looked through here for various solutions, but the best I can find is where people have used the upsize wizard and it didn't carry the primary key forward. That is not the case here, the primary keys have been carried through to SQL Server.

If I go into native SQL Server it is not read-only.

Any ideas?
0
Comment
Question by:rick_danger
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 84
ID: 35035241
After creating your Links in this manner, review the Indexes of the Linked filed to see if Access added one named "_Unique index" that specifies the correct field for the Primary Key or Unique Index. If it does not, you'll have to add that to your linked table.

Note you can add this via SQL by running a statement like this:

CREATE INDEX __UniqueIndex ON [AcctsRecExpensesSUBQRY] ([FileNumber])
0
 

Author Comment

by:rick_danger
ID: 35035279
LSM
Thanks
Where would I look to see this and which field would it apply to?
On CREATE INDEX, what would FileNumber be set to?

But if ODBC link works, would this apply?
0
 
LVL 7

Expert Comment

by:printnix63
ID: 35035313
When you run this code, is it run under your user, or a different one?
As you use trusted connection it should use windows authentication, whence the question.
Otherwise you could try, if the sql server runs in mixed mode, to use a database user when connecting...
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 35036382
Open the linked table in "design view" (Access will complain, but open it anyway).

On the Table Tools - Design tab, click the Indexes item. This will show you the indexes Access is using for that table. If there is NOT one that is marked as a Primary Index you'll have to add one.
0
 

Expert Comment

by:faquue
ID: 40933429
I run the same code and the solution doesn't work at ALL!!!
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:rick_danger
ID: 40933439
This was 4.5 years ago - you need to create a new question, I think...
0
 

Expert Comment

by:faquue
ID: 40933688
I don't care how  old the question is that  answer would have never worked, the following does.

Public Function SetLinkedTables()
    Dim db As Database
    Dim Cnct As String
    Dim tdf As TableDef
    Dim sCon As Variant
    Dim a As Integer
    Dim sVar As Variant
    Dim sName As String
   
    On Error GoTo SLT_Err

    Set db = CurrentDb

    db.TableDefs.Refresh
 
    ' Loop Table Defs
    For Each tdf In db.TableDefs
        With tdf
            'split current string to find database name
            sCon = Split(.Connect, ";")
            For a = 0 To UBound(sCon)
                If sCon(a) <> "" Then
                    sVar = Split(sCon(a), "=")
                    If sVar(0) = "DATABASE" Then
                        Cnct = sVar(1)
                        'Fix table name issue
                        If Left(.Name, 4) = "dbo_" Then
                            sName = Right(.Name, Len(.Name) - 4)
                        Else
                            sName = .Name
                        End If
                        ' create connection to table
                        If Left(.Name, 1) <> "~" Then
                              'Call AttachDSNLessTable(.Name, sName, "servername", Cnct, "userid", "password")
                        End If
                    End If
                End If
            Next
        End With
    Next
                       
    db.TableDefs.Refresh

    Set tdf = Nothing
    Set db = Nothing

SLT_Exit:
   Exit Function

SLT_Err:
   MsgBox "Error in SetLinkedTables : " & Err.Description
   Resume SLT_Exit

End Function

Public 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
    Dim ind As DAO.Index

    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)
   
    DoCmd.TransferDatabase acLink, "ODBC Database", stConnect, acTable, stRemoteTableName, stLocalTableName
    AttachDSNLessTable = True

    Exit Function
End Function
0
 

Author Comment

by:rick_danger
ID: 40933700
OK, get abusive. I have nothing to say to you.
0
 

Expert Comment

by:faquue
ID: 40934700
forgot to uncomment the following.

Call AttachDSNLessTable(.Name, sName, "servername", Cnct, "userid", "password")
0
 
LVL 84
ID: 40934740
The question was not about how to create linked tables, it was about why the tables were read-only. The author's original method to link tables works fine, as does yours - which is should, since you copied it from another site:

http://www.tek-tips.com/viewthread.cfm?qid=1644001

and

https://support.microsoft.com/en-us/kb/892490
0
 

Author Comment

by:rick_danger
ID: 40934771
Scott - nice one!

Thanks...
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now