Why linked sql database is read only

[Al]

I am using the following code to establish a DSN less connection:
**************************
Private Sub Form_Load()
Dim db As dao.Database
Dim tdf As dao.TableDef
Dim rst As dao.Recordset
Dim strServer As String
Dim strDB As String
Dim strTable As String
Dim strConnect As String
Dim strMsg As String, id As String, pw As String

id = "DR_RV"
pw = "drawingreview"


On Error GoTo HandleErr

strConnect = "ODBC;Driver={SQL Server};UID=" _
& id & ";PWD=" & pw & ";"



' Build base authentication strings
'Select Case Me!optAuthentication
' Windows/NT login
' Case 1
' strConnect = "ODBC;Driver={SQL Server};Trusted_Connection=Yes;"
' SQL server login
' Case 2
' strConnect = "ODBC;Driver={SQL Server};UID=" _
' & Me.txtUser & ";PWD=" & Me.txtPwd & ";"
'End Select

' Get rid of old links, if any
Call DeleteLinks

' Create recordset to obtain server, database and table names
Set db = CurrentDb
Set rst = db.OpenRecordset("tblSQLTables", dbOpenSnapshot)
If rst.EOF Then
strMsg = "There are no tables listed in tblSQLTables."
GoTo ExitHere
End If

' Walk through the recordset and create the links
Do Until rst.EOF
strServer = rst!SQLServer
strDB = rst!SQLDatabase
strTable = rst!SQLTable
' Create a new TableDef object
Set tdf = db.CreateTableDef(strTable)
' Set the Connect property to establish the link
tdf.Connect = strConnect & _
"Server=" & strServer & _
";Database=" & strDB & ";"
tdf.SourceTableName = strTable
' Append to the database's TableDefs collection
db.TableDefs.Append tdf
rst.MoveNext
Loop

'strMsg = "Tables loaded successfully."

rst.Close
Set rst = Nothing
Set tdf = Nothing
Set db = Nothing

ExitHere:
'MsgBox strMsg, , "Link SQL Tables"
Exit Sub

HandleErr:
Select Case Err
Case Else
strMsg = Err & ": " & Err.Description
Resume ExitHere
End Select

End Sub
********************************
The database in sql was originally an access 2003 database. I converted the
database to sql using the Microsoft SQL Server Migration Assistant for Access.
all tables have there pk and fk established and links are in effect. I
created an account on the sql server with ID and PW as stated above in the
code (IDR_RV, PW: drawingreview). I am able to link the tables but if I
tried to enter any data, it says that it is a read only db. I have checked
all the rights on the sql server end. I logged in as the DR_RV (SQL
Authentication) user and was able to insert and update. Any ideas?
thanks
Al

 

[Proko]

Hi Al,
Check your tables. They will be read only if they do not contain at least
one unique indexed field. Search google for 'SSW Upsizing Pro'. It is a free
download with not all functionallity provided but still enough to highlight
lots of potential problems.
Proko

 

[Michael Gramelspacher]

I am using the following code to establish a DSN less connection:
**************************
Private Sub Form_Load()
Dim db As dao.Database
Dim tdf As dao.TableDef
Dim rst As dao.Recordset
Dim strServer As String
Dim strDB As String
Dim strTable As String
Dim strConnect As String
Dim strMsg As String, id As String, pw As String

id = "DR_RV"
pw = "drawingreview"


On Error GoTo HandleErr

strConnect = "ODBC;Driver={SQL Server};UID=" _
& id & ";PWD=" & pw & ";"



' Build base authentication strings
'Select Case Me!optAuthentication
' Windows/NT login
' Case 1
' strConnect = "ODBC;Driver={SQL Server};Trusted_Connection=Yes;"
' SQL server login
' Case 2
' strConnect = "ODBC;Driver={SQL Server};UID=" _
' & Me.txtUser & ";PWD=" & Me.txtPwd & ";"
'End Select

' Get rid of old links, if any
Call DeleteLinks

' Create recordset to obtain server, database and table names
Set db = CurrentDb
Set rst = db.OpenRecordset("tblSQLTables", dbOpenSnapshot)
If rst.EOF Then
strMsg = "There are no tables listed in tblSQLTables."
GoTo ExitHere
End If

' Walk through the recordset and create the links
Do Until rst.EOF
strServer = rst!SQLServer
strDB = rst!SQLDatabase
strTable = rst!SQLTable
' Create a new TableDef object
Set tdf = db.CreateTableDef(strTable)
' Set the Connect property to establish the link
tdf.Connect = strConnect & _
"Server=" & strServer & _
";Database=" & strDB & ";"
tdf.SourceTableName = strTable
' Append to the database's TableDefs collection
db.TableDefs.Append tdf
rst.MoveNext
Loop

'strMsg = "Tables loaded successfully."

rst.Close
Set rst = Nothing
Set tdf = Nothing
Set db = Nothing

ExitHere:
'MsgBox strMsg, , "Link SQL Tables"
Exit Sub

HandleErr:
Select Case Err
Case Else
strMsg = Err & ": " & Err.Description
Resume ExitHere
End Select

End Sub
********************************
The database in sql was originally an access 2003 database. I converted the
database to sql using the Microsoft SQL Server Migration Assistant for Access.
all tables have there pk and fk established and links are in effect. I
created an account on the sql server with ID and PW as stated above in the
code (IDR_RV, PW: drawingreview). I am able to link the tables but if I
tried to enter any data, it says that it is a read only db. I have checked
all the rights on the sql server end. I logged in as the DR_RV (SQL
Authentication) user and was able to insert and update. Any ideas?
thanks
Al

excerpt from my code:

'See Artcle: 209123
'Creating Virtual Indexes with SQL Data Definition Queries
'These statements make the SQL Views updateable by defining
'the primary key columns for Access to use.

On Error Resume Next

CurrentProject.Connection.Execute _
"CREATE UNIQUE INDEX pk_qryBaptisms ON dbo_qryBaptisms (BaptNo);"