UtterAccess.com
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    
 
   
carlo
post Jun 20 2019, 04:34 AM
Post#1



Posts: 97
Joined: 12-February 15



Hi,

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; " & _
"SERVER=server\iws;DATABASE=enologia;UID=sa;pwd=sa"


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

thank
Carlo
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
 
jleach
post Jun 20 2019, 04:49 AM
Post#2


UtterAccess Editor
Posts: 10,037
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.

hth

--------------------
Go to the top of the page
 
carlo
post Jun 20 2019, 05:00 AM
Post#3



Posts: 97
Joined: 12-February 15





how do I create index in the SQL view??


thank

Go to the top of the page
 
nvogel
post Jun 20 2019, 05:49 AM
Post#4



Posts: 976
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
 
PhilS
post Jun 20 2019, 06:21 AM
Post#5



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


Use CreateTableDef instead of DoCmd.TransferDatabase to create the local linked object.
CODE
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
 
carlo
post Jun 20 2019, 09:24 AM
Post#6



Posts: 97
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    19th August 2019 - 11:25 AM