ODBC - update on a linked table <table name> failed

[anandsbr]

Hello,

I have an Access Database 2007 that is linked to our SQL Server 2008.
In my MSA I have a Form that has recordsource which is view. User can update his userid so far without any issues. Now I have selected
a new column in the view which already exists in one of the main table. since then I started getting this error when i try to update userid using
rst.update command. As soon as I remove the column from the view and relink SQL server database again, it works just fine.

Please help.

 

[boblarson]

1. Does your SQL Server table have an identity column (Primary Key) set? It needs to have it set to do updates.
2. Which operating system are you on and which SQL Server Driver have you used? For 2008 you really should use the SQL Native Client 10 driver and not the standard SQL Server Driver - at least until you are on Windows 7.

 

[anandsbr]

Please see my reply below.

1. Does your SQL Server table have an identity column (Primary Key) set? It needs to have it set to do updates.

Yes. Record already exists and primary key is in the where clause of the select statement of recordset.

2. Which operating system are you on and which SQL Server Driver have you used? For 2008 you really should use the SQL Native Client 10 driver and not the standard SQL Server Driver - at least until you are on Windows 7.

Windows XP Professional.

This is connectionstring is being used to connect.

DBC;DRIVER=SQL Server;SERVER=<<server>>,<<port>>\database;DATABASE=<<database>>;Trusted_Connection=Yes"

 

[boblarson]

Please see my reply below.



Windows XP Professional.

This is connectionstring is being used to connect.

DBC;DRIVER=SQL Server;SERVER=<<server>>,<<port>>\database;DATABASE=<<database>>;Trusted_Connection=Yes"

The connection should be:

DBC;DRIVER=SQL Native Client 10;SERVER=<<server>>,<<port>>\database;DATABAS E=<<database>>;Trusted_Connection=Yes"

SQL Server 2008 on Vista should have the SQL Native Client 10 driver as the driver. Make sure that gets installed on the machines which will be using it.

This is information direct from one of the Microsoft SQL Server folks - Mary Chipman -

with xp you need

SQL Server - for versions up to SQL Server 2000
SQL Native Client for 2005
SQL Native Client 10 for 2008

with Vista
SQL Server - for versions up to SQL Server 2005
SQL Native Client 10 for 2008

with Win 7
SQL Server - for all current SQL Server versions (to 2008)

(I'm guessing here but you might need SQL Native Client 10 for 2008r2 on Win7)

 

[anandsbr]

Hi,

I am unable to change the DRIVER to SQL Native Client 10 as when I try to connect, it prompts for dsn name which I do not have. I use ADO.NET for connectivity. I also checked connectionstring.com website where they have given all sorts of connectionstrings for each connectivity. Nothing works for me except that what I have as it prompts for me to pick up .dsn name.

Also what I have found out was, it does not give error for few records when it updates. but get error for more records. So it is confusing me more now.

Any thoughts...

Thanks.

 

[boblarson]

This is connectionstring is being used to connect.

DBC;DRIVER=SQL Server;SERVER=<<server>>,<<port>>\database;DATABAS E=<<database>>;Trusted_Connection=Yes"

You gave that so it would be:

This is connectionstring is being used to connect.

DBC;DRIVER=SQL Native Client 10;SERVER=<<server>>,<<port>>\database;DATABAS E=<<database>>;Trusted_Connection=Yes"

 

[anandsbr]

You gave that so it would be:

This is connectionstring is being used to connect.

DBC;DRIVER=SQL Native Client 10;SERVER=<<server>>,<<port>>\database;DATABAS E=<<database>>;Trusted_Connection=Yes"

Oops, It is ODBC;DRIVER=SQL Server;SERVER=<<server>>,<<port>>\database;DATABAS E=<<database>>;Trusted_Connection=Yes"

Also I tried removing ODBC part from the connectionstring but it would not work meaning it still prompts me to choose dsn name.

 

[boblarson]

So this doesn't work?

ODBC;DRIVER=[B][COLOR=red]SQL Native Client 10[/COLOR][/B];SERVER=<<server>>,<<port>>\database;DATABASE=<<database>>;Trusted_Connection=Yes"

 

[anandsbr]

No, It does not work.

It was a typo but in the code it starts with ODBC. I tried removing and still it prompts me to give .dsn name.

 

[boblarson]

So, how are you trying to use this connection? Are you trying to use it with ADO code? If so, what is the code you are using (completely) and how about filling in the blanks on the rest of this (the actual server, database, etc. that you are passing to it)?

 

[anandsbr]

Bob,

I am using ADO connection and here is the code for connectivity.

1) Form frmstartup is being called first which in turn calls form frmDBSelect to get db information.

2) code in Form frmDBselect is...

Code
-----
Option Compare Database
Option Explicit

Private Sub cmdOK_Click()

Select Case Me!grpDBServer.Value
Case Me!optJPROD.OptionValue
Case Me!optJDEV.OptionValue
CurrentDb.Properties("DBServerName") = "CT2WD002.svr.company.net,59384\AMLMetrics"
CurrentDb.Properties("AppTitle") = Me!txtAppTitle.Value & " - DEV"
End Select

Application.RefreshTitleBar
Me.Visible = False

End Sub

Private Sub Form_Open(Cancel As Integer)

Select Case CurrentDb.Properties("DBServerName")
Case "CT2WD002.svr.company.net"
Me!grpDBServer.Value = Me!optJDEV.OptionValue
End Select

End Sub

2) Next global objects gets instantiated.
gcnnProject as New clsADOCnn (Provides global ADO connection object)

Code in Class clsADOcnn
-------------------------

Option Compare Database
Option Explicit

Private Const mcstrmod As String = "clsADOcnn"
Private mstrServer As String
Private Const mcstrDatabase As String = "AMLMetrics"

Private mcnnCurrent As New ADODB.Connection

Public Property Get Connection() As ADODB.Connection

With mcnnCurrent
If .State = adStateClosed Then
.ConnectionString = Me.ConnectionString
.Open
End If
End With

Set Connection = mcnnCurrent

End Property

Public Property Get ConnectionString() As String
ConnectionString = "DRIVER=SQL Server;SERVER=" & Me.Server & ";DATABASE=" & Me.Database & ";Trusted_Connection=Yes"

Commented code in Red (Tried all options in the Provider or Server)
-------------------
' ConnectionString = "Provider=SQLNCLI10;Data Source=" & Me.Server & ";Initial Catalog=" & Me.Database & ";Integrated Security=True"
End Property

Public Property Get Server() As String
If mstrServer = vbNullString Then
Server = CurrentDb.Properties("DBServerName")
Else
Server = mstrServer
End If
End Property

Public Property Get Database() As String
Database = mcstrDatabase
End Property

3) Then function tablereconnect() is being called from form frmstartup where linking tables and queries happens.

Code of tablereconnect()
-------------------------

Function TableReconnect(Optional strBaseMessage As String) As Boolean
Const cstrProc As String = "TableReconnect"
On Error GoTo ErrHandler

TableReconnect = True ' Default return value

Dim db As DAO.Database
Dim td As DAO.TableDef
Dim qd As DAO.QueryDef
Dim blnStartFormOpen As Boolean
Dim intTableCount As Integer
Dim intTable As Integer
Dim ctlLabel As Label

blnStartFormOpen = IsFormOpen("frmStartup")
If blnStartFormOpen = True Then
Set ctlLabel = Forms!frmStartup!lblStatus
ctlLabel.Caption = strBaseMessage & vbCrLf & "0% complete"
End If

Set db = CurrentDb()
intTableCount = db.TableDefs.Count
intTable = 0
For Each td In db.TableDefs
If Left(td.Name, 1) <> "~" Then
If Len(Nz(td.Connect, "")) > 0 Then
' td.Connect = "ODBC;" & gcnnProject.ConnectionString
td.Connect = gcnnProject.ConnectionString
td.RefreshLink
If blnStartFormOpen = True Then
intTable = intTable + 1
ctlLabel.Caption = strBaseMessage & vbCrLf & CStr(CInt(intTable * 100 / intTableCount)) & "% complete"
Forms!frmStartup.Repaint
DoEvents
Else
Debug.Print "Table " & td.Name & " refreshed at " & Time
End If
End If
End If
Next

For Each qd In db.QueryDefs
If Left(qd.Name, 1) <> "~" Then
If Len(Nz(qd.Connect, "")) > 0 Then
qd.Connect = "ODBC;" & gcnnProject.ConnectionString
' qd.Connect = gcnnProject.ConnectionString
Debug.Print "Query " & qd.Name & " refreshed at " & Time
End If
End If
Next

' Restore view indexes
If blnStartFormOpen = True Then
ctlLabel.Caption = strBaseMessage & vbCrLf & "Creating indexes: 1 of 3"
Forms!frmStartup.Repaint
DoEvents
End If
db.Execute "CREATE INDEX PrimaryIndex ON AlertAssign_UI_V (AlertSurrogateId) WITH PRIMARY"
If blnStartFormOpen = True Then
ctlLabel.Caption = strBaseMessage & vbCrLf & "Creating indexes: 2 of 3"
Forms!frmStartup.Repaint
DoEvents
End If
db.Execute "CREATE INDEX PrimaryIndex ON AlertSelect_UI_V (QCReviewId) WITH PRIMARY"
If blnStartFormOpen = True Then
ctlLabel.Caption = strBaseMessage & vbCrLf & "Creating indexes: 3 of 3"
Forms!frmStartup.Repaint
DoEvents
End If
db.Execute "CREATE INDEX PrimaryIndex ON ReviewType_V (ReviewTypeId) WITH PRIMARY"

ExitHere:
On Error Resume Next
Set ctlLabel = Nothing
Set td = Nothing
Set qd = Nothing
Set db = Nothing
Exit Function

ErrHandler:
TableReconnect = False
Call sys_ErrorHandler(mcstrmod & "." & cstrProc, Err.Number, Err.Description)
Resume ExitHere
Resume

End Function




Hope this helps...