Linking an SQLServer view

[Mark A. Sam]

Hello,

I am linking an SQLServer view using the TransferDatabase method, like I
would link a table. The problem is that it brings up a dialog box asking me
for a field(s) to select as a unique indentifier. I am programming this so
that the end user doesn't have the password info for my database, and using
a view so that the user only sees his data. Is there a way to select the
unique key so that it isn't asked for each time?

DoCmd.TransferDatabase acLink, "ODBC Database",
"ODBC;DSN=DBName;UID=user12345;PWD=somepassword;LANGUAGE=us_english;DATABASE=myDatabase",
acTable, "Viewname", "AliasName", False

Thank you for your help and God Bless,

Mark A. Sam

 

[Stefan Hoffmann]

hi Mark,

I am linking an SQLServer view using the TransferDatabase method, like I
would link a table. The problem is that it brings up a dialog box asking me
for a field(s) to select as a unique indentifier. I am programming this so
that the end user doesn't have the password info for my database, and using
a view so that the user only sees his data. Is there a way to select the
unique key so that it isn't asked for each time?

You must use the TableDefs collection for this purpose:

With CurrentDb
.TableDefs.Append _
.CreateTableDef(ADestinationName, 0, _
ASourceName, CONNECTION_ODBC)
Execute "CREATE INDEX pk_" & ADestinationName & _
" ON " & ADestinationName & _
"(" & APrimaryKey & ") WITH PRIMARY;"
End With

where ADestinationName is the name of your view (maybe you have to use
the qualified name, e.g. dbo.viewName) and ASourceName is the name for
the link (e.g. viewName). CONNECTION_ODBC is the connection string.

APrimaryKey is the primary key field or field list (comma seperated).


mfG
--> stefan <--

 

[Mark A. Sam]

Thank you Stefan, this worked great.

hi Mark,

You must use the TableDefs collection for this purpose:

With CurrentDb
.TableDefs.Append _
.CreateTableDef(ADestinationName, 0, _
ASourceName, CONNECTION_ODBC)
Execute "CREATE INDEX pk_" & ADestinationName & _
" ON " & ADestinationName & _
"(" & APrimaryKey & ") WITH PRIMARY;"
End With

where ADestinationName is the name of your view (maybe you have to use the
qualified name, e.g. dbo.viewName) and ASourceName is the name for the
link (e.g. viewName). CONNECTION_ODBC is the connection string.

APrimaryKey is the primary key field or field list (comma seperated).


mfG
--> stefan <--

 

[Mark A. Sam]

Can this be done without using a DSN? I want to distribute the application
without having to have the user configure the dsn.

 

[Pieter Wijnen]

sure,
check out
http://support.microsoft.com/default.aspx?scid=kb;en-us;166392

and

http://www.accessmvp.com/djsteele/DSNLessLinks.html

Pieter

 

[Mark A. Sam]

Thank you Pietre. The second link seems to be what I am looking for.

"Pieter Wijnen"