Linking an SQLServer view

  • Thread starter Thread starter Mark A. Sam
  • Start date Start date
M

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
 
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?
Click to expand...
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 <--
 
Thank you Stefan, this worked great.


Stefan Hoffmann said:
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 <--
Click to expand...
 
Can this be done without using a DSN? I want to distribute the application
without having to have the user configure the dsn.
 
Thank you Pietre. The second link seems to be what I am looking for.

"Pieter Wijnen"
 
Back
Top