In a Replication Config (Transaction) >> HEADS UP >> "ANSI_NULLS set incorr
Hi there
im linking a view on an ms sql server to my access database using VBA using DoCmd.TransferDatabase acLink, etc.etc.
How ever, I can NOT get the `Select Unique record Identifier` pop up to stop coming up when the link is being made. I dont care about needing a unique identifier, its a read only record source.
The only way ive found is using sendkeys command to cancel the popup - however this can not be relied on, if it happens when the user doesn't have the access window in focus, it fails and the pop up appears.
That pop up is way beyond many of my users to even press cancel and looks very unprofessional :)
any ideas?
many thanks
martin
[668 byte] By [
Babbage] at [2007-12-24]
Hi Martin,
I've escalated your question to our support team for assistance and will post their response as soon as possible.
thanks,
-brenda (ISV Buddy Team)
Per one of our support engineers:
The reason why Access popup the dialog is the source table has no index and primary keys on. The behavior is by design. So I paste following description of the importing steps in HELP documents.
==================
If you're linking a table and it doesn't have an index that uniquely identifies each record, then Microsoft Access displays a list of the fields in the linked table. Click a field or a combination of fields that will uniquely identify each record, and then click OK.
http://office.microsoft.com/en-us/assistance/HP051876101033.aspx
==================
If we do want to bypass the dialog (It’s not recommended. We’d better have one column as PK), one of the workarounds is just as you mentioned in the post that we may send {Enter} or {ESC} key to the popup. The other workaround is to add the index by coding before we call ‘TransferDatabase’. Please refer to following code snippet (Suppose I have a table named ‘tbName’ in Northwind database, before we do data transferring, we add an index on the ‘ID’ column first.J):
======================
Sub EnsurePrimaryKey()
Dim dbCon As New ADODB.Connection
Dim dbCmd As New ADODB.Command
Dim szConnectionString As String
szConnectionString = "Driver={SQL Server};Server=SHA-LM-WK;Database=NORTHWIND;Trusted_Connection=yes;"
dbCon.ConnectionString = szConnectionString
dbCon.Open
Set dbCmd.ActiveConnection = dbCon
dbCmd.CommandType = adCmdText
dbCmd.CommandText = "CREATE UNIQUE INDEX [idx] ON [dbo].[tbName] ([ID])"
On Error Resume Next
dbCmd.Execute
End Sub
=====================-brenda (ISV Buddy Team)
no good trying to add an index based on a view which may include several tables! :)
Im guessing the overall answer is `no you cant stop it propperly` - theres no overriding function. *HINT HINT OFFICE DEVELOPMENT TEAM :)*
How about a `lower level` method of linking a table in code that you can by pass the docmd command with?
many thanks
martin
Here's the support engineer's response:
I think there must be some misunderstanding. Index/Primary is very important even critical for a database system. That’s why Access asks us to select a primary key if there’s none. Otherwise, the linked table is ready-only; we can not update the data without the key column. I’d like to emphasize that behavior is by design. However, there’s some workaround regarding this issue:
1. Indexed View (Similar to add index dynamically onto a table)
We can add the index on a view in SQL Server. Please refer to following articles
Improving Performance with SQL Server 2000 Indexed Views
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/indexvw.mspx
Designing Indexed Views
http://msdn2.microsoft.com/en-us/library/ms187864.aspx
===================
CREATE VIEW dbo.vwName with SCHEMABINDING
AS
SELECT ID,Name FROM dbo.tbName
CREATE UNIQUE CLUSTERED INDEX [Idx]
ON [dbo].[vwName]([ID])
===================
We may create an indexed view by coding, and then link this view into Access by calling ‘TransferDatabase’. The code is similar to what I posted in the previous thread.
2. We can bypass the popup, however, as I mentioned above, the data is read-only, we can not modify the data from the linked table/view. Here is the code:
==================
Option Compare Database
Const tblLocalTableName As String = "NameList_SQL"
Function TableNotExist(strTblName As String) As Boolean
On Error Resume Next
Dim db As DAO.Database, tbl As DAO.TableDef
Set db = CurrentDb
Set tbl = db.TableDefs(strTblName)
If Err.Number = 3265 Then ' Item not found.
DoesTblExist = False
Exit Function
End If
DoesTblExist = True
End Function
Function CreateODBCLinkedTables() As Boolean
On Error GoTo CreateODBCLinkedTables_Err
Dim strConn As String
Dim db As Database, tbl As TableDef
Set db = CurrentDb
'Register DSN
DBEngine.RegisterDatabase "NorthwindSQL", "SQL Server", True, _
"Description=Nothwind Employee" & Chr(13) & "Server=SHA-LM-WK" & Chr(13) & "Database=Northwind"
CreateODBCLinkedTables = True
MsgBox "Refreshed ODBC Data Sources", vbInformation
'Create Link Table
If Not TableNotExist(tblLocalTableName) Then
'Create Link Table
strConn = "ODBC;"
strConn = strConn & "DSN=NorthwindSQL;"
strConn = strConn & "DATABASE=Northwind;"
strConn = strConn & "Trusted_Connection=yes;"
strConn = strConn & "TABLE=tbName"
Set tbl = db.CreateTableDef(tblLocalTableName, dbAttachSavePWD, "tbName", strConn)
db.TableDefs.Append tbl
Else
'Refresh Link Table
Set tbl = db.TableDefs(tblLocalTableName)
tbl.Connect = strConn
tbl.RefreshLink
End If
CreateODBCLinkedTables_End:
Exit Function
CreateODBCLinkedTables_Err:
MsgBox Err.Description, vbCritical, "MyApp"
Resume CreateODBCLinkedTables_End
End Function
==================
I hard coded some information in the code:
SQL Server – SHA-LM-01
Database – Northwind
Table – tbName(ID as int not null, Name as nvarchar(100))
-brenda (ISV Buddy Team)
You madam, are a complete and shining star :D
many thanks and much appriciated,
martin
well thank you good sir! I'll pass on your accolades to the engineer.
-brenda (ISV Buddy Team)
Hello! I do a hook on reply.
I need create a linked table to a view with unique index on MSSQL at execution time and for data editing.
The view use schemabinding and has a PK. The transferdatabase command on VBA work and linked table is created.
I follow that suggestions (it′s working), but when editing a record take a ARITHABORT error.
I know about SET options must be setting on MSSQL, but how I do it at execution time on VBA?
Thanks
Brenda, you appear to have the solution I seek ... but, as a true ameteur, I am struggling to understand the solution.
In my application, I have already verified that the SQL table that I want to link to exists using a field [TestDB] where IsNull([TestDB]) = False indicates the database does exist. Can you simplify your solution for me please? I want to get rid of the SendKeys statement.
Here is an excerpt of my code:
If IsNull(TestDB) = False Then
DoCmd.DeleteObject acTable, "T_ProdCtrl_Pattern"
SendKeys "{ESC}", False
DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=dsn;UID=login;PWD=password;LANGUAGE=us_english;DATABASE=database, acTable, "dbo.SQL_Table", "Access_Table", , True
End If
Thank you for your help.
per the support engineer:
Actually, I do not quite understand our buddy’s exact request. Does he want us to troubleshoot on his existing error message or to know howto switch on the settings in VBA? Here’s my update. Hopefully I can help him. J
1. Please refer to http://msdn2.microsoft.com/en-us/ms189118.aspx for detail information of these settings.
2. EXEC ‘SET ARITHABORT ON’ is a doable way to execute SQL in VBA.
We can also put this setting into a store procedure and call the store procedure from the VBA code.
-brenda (ISV Buddy Team)
From the thread, i was able to setup indexed views & links through ODBC w/o the popup. Thx U.
However, I can not edit the data from the view in my Access forms. It says the ARITHABORT setting is incorrect.
I set it ON during the View & Index creation. I set it ON & OFF for testing just before attempting to edit data on my form(s) -- since it says it's wrong but does not say what it should be? Your post says ON but that makes no difference. I have 1 table for each view i want to update data.
When & how often exactly do we set the ARITHABORT to ON.
I realize this may be one of those error messages that don't actually relate to the problem.
'-
Public Sub sb_MXQAddIndexes_nViews_ADO()
Dim rs As DAO.Recordset
Dim oCn As New ADODB.Connection
Dim oCmd As New ADODB.Command
oCn.ConnectionString = _
"Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=xxx;" & _
"Data Source=zzz"
oCn.Open
oCmd.CommandType = adCmdText
oCmd.ActiveConnection = oCn
' Settings
oCmd.CommandText = _
"SET NUMERIC_ROUNDABORT OFF;" & vbCrLf & _
"SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, " & _
"QUOTED_IDENTIFIER, ANSI_NULLS ON;"
oCmd.Execute
Set rs = CurrentDb.OpenRecordset("zdTbl_Indexes", DAO.dbOpenDynaset)
With rs
.MoveFirst
Do Until .EOF
' View
oCmd.CommandText = _
"CREATE VIEW dbo.v" & !TableName & " WITH SCHEMABINDING AS " & vbCrLf & _
vbTab & "Select " & fn_MXQGetColumns(!TableName) & " From dbo." & !TableName
oCmd.Execute
' Index
oCmd.CommandText = _
"CREATE UNIQUE CLUSTERED INDEX " & !IdxName & vbCrLf & _
vbTab & "ON [dbo].[v" & !TableName & "](" & !IdxColumns & ")"
oCmd.Execute
.MoveNext
Loop
End With 'rs
' Cleanup
rs.Close
Set rs = Nothing
Set oCmd = Nothing
oCn.Close
Set oCn = Nothing
MsgBox "Build Views & Indexes DONE!"
End Sub
'-
Public Sub sb_MXQSetArithAbort_ADO()
Dim oCn As New ADODB.Connection
Dim oCmd As New ADODB.Command
oCn.ConnectionString = _
"Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=xxx;" & _
"Data Source=zzz"
oCn.Open
oCmd.CommandType = adCmdText
oCmd.ActiveConnection = oCn
' Settings
oCmd.CommandText = _
"SET ARITHABORT ON;"
oCmd.Execute
' Cleanup
Set oCmd = Nothing
oCn.Close
Set oCn = Nothing
MsgBox "Option Set!"
End Sub
'-
We had trouble setting ARITHABORT so we did the following from Query Analyzer which corrected the problem (enabled AddNew/Update).
Exec sp_Configure 'user options', 64
Reconfigure
Remember the 'Reconfigure' after sp_configure.
*** Plz disregard the sb_MXQSetArithAbort procedure in previous post. It did not work.
After a final test, i posted the release to production and did another round of test cases. All worked. Then we engaged transaction replication. All the test cases then failed -- ANSI_NULLS incorrectly set. It was production so we needed to revert to the last release quickly, however, not before a retest after disengaging replication. All the test cases again passed.
No solution yet on that one. Suggestions?
Note: we did use sp_DBoptions as follows for the master & subscriber servers:
use master
exec sp_dboption 'xxx', 'ansi nulls', 'true'
exec sp_dboption 'xxx', 'quoted identifier', 'true'
exec sp_dboption 'xxx', 'numeric roundabort', 'false'
exec sp_dboption 'xxx', 'concat null yields null', 'true'
exec sp_dboption 'xxx', 'ANSI warnings', 'true'
exec sp_configure ‘user options’, 64
reconfigure
Disconcerting that there are so many gotcha's using the MS flagship SQL Server. (2000 for this client, not 2005 yet)
Hello there. I see this thread is a bit old, but was kind of hoping you might have an answer for me.
I have kind of the opposite problem that the original thread has. I have a number of views in a SQL Server database that is connected to my Access application. When you manually add the views as linked tables into Access, its asks you what the key is for each view. This is fine. However, when the application is deployed in production, the application refreshes the links to the tables and views each time the user opens the program. It does this for two reasons. 1) so that we don't have to manually disconnect and reconnect the tables each time we move from development to production, and 2) in this particular instance because the tables are connected with the TCP/IP DBMSSOCN network library. We do this (and have been doing so for years across many many applications) like this:
For Each tbl In CurrentDb.TableDefs
If tbl.Connect <> "" And Left(tbl.Name, 6) <> "merge_" Then
If DLookup("RelinkTables", "Version") Then
'Production
tbl.Connect = "ODBC;DRIVER=SQL Server;SERVER=<servername>;APP=Microsoft Data Access Components;WSID=COMPAQ;Network=DBMSSOCN;Trusted_Connection=no;UID=<username>;PWD=<password>;Database=BlueHeron"
Else
'Development
tbl.Connect = "ODBC;DRIVER=SQL Server;SERVER=tower;APP=Microsoft Data Access Components;WSID=COMPAQ;Network=DBNMPNTW;Trusted_Connection=yes;Database=BlueHeron"
End If
tbl.RefreshLink
End If
Next
The problem is (and its almost unbelieveable that I've just noticed this), that when the links are refreshed this way, the designated unique identifer is not preserved for views, making a view that would otherwise be editable uneditable. I was looking to see if there was an argument that I could supply to the connection string to force it to preserve the key. I did not find one. Got a clue how I can preserve the key on views when refreshing the linked tables like this?
Thanks even if you do not know or it is not possible. 
RJ
Database Whiz Consulting
I personally never refresh links, I always delete/recreate them (refreshes things a relink doesnt)
I have a bit of code that does this for me, so I don't have to do it manually (just cycles through tables deleting/relinking as required). I don't have to manually disconnect/reconnect tables then. Infact I run development versions, test versions and live versions of my application at the same time, sometimes including different fields/tables/etc. per database I connect to. I just tell the front end which version I want it to point at (or in certian live situations, to force usage of the live database) - job done. Application checks to see what its connected to when it boots, only does the relinking as required rather than every time.
Could you go down that route?
Martin
