[AccessD] SQL Newbie Questions

MartyConnelly martyconnelly at shaw.ca
Mon Mar 19 11:51:47 CDT 2007


Just something to watch out for with SQL2005 and Express
with new ODBC and OLEDB providers. Might have deployment
problems with DSN-less connections.

 From MSDN:
"SQL Native Client is a data access technology that is new to
Microsoft SQL Server 2005, and it is a stand-alone data access
application programming interface (API) that is used for both OLE
DB and ODBC. It combines the SQL OLE DB provider and the SQL ODBC
driver into one native dynamic-link library (DLL) while also
providing new functionality above and beyond that supplied by the
Microsoft Data Access Components (MDAC). SQL Native Client can be
used to create new applications or enhance existing applications
that need to take advantage of new SQL Server 2005 features such
as multiple active result sets (MARS), user-defined data types
(UDT), query notifications, snapshot isolation, and XML data type
support."

http://msdn2.microsoft.com/en-us/library/bb188204.aspx


Setting Connection Properties
Many Office Access applications include code for relinking tables by 
resetting their Connect property values. When you are working with ODBC
links, these connect strings can be based on defined data sources, 
called DSNs (defined data-source names), created and stored by
Windows in files or in the registry.

The Office Access graphical tools for creating ODBC-linked tables and 
pass-through queries require you to select or create a named ODBC
DSN when specifying a connection. But this is not required. Instead, use 
code to set these properties using "DSN-less" ODBC connect strings.
One strategy is to use a startup login form that collects login data 
from the user and that constructs and caches both an ODBC connect string
and an OLE DB connection string to use in ADO code. The following 
example code creates connection strings based on selections made in a
login form, using the Microsoft SQL Native Client OLE DB Provider and 
ODBC driver that were released with SQL Server 2005.

  Select Case Me.optgrpAuthentication
    Case 1      ' NT authentication
      mstrOLEDBConnection = "Provider=SQLNCLI;" & _
        "Data Source=" & Me.txtServer & ";" & _
        "Initial Catalog=" & Me.txtDatabase & ";" & _
        "Integrated Security=SSPI"

      mstrODBCConnect = "ODBC;Driver={SQL Native Client};" & _
        "Server=" & Me.txtServer & ";" & _
        "Database=" & Me.txtDatabase & ";" & _
        "Trusted_Connection=Yes"

    Case 2      ' SQL server authentication
      mstrOLEDBConnection = "Provider=SQLNCLI;" & _
        "Data Source=" & Me.txtServer & ";" & _
        "Initial Catalog=" & Me.txtDatabase & ";" & _
        "User ID=" & Me.txtUser & _
        ";Password=" & Me.txtPwd

      mstrODBCConnect = "ODBC;Driver={SQL Native Client};" & _
        "Server=" & Me.txtServer & ";" & _
        "Database=" & Me.txtDatabase & ";" & _
        "UID=" & Me.txtUser & _
        ";PWD=" & Me.txtPwd
  End Select

The following example cycles through all of the tables in a database and 
resets the connection properties for all the ODBC-linked tables,
assuming that they all are linked to tables or views in the same 
database. The code sets three properties for each linked table: the name 
of the
link, the name of the source table (or view), and the connect string.

  Dim fLink As Boolean
  Dim tdf As DAO.TableDef
  Dim db as DAO.Database
  Set db = CurrentDb
  For Each tdf In db.TableDefs
    With tdf
      ' Only process linked ODBC tables
        If .Attributes = dbAttachedODBC Then
            fLink = LinkODBCTable( _
              strLinkName:=.Name, _
              strConnect:= mstrODBCConnect, _
              strSourceTableName:=.SourceTableName)
      End If
    End With
  Next tdf

Private Function LinkODBCTable( _
    strLinkName As String, _
    strConnect As String, _
    strSourceTableName As String) As Boolean

    ' Links or relinks a single table.
    ' Returns True or False based on Err value.

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef

  On Error Resume Next
    Set db = CurrentDb

    ' Check to see if the table link already exists;
    ' if so, delete it
    Set tdf = db.TableDefs(strLinkName)
    If Err.Number = 0 Then
        db.TableDefs.Delete strLinkName
        db.TableDefs.Refresh
    Else
        ' Ignore error and reset
        Err.Number = 0
    End If

    Set tdf = db.CreateTableDef(strLinkName)
    tdf.Connect = strConnect
    tdf.SourceTableName = strTableName
    db.TableDefs.Append tdf
    LinkTableDAO = (Err = 0)
End Function

It is generally best to use DAO.CreateTableDef for linking tables, 
instead of using DoCmd.TransferDatabase, because you have more control
over the properties of the link. Also, if you need to create a link to a 
table or view that does not have a unique index (knowing it will 
therefore not
be updatable), using TransferDatabase will cause a dialog box to open 
asking the user to specify a unique index. Using CreateTableDef
doesn't cause this side effect.

One technique for making connection strings available throughout the 
lifetime of your applications is to expose them as public properties of the
login form and then hide the login form instead of closing it. When the 
application exits, the form closes and no credentials are persisted.
If you are using SQL Server Authentication and storing user names and 
passwords with your links, it is safest to delete the links when the
application exits. When the application starts up, code in your 
application can delete any existing links (in case there was an abnormal
shutdown) and then create new links, retrieving SQL Server table names 
from a local table. For pass-through queries, you can delete the
connection data without having to delete the entire query. Here's an 
example of code that cycles through all SQL pass-through queries to do
this. At startup, you would need to reset the connect property for each 
of the queries based on credentials the user enters in a login form.

    Dim qdf As DAO.QueryDef
    Dim db As DAO.Database
    Set db = CurrentDb
    For Each qdf In db.QueryDefs
      If qdf.Type = dbQSQLPassThrough Then
          qdf.Connect = "ODBC;"
      End If
    Next qdf

Rocky Smolin at Beach Access Software wrote:

>Randall, et. Al.:
>
>I finally got some good MS tech support on the phone, guy named...Martin...I
>think...could barely understand him for the accent, said he was in Belfast.
>
>Anyway, guy's a genius with this SQL stuff.  Walked me through the download
>of the manager, got my DNS created, and am in the process of linking the
>tables from Small Business Accounting 2007.  There are about 1000 tables,
>most prompt for a PK.  Should take about two days to link them all.
>
>Thanks for the help.
>
>I'll have more questions soon.
>
>BTW that SQL Server Management Studio Express is terrific.
>
>Best to all,
>
>Rocky
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of RANDALL R ANTHONY
>Sent: Friday, March 16, 2007 1:08 PM
>To: 'Access Developers discussion and problem solving'
>Subject: Re: [AccessD] SQL Newbie Questions
>
>If you have SQL2K, you would have MS SQL Server and under that Enterprise
>Manager.
>If you have SQL2K5, you should have  MS SQL Server 2005, under that,  SQL
>Server Management Studio or  SQL Server Studio Express.
>If you don't have either, get Express here:
>http://www.microsoft.com/downloads/details.aspx?FamilyId=C243A5AE-4BD1-4E3D-
>94B8-5A0F62BF7796&DisplayLang=en
>
>A better question maybe, what'cha got?  Default file extensions for SQL is
>usually .mdf and .ldf.  Your earlier email indicates .db and .lg.   Have you
>tried just opening the sampleproduct db with Access?  
>
>  
>
>>>>"Rocky Smolin at Beach Access Software" <rockysmolin at bchacc.com> 
>>>>3/16/2007 3:35 PM >>>
>>>>        
>>>>
>In the Program list I have Microsoft SQL Server 2005 with a sub menu of
>Configuration Tools and a sub-sub menu with SQL Server Configuration
>Manager.
>
>But no SQL Servers > SQL Server Group.
>
>Another question:  suppose I get this link going.  Every time I send it to
>someone who has Microsoft Small business Accounting will I have a problem
>getting them connected?
>
>Rocky
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada




More information about the AccessD mailing list