October 21, 2005 at 9:41 am
Hi!
I have an ACCESS database with every table linked to SQL Server. Our network admin won't allow users to modify their Data Sources, so I can't create dsn's on each user's workstation in order to use the database. How can I change the links within the ACCESS database to ado connections rather than connecting through a dsn? Is that possible? That is, how can I change the properties of the linked tables to make them direct ado connections rather than going through a dsn?
Please let me know.
Thanks!
Karen Grube
October 21, 2005 at 10:45 am
You can write a routine that reads an Access table and then creates TABLEDEFs for the tables and then connects those using an ODBC string that you also wrote in the code
i.e. strConnection = "ODBC......"
strServer = "MySQLServer"
strDatabase = "MyDatabase"
strTable = RecordSet.fldTableName
myTableDef = CurrentDB.CreateTableDef(strTable)
myTableDef.Connect = strConnection & "server=" & strserver & ";database="& strDatabase & ";"
myTableDef.SourceTableName = strTable
Currentdb.tabledefs.append myTableDef
We use code like above here....
Good Hunting!
AJ Ahrens
webmaster@kritter.net
October 25, 2005 at 7:26 am
You can also go to this website:
http://www.mvps.org/access/modules/mdl0064.htm
This has a download of an Access add in which is called DSNStripper - it automatically creates the DSNless connection for you. Website has all details on how to use.
Good luck!
[mts]
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy