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