Rocky Smolin at Beach Access Software
rockysmolin at bchacc.com
Mon Mar 19 14:09:13 CDT 2007
It was native client that eventually got me connected.
Rocky
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of MartyConnelly
Sent: Monday, March 19, 2007 9:52 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] SQL Newbie Questions
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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.13/726 - Release Date: 3/18/2007
3:34 PM