Using DSN-less connection for SQL Server 7 in Access 2003

I have a Access 2003 frontend which is currently linked to a SQL server 7 backend via ODBC.  I wish to remove the administration of conifguring DSN's on each users machine and opt for a DSN-less connection to SQL server.  Does anyone know some code which could run at mdb startup to connect all the tables my database needs.  Hope you can help.  Alan
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

For all your connection string needs:

An example from mine (This does a little more than you ask... but it's a real world example):

Public Const cnProvider As String = "MSDataShape" '"SQLOLEDB"
Public Const cnNetwork As String = "DBMSSOCN" 'Use TCP/IP
Public Const cnSecurity As String = "SSPI"
Public Const cnSrvName As String = "XXX-SERVER-NAME-XXX"    '
Public Const cnTimeout As Integer = 600
Public dbcnn As New ADODB.Connection
Public cnDBName As String
Private Const DatabaseNameFormat = "YOURDATABASENAME"

Public Function fSetADOConnection()
On Error GoTo cnADOErr
'This is for startup initialization - set the connection database
If Len(cnDBName) < 1 Then
    Dim strDbName As String
    'format the string
    strDbName = CurrentDb.TableDefs(1).Connect
    strDbName = Right(strDbName, (Len(strDbName)) - InStr(1, strDbName, "Database", vbTextCompare) - Len("Database"))
    strDbName = Left(strDbName, Len(DatabaseNameFormat))
    'set the Public connection name to the formated string
    cnDBName = strDbName
End If

With dbcnn
    '.Close 'make sure it's closed before trying to assign and reopen

    .Provider = cnProvider
    .ConnectionString = "DATA PROVIDER=SQLOLEDB; Network Library=DBMSSOCN"
    .CommandTimeout = cnTimeout
    .Properties("Data Source") = cnSrvName
    .Properties("Initial Catalog") = cnDBName
    .Properties("Integrated Security") = cnSecurity
    '.Properties("Network Library") = cnNetwork
End With

Exit Function
Also, I just posted this but if you want to run a VBS script on the start up of the MACHINE you can also -- which will register the ODBC connections for you if they don't exist.  This is another real world example -- You'll need to edit it.

'*****      This script creates a DSN for connecting to a
'*****      SQL Server database. To view errors comment out line 16
'*****      Script Name: SetupODBC.vbs
'*****      Author: Randall Vollen
'*****      Depends: VBScript, WScript Host
'*****      Created: 01/04/03

'Values for variables on lines 25 - 29, 32, and 36
'must be set prior to running this script.

      On Error Resume Next

      Dim RegObj
      Dim SysEnv

      Set RegObj = WScript.CreateObject("WScript.Shell")

      '***** Specify the DSN parameters *****
      Dim DataSiteAbbrev(8)
    DataSiteAbbrev(1) = "PA"
    DataSiteAbbrev(2) = "SI"
    DataSiteAbbrev(3) = "MA"
    DataSiteAbbrev(4) = "KY"
    DataSiteAbbrev(5) = "OH"
    DataSiteAbbrev(6) = "IN"
    DataSiteAbbrev(7) = "MI"
    DataSiteAbbrev(8) = "IL"
    i = 1
do while i <= 8
    DataSourceName = "BranchRecon" & DataSiteAbbrev(i)
    DatabaseName = "CentralProof" & DataSiteAbbrev(i)
    Description = "Central Proof Db connection"
    DefaultDatabase = "CentralProof" & datasiteabbrev(i)
    LastUser = "xrlv23h"
    Server = "ohclesql1001"

       'if you use SQL Server the driver name would be "SQL Server"
    DriverName = "SQL Server"

       'Set this to True if Windows Authentication is used
       'else set to False or comment out
    WindowsAuthentication = True

      'point to DSN in registry

   ' Open the DSN key and check for Server entry
        'lResult = RegObj.RegRead (REG_KEY_PATH & "\Server")
      'I want to check for the database, not the server
      lResult = RegObj.RegRead (REG_KEY_PATH & "\Database")

      'if lResult is nothing, DSN does not exist; create it
        if lResult = "" then

        'get os version through WSCript Enviroment object
         Set SysEnv = RegObj.Environment("SYSTEM")
        OSVer = UCase(SysEnv("OS"))

         'check which os is running so correct driver path can be set
        Select Case OSVer
          Case "WINDOWS_NT"
              DrvrPath = "C:\WinNT\System32"
          Case Else
              DrvrPath = "C:\Windows\System"
        End Select

        'create entries in registry
        RegObj.RegWrite REG_KEY_PATH & "\DataBase",DatabaseName,"REG_SZ"
        RegObj.RegWrite REG_KEY_PATH & "\Description",Description,"REG_SZ"
        RegObj.RegWrite REG_KEY_PATH & "\LastUser",LastUser,"REG_SZ"
        RegObj.RegWrite REG_KEY_PATH & "\Server",Server,"REG_SZ"
        RegObj.RegWrite REG_KEY_PATH & "\Driver",DrvrPath,"REG_SZ"
        RegObj.RegWrite REG_KEY_PATH & "\Database",DefaultDatabase,"REG_SZ"

        'if WindowsAuthentication set to True,
        'a trusted connection entry is added to registry
        'else, SQL Authentication is used.
        if WindowsAuthentication = True then
          RegObj.RegWrite REG_KEY_PATH & "\Trusted_Connection","Yes","REG_SZ"
        end if

        'point to data sources key
        REG_KEY_PATH = "HKLM\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources\" & DataSourceName

        'and add the name of the new dsn and the driver to use with it
        RegObj.RegWrite REG_KEY_PATH,DriverName,"REG_SZ"

        MsgBox DataSourceName & " DSN Created!"

            MsgBox DataSourceName & " DSN already exists!"
      end if
      i = i + 1
      Set RegObj = Nothing
      Set SysEnv = Nothing

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ambullockAuthor Commented:
thanks, but using the first example I get the following error "Invalid call or procedure"  at this line :

 strDbName = Right(strDbName, (Len(strDbName)) - InStr(1, strDbName, "Database", vbTextCompare) - Len("Database"))
    strDbName = Left(strDbName, Len(DatabaseNameFormat))

any ideas.  Alan
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Gustav BrockCIOCommented:
Watch here for a method using DAO:

That is as for attaching a new table.
For reattaching existing tables, this is a route:

  Dim dbs As DAO.Database
  Dim tdf As DAO.TableDef
  Set dbs = CurrentDb
  Set tdf = dbs.TableDefs("dbo_sometable")
  With tdf
    .Connect = "ODBC;DRIVER=SQL Server;SERVER=SERVER1;APP=Microsoft Office 2003;WSID=xxxx;DATABASE=VIPSQL;Trusted_Connection=Yes;"
  End With
  Set tdf = Nothing
  Set dbs = Nothing

No line break, of course, and you'll need to modify the connect string to your environment.
This is for one table. For all attached tables do something like:

  For Each tdf in dbs.Tabledefs
    With tdf
      If Left(.SourceTableName, 4) = "dbo." Then
        <refresh link>
      End If
    End With

You don't really need the code that you errored on, it sets the default database to be the one that the current first table is point to .....

You can change cnDBName = "Your Database"

instead of all the hooplah.  

When in doubt, I use for resources on how to use a connection.
The OBDC drivers will still need to be installed on each machine even for a DNS-less connection.

I create pass-through queries whenever I want to create dns-less connections.  When a database or form opens, you could have the query deleted and recreated as needed.  Here is an example of a pass-through query I used to connect to a remote MySQL DB

Function Make_Qry_Pass_Tbl_CFF_BatchID()

Dim dbsCurrent As Database
    Dim qdfPassThrough As QueryDef
    Dim qdfLocal As QueryDef
    ' Open a database from which QueryDef objects can be created.
    Set dbsCurrent = CurrentDb
    ' Create a pass-through query to retrieve data from a Microsoft SQL Server database.
    Set qdfPassThrough = dbsCurrent.CreateQueryDef("Qry_Pass_Tbl_CFF_BatchID")
    qdfPassThrough.Connect = "ODBC;Driver={MySQL ODBC 3.51 Driver};DATABASE=iatest;;UID=userid;PASSWORD=password;PORT=3306;OPTION=3;STMT=;"
    qdfPassThrough.SQL = "Select * From Tbl_CFF_BatchID"
    qdfPassThrough.ReturnsRecords = True
End Function

All you would need to do is change the driver to something like DRIVER=SQL Server, and create an macro called "autoexec"  in the macro choose the "runcode" and enter the function name.  Autoexec will automatically execute on Access Startup.

Gustav BrockCIOCommented:
At least one solution is provided ...

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.