How to create an Access database with a DSN-less linked table to a SQL database?
Posted on 2012-08-20
Hey guys, my question is pretty straight forward. There is a database on our SQL 2008 server and it has just one table in it. All I want to do is link to that table from an Access 2007 database where a person can make changes and additions. I know how to create a DSN and then link a table via a DSN using ODBC, that's easy. But here's kicker: I want to put this Access database file on a shared drive and let a good amount of people open it up and read/write to it. When I do that using the ODBC & DSN method, as soon as they try to double click the table it fails throwing an "ODBC--connection failed" error because the DSN I created on my computer when I first created the Access file doesn't exist on all the users computers.
So..........how do I have Access work like I want to but without the need of a DSN? Can I use an ADO connection string in stead? That would be awesome if I could. Thanks guys!