Solved

Read-only problem with SQL Server back end in Access

Posted on 2011-03-04
11
652 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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

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

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.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

706 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

19 Experts available now in Live!

Get 1:1 Help Now