X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Problem Connect SQL View, SQL Server 2008 R2    
post Jun 20 2019, 04:34 AM

Posts: 98
Joined: 12-February 15


I have the problem when connect (code vba) with odbc driver SQL the view, the message block and asks the index field

i use this string for connect:

strConnectionString = "ODBC;DRIVER=SQL Server; " & _

DoCmd.RunSQL "drop table " & "dbo_DocUniTestata"
DoCmd.TransferDatabase acLink, "ODBC Database", _
strConnectionString, acTable, "DocUniTestata", "dbo_DocUniTestata"

attached file with message block

it' s possible to remove message??

This post has been edited by carlo: Jun 20 2019, 04:35 AM
Attached File(s)
Attached File  message.jpg ( 39.46K )Number of downloads: 0
Go to the top of the page
post Jun 20 2019, 04:49 AM

UtterAccess Editor
Posts: 10,092
Joined: 7-December 09
From: St Augustine, FL

This is because Access can't automatically determine what the unique field should be. Select one or more applicable fields for your unique index, and that information will be saved (until next time you connect to the view, anyway).

Alternatively, update the view on SQL Server to include a primary key of some sort.

You can also set the index programmatically, but it's a bit of a process to do so.


Go to the top of the page
post Jun 20 2019, 05:00 AM

Posts: 98
Joined: 12-February 15

how do I create index in the SQL view??


Go to the top of the page
post Jun 20 2019, 05:49 AM

Posts: 998
Joined: 26-January 14
From: London, UK

Do you expect the view to be updateable or not? Access requires you to specify a key only if you want the view to be updateable but not all views are updateable in SQL Server so this may not be necessary.

For most views, even if they are updateable, it is not necessary to create an index. Your view should always include some column(s) that are unique within the view (i.e. a key) but there's no need to create an index on the view itself because SQL Server will make use of indexes on the underlying tables. I think Jack is just suggesting that you make sure you have key columns in your view and specify those columns as a key when you link.

Go to the top of the page
post Jun 20 2019, 06:21 AM

Posts: 617
Joined: 26-May 15
From: The middle of Germany

Use CreateTableDef instead of DoCmd.TransferDatabase to create the local linked object.
Dim db            As DAO.Database
Dim td            As DAO.TableDef

Set db = CurrentDb
Set td = db.CreateTableDef("DocUniTestata")
With td
   .SourceTableName = "dbo_DocUniTestata"
   .Connect = strConnectionString
End With
db.TableDefs.Append td

Go to the top of the page
post Jun 20 2019, 09:24 AM

Posts: 98
Joined: 12-February 15

thank you very much

i tried it it works

best regards

Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    21st September 2019 - 06:46 AM