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
Solved

Read-only problem with SQL Server back end in Access

Posted on 2011-03-04
11
661 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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
 

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How do I bind a table in Access 2013 to a form 5 34
IIF in access query 19 25
Database keeps telling me Recordset Locked 10 24
MS SQL Server select from Sub Table 14 26
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

828 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