What am I missing? (1 Viewer)

[FoFa]

SQL server 2008, ODBC connection.

Both DEV. and PROD same.

DEV. can update a view via open it (forms based off the view) and update in form, etc.

PROD, won't allow updates either in the form or opening the view directly.

View permissions are the same on SQL both DEV and PROD.

Delinked and relinked many times.

Via SQL query using the same ID access uses can issue an UPDATE sql, works fine.

PROD can open the table the view is based off of, and can update in both DEV and PROD.

I know I am just missing something, just can't put my finger on it.
DEV. and PROD seem the same.

Ideas?

 

[boblarson]

What about the DSN? Are they both identical?

 

[FoFa]

Using a DSNless connection, only difference is I change a value in a variable to point to one SQL server or the other. Does not seem to be an issue other than for this view for some reason.

 

[boblarson]

Which driver are you calling in your connection?

Also, can you open a view from Access (not in a form) and modify data?

 

[FoFa]

OK, it is something with the link refresh. If i remove the SQL view (access thinks it is a table), and add it back using my DNS (SQL ODBC connection, NOT native Client), select the primary key (on the underlying table) AND select a timestamp, it allows me to update the view through both the open table and via the form. Until I invoke the link refresh code, then it fails to work in either mode.
As far as i can tell, the DSN and internal connection are defined the same.

  CnnStr = "ODBC;Description=Calibration Control;DRIVER=SQL Server;" & _
                 "SERVER=" & ODBC1 & ";UID=" & ODBC3 & ";" & _
                 "PWD=" & ODBC4 & ";DATABASE=" & ODBC2 & ";LANGUAGE=us_english"

And it uses this code:

    For Each tb In db.TableDefs
        If Left(tb.Connect, 4) = "ODBC" Then
            objName = tb.Name
            tb.Connect = newConnectionString
            tb.RefreshLink
            Debug.Print "Refreshed ODBC table " & tb.Name
        End If
    Next tb

 

[boblarson]

If you are on SQL Server 2008 you really should use the SQL Native Client 10 driver.

 

[FoFa]

know what the equivalent connection string for native client would be? As the one I used above as an example?

 

[FoFa]

looked like the only diff in the connection string is:
DRIVER=SQL Server; is changed to DRIVER=SQL Native Client;

But upom refresh the view is NOT updateable. (well it changes from updateable to not).

 

[FoFa]

SQL native driver didn't work on our Citrix box (guess it is older stuff).

never did figure it out, so I went back to an Access query, it is just MUCH slower.

 

[Banana]

What if you executed that query in an ADO recordset and bind to the form instead? This can help with performance in right circumstances.