ODBC links

[dave]

Does anyone use ODBC administrator to create a new DSN and link to the SQL server project DB? I believe doing this gives you all of the M$ Access functionality without worrying about the Bentley DM hangups.

[PeterJ]

Dave, you can use ODBC, but for SQL server you'd better create an .adp (msaccess data project) in msaccess, that's faster. You can link while using the same parameters as AP does, check the connection string in project.ini.

For Oracle projects, ODBC is the best solution.

[dave]

.adp of course. Thanks Peter.

[tw_echo]

Dave,
There is another possible way to do this if you are interested in trying. There is code from Access MVPS's created that allows you to create DSN-less ODBC connections through code. The links are as follows: http://www.mvps.org/access/tables/tbl0010.htm and http://www.mvps.org/access/tables/tbl0009.htm and http://www.databasejournal.com/features/mssql/article.php/10894_1491011_1

I basically implement this code with a form that lists current Bentley databases (datawarehouse database in our case) on SQL Server and select the project that I Would like to view. The code then executes and creates the connection to the selected database while refreshing all the linked tables. I used to use this method when deploying Access apps as I did not have to worry about the user having the DSN file, etc.

This esentially will be the same as an ADP file but I can view any SQL database from one Access database rather than one database per SQL database. I guess it all depends what you want to do with this access database will ultimately decide which solution works best for you.

Something to chew on when you have nothing else to do...like that will ever happen hey??

[dave]

Thanks Tim, great links there! I will check them out in detail when I get some "free" time :)