Solved

Read-only problem with SQL Server back end in Access

Posted on 2011-03-04
11
668 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 85
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 85

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
 

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 85
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

617 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