Creating a DSNless connection to SQL

• Karen Grube

  SSC Eights!

  Points: 994

  More actions

  October 21, 2005 at 9:41 am

  #68500

  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

• AJ Ahrens

  SSC-Insane

  Points: 20676

  More actions

  October 21, 2005 at 10:45 am

  #599204

  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

• MTSmith

  SSC-Addicted

  Points: 449

  More actions

  October 25, 2005 at 7:26 am

  #599782

  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]