Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Read-only problem with SQL Server back end in Access

Posted on 2011-03-04
11
Medium Priority
?
675 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

715 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