Re: connection strings question
From: Val Mazur (group51a_at_hotmail.com)
Date: 10/30/04
- Next message: Al Reid: "Re: How to check an empty recordset?"
- Previous message: Val Mazur: "Re: How to check an empty recordset?"
- In reply to: Dale: "connection strings question"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 29 Oct 2004 21:20:52 -0400
Hi,
Couple of issues here.
1. First of all ADO does not work with ODBC drivers directly and in a case
when you specify ODBC driver in your connection string, ADO will place one
more layer, between this driver and ADO library itself. This layer is OLEDB
for ODBC Provider, because ADO works ONLY with OLEDB providers. This
additional layer will put some limitations on functionality and will
definitely will work slower. Preferable way is to use native OLEDB provider,
when you connect to the database.
In a case of SQL Server, your connection string would look like
"Provider=sqloledb;Data Source=BACKOFFICE_DEMO\RETAIL;Initial
Catalog=POSBdat;User Id=MyUsernameHere;Password=MyPasswordHere"
2. Never declare and instantiate ADO variable in a declaration part. Like
in your case with the connection (Public cnn As New ADODB.Connection) it
would lead to the connection and memory leaks. Separate declaration and
instantiation part. You could do instantiation in some code, when you need
to open connection
Public cnn As ADODB.Connection
Set cnn = New ADODB.Connection
3. If you are trying to open connection once and keep it during life of the
application, then it is not a good idea. First of all there is no reason to
keep it live and lock server resources if application does not uses it 100%
of the time. Open connection, get data and close connection as soon as you
finished your operation. It will release resources, which SQL Server
requires to maintain to keep connection life. Since ADO uses pooling for
OLEDB connections, then, if you connect to the same database with same
userid and password, then provider will re-open connection pretty fast from
the connection pool and your application will not experience performance
impact. Another potential issue with keeping connection alive during long
time is that actual connection could be dropped for some reason (network
failure or something else) and you would need to open it again. Short
periods between opening and closing connection will reduce probability of
this situation.
4. NEVER hardcode userid and password in your code. It will lead to the
information disclose vulnerability in your code.
5. NEVER use sa user with blank password. It is just an open door and an
invitation for hackers. Actually using of sa is not a good idea at all
unless you have good reason for it. Always try to open connection with the
user, which has minimum privileges to do ONLY what this user suppose to do.
--
Val Mazur
Microsoft MVP
"Dale" <dale0610@hotmail.com> wrote in message
news:emye$0QvEHA.2288@TK2MSFTNGP09.phx.gbl...
> Hi all,
>
> I am starting to use connection strings a bit more now in coding and am a
> little confused. If anyone can clarify some of this for me, it'd be
> great. Here's what I'm doing:
>
> If I have some code like this:
>
>
> Public cnn As New ADODB.Connection
>
> Public rsProducts As ADODB.Recordset
>
>
> Set cnn = New ADODB.Connection
>
> cnn.Open sConnectionString
>
> Set rsProducts = New ADODB.Recordset
>
> sSelect = "SELECT * FROM Products ORDER BY " & PRODUCTS_UPC
> rsProducts.Open sSelect, cnn, adOpenStatic, adLockOptimistic
>
>
> with this connection string:
>
> DRIVER={SQL
> Server};SERVER=BACKOFFICE_DEMO\RETAIL;DATABASE=POSBdat;UID=sa;PWD=;
>
> things are fine, I can call the methods from rsProducts and it all works.
> However if I use this connection string:
>
>
> ODBC;DRIVER={SQL
> Server};SERVER=BACKOFFICE_DEMO\RETAIL;DATABASE=POSBdat;UID=sa;PWD=;
>
> I get this error:
>
> Run-time error '3704':
> Operation is not allowed when the object is closed.
>
>
> As an aside, I have a different program accessing the same database and if
> I have code like the following:
>
> Dim appAccess As New access.Application 'declare this in your declaration
> seciton
>
>
> appAccess.OpenCurrentDatabase (strLabelDBPathname)
> Set tdfLink = appAccess.CurrentDb.CreateTableDef(strTableName,
> dbAttachSavePWD, strTableName, sConnectionString)
> appAccess.CurrentDb.TableDefs.Append tdfLink
> appAccess.CloseCurrentDatabase
>
> I get this error when I have the first connection string:
>
> Could not find installable ISAM
>
> but the code works fine with the second connection string.
>
> I guess this is a long winded way of asking, what's happening with the
> "ODBD;" part of the connection string?
>
> Thanks for your help.
- Next message: Al Reid: "Re: How to check an empty recordset?"
- Previous message: Val Mazur: "Re: How to check an empty recordset?"
- In reply to: Dale: "connection strings question"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
