In a Replication Config (Transaction) >> HEADS UP >> "ANSI_NULLS set incorrectly" error  
Author Message
Babbage





PostPosted: Visual Basic for Applications (VBA), In a Replication Config (Transaction) >> HEADS UP >> "ANSI_NULLS set incorrectly" error Top

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


Microsoft ISV Community Center Forums3  
 
 
MS ISV Buddy Team





PostPosted: Visual Basic for Applications (VBA), In a Replication Config (Transaction) >> HEADS UP >> "ANSI_NULLS set incorrectly" error Top

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)

 
 
MS ISV Buddy Team





PostPosted: Visual Basic for Applications (VBA), In a Replication Config (Transaction) >> HEADS UP >> "ANSI_NULLS set incorrectly" error Top

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.< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

 

==================

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)



 
 
Babbage





PostPosted: Visual Basic for Applications (VBA), In a Replication Config (Transaction) >> HEADS UP >> "ANSI_NULLS set incorrectly" error Top

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


 
 
MS ISV Buddy Team





PostPosted: Visual Basic for Applications (VBA), In a Replication Config (Transaction) >> HEADS UP >> "ANSI_NULLS set incorrectly" error Top

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:< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

 

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)



 
 
Babbage





PostPosted: Visual Basic for Applications (VBA), In a Replication Config (Transaction) >> HEADS UP >> "ANSI_NULLS set incorrectly" error Top

You madam, are a complete and shining star :D

many thanks and much appriciated,

martin

 
 
MS ISV Buddy Team





PostPosted: Visual Basic for Applications (VBA), In a Replication Config (Transaction) >> HEADS UP >> "ANSI_NULLS set incorrectly" error Top

well thank you good sir! I'll pass on your accolades to the engineer. Smile

-brenda (ISV Buddy Team)

 
 
VictorToso





PostPosted: Visual Basic for Applications (VBA), In a Replication Config (Transaction) >> HEADS UP >> "ANSI_NULLS set incorrectly" error Top

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


 
 
Blue Stone Post





PostPosted: Visual Basic for Applications (VBA), In a Replication Config (Transaction) >> HEADS UP >> "ANSI_NULLS set incorrectly" error Top

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.



 
 
MS ISV Buddy Team





PostPosted: Visual Basic for Applications (VBA), In a Replication Config (Transaction) >> HEADS UP >> "ANSI_NULLS set incorrectly" error Top

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)



 
 
MXQBear





PostPosted: Visual Basic for Applications (VBA), In a Replication Config (Transaction) >> HEADS UP >> "ANSI_NULLS set incorrectly" error Top

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
'----------------------------------------------------------------------------------------------


 
 
MXQBear





PostPosted: Visual Basic for Applications (VBA), In a Replication Config (Transaction) >> HEADS UP >> "ANSI_NULLS set incorrectly" error Top

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.


 
 
MXQBear





PostPosted: Visual Basic for Applications (VBA), In a Replication Config (Transaction) >> HEADS UP >> "ANSI_NULLS set incorrectly" error Top

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)


 
 
Database Whiz





PostPosted: Visual Basic for Applications (VBA), In a Replication Config (Transaction) >> HEADS UP >> "ANSI_NULLS set incorrectly" error Top

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. Smile

RJ

Database Whiz Consulting


 
 
Babbage





PostPosted: Visual Basic for Applications (VBA), In a Replication Config (Transaction) >> HEADS UP >> "ANSI_NULLS set incorrectly" error Top

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