DataBase - Connecting to SQL Server

Asked By Jim Devenish

22-Nov-10 12:08 PM

I connect my Access database to a SQL Server in two different ways:

1  with ADO such as:
Dim theConnectionString

theConnectionString = Driver=SQL Server; Server=Server
\TRUTIMEV3;Database=Intelligent;User ID=xxx;Password=xxx;

Dim rec As ADODB.Recordset
Set rec = New ADODB.Recordset

Dim theSQLString
theSQLString = "Select * From [Cost Centres] Where CC_Code = '" &
theJobDigits & "'"
rec.Open theSQLString, theConnectionString, adOpenStatic,
adLockOptimistic

2  with DAO such as:
Dim theViewName
theViewName = "aView"

Dim aTable As DAO.TableDef
Set aTable = CurrentDb.CreateTableDef(theViewName)
aTable.Connect = ODBC;APP=Microsoft Data Access
Components;Trusted_Connection=Yes;Driver=SQL Server; Server=Server
\TRUTIMEV3;Database=Intelligent;User ID=xxx;Password=xxx;

aTable.SourceTableName = theViewName
CurrentDb.TableDefs.Append aTable
CurrentDb.TableDefs.Refresh


Both versions have been working satisfactorily for a number of years.
Last week the Server needed to be rebuilt and the suppliers of the SQL
database introduced  a User ID and Password that had not been used
before. Other than that I am not aware of any changes to the system.

Now the first version (ADO) still works but the other (DAO) does not.
It fails when appending the table, indicating a connection failure:

Connection Failed
SQLState: 01500
SQL Server Error: 0
[Microsoft][ODBC SQL Server Driver] Invalid connection string
attribute

Connection failed:
SQLState: 08004
SQL Server Error: 4060
Server rejected the connection; Access to selected database has been
denied

Any help with getting the connection string correct would be welcome.

22-Nov-10 02:02 PM

used to establish the connection. Since the database requires a user
name and password, you should remove the Trusted_connection attribute or
set it to No. You should use the ODBC names for the user and password
attributes: UID and PWD instead of "User ID" and "Password". Also,
should not that connection string be quoted? Like this:

aTable.Connect = "ODBC;APP=Microsoft Data Access" & _

PS. I know you say it is been "working for years", but it is really not a
good idea to use implicit ADO connections. The line:
rec.Open theSQLString, theConnectionString ...
causes ADO to open a connection object behind the scenes (implicitly),
an object over which you have no control. If you are opening multiple
recordsets in your application, the result could well be that a separate
open connection to the database will exist for each recordset you have
opened, each of which consumes resources both on the client machine as
well as on the database server. Eventually, the point could be reached
where your database server refuses new connections. Best practice is to
explicitly create a connection object and use that to open your
recordsets:

dim cn as adodb.connection
set cn=new adodb.connection
cn.open "your connection string"
...
rec.Open theSQLString, cn, ...

Close the connection object when finished using it so that it goes back
into the pool of connections that can be re-used.

Also, you should get away from using ODBC with ADO as it creates an
extra software layer. Use the native OLE DB provider for SQL that is
relevant for the version of sql server you are connecting to. You can
find the relevant OLE DB connection strings here:
http://www.connectionstrings.com/




--
HTH,
Bob Barrows

22-Nov-10 03:39 PM

=3D '" &
r
ings.com/

Many thanks Bob.  It is now working.  I do not use/create connection
strings very often and so am not sufficiently familiar with all the
syntax.  I had not appreciated that I needed UID and PWD instead of
User ID and Password for an ODBC connection.  That did the trick.

Prior to last week there was no password protection on the SQL
database and so I had not needed these parameters before.

Thanks for the advice on the connection object.

The missing quotes were caused by a bit of copy and paste.  They were
in my code!