Password connection to ODBC database

[Bob Parr]

I am working on an access database that will pull some data from a networked
SQL server. I have most of the cod written and it works but when I first
access the SQL server (as an ODBC linked table) I am propted for the
password. How can I pass the password in VB? If it matters I am using DAO
not ADO.

Bob

 

[name]

Ms because of SUN's verdict on JAVA

decided to abolish many products.
See the latest.

Your configuration may be among them.

 

[Bob Barrows]

I am working on an access database that will pull some data from a
networked SQL server. I have most of the cod written and it works
but when I first access the SQL server (as an ODBC linked table) I am
propted for the password. How can I pass the password in VB? If it
matters I am using DAO not ADO.

Well stop doing that!

Whatever made you decide to use the antiquated DAO to access a SQL Server
database??? It never did that well even before ADO was in existence.

Maybe you're afraid of a long learning process. Well, you should not be: ADO
is just as easy to use as DAO.

IIRC, there is a way to pass the user credentials to a linked ODBC table,
but it is convoluted. Let me do a quick search of the KB - I haven't had to
look at that DAO stuff in years. (I've got to tell you that your chances of
getting help with this are limited due to the number of people who have
stopped using DAO).

OK, here it is. Enjoy
http://support.microsoft.com/default.aspx?scid=kb;EN-US;177594

Bob Barrows

 

[Bob Parr]

Okay this may be a dumb question, but can ADO and DAO be used in the same
DB, form or module?

Bob

 

[Bob Barrows]

Okay this may be a dumb question, but can ADO and DAO be used in the
same DB, form or module?

Yes, but you need to be aware that these libraries contain objects and
collections with the same names: Recordest, Connection, etc. This requires
you to fully qualify them when being used, otherwise the compiler will
sometimes choose the wrong library. For example:
Dim rs_dao AS DAO.Recordset
Dim rs_ADO AS ADODB.Recordset

If you just used this:
Dim rs_DAO AS Recordset

and then tried to set one of the DAO recordset properties or use one of the
DAO recordset methods, the compiler might try to find the property or method
in the ADO library, resulting in an error if the property or method does not
exist in ADO, or if the ADO property or method required different settings
or arguments than the similarly named DAO property or method.

HTH,
Bob Barrows

 

[Joe Fallon]

This is the key: dbAttachSavePWD
==================================================================
Also, DAO is fine to use. It is faster and more complete than ADO. Many
developers never switched.
(ADO is dead now that .Net is here. Now you have to learn ADO.Net.)
==================================================================

I use this procedure to re-create links to SQL Server.
(This eliminates the need to re-name all the tables to strip out dbo_ and it
allows you to point to different versions of the same database easily.)
There is a local Access table (tblODBCTables) that contains the table names
I want to link to on the Server.
Note: the source table name needs the dbo. prefix which is in the code. The
linked table name usualy omits this. .

Public Sub LinkSQLServerTables(strDSN As String, strDatabase)
On Error GoTo Err_LinkSQLServerTables

Dim dbs As Database, rs As Recordset, tdfAccess As TableDef
Dim dbsODBC As Database, strConnect As String

If strDSN = "" Then
MsgBox "You must supply a DSN in order to link tables."
Exit Sub
Else
strConnect = "ODBC;DSN=" & strDSN & ";UID=User;PWD=password;DATABASE=" &
strDatabase & ";"
End If

SysCmd acSysCmdSetStatus, "Connecting to SQL Server..."

Call DeleteODBCTableNames

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("tblODBCTables")
Set dbsODBC = OpenDatabase("", False, False, strConnect)

Do While Not rs.EOF
Set tdfAccess = dbs.CreateTableDef(rs![LinkTablename], dbAttachSavePWD)
tdfAccess.Connect = dbsODBC.Connect
tdfAccess.SourceTableName = dbsODBC.TableDefs("dbo." &
rs![LinkTablename]).Name
dbs.TableDefs.Append tdfAccess
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set dbsODBC = Nothing
Set dbs = Nothing

Exit_LinkSQLServerTables:
SysCmd acSysCmdClearStatus
Exit Sub

Err_LinkSQLServerTables:
MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source
& Chr(13) & Err.Description)
Resume Exit_LinkSQLServerTables

End Sub


'This procedure deletes all linked ODBC table names in an mdb.
Public Sub DeleteODBCTableNames()
On Error GoTo Err_DeleteODBCTableNames

Dim dbs As Database, tdf As TableDef, I As Integer
Set dbs = CurrentDb
For I = dbs.TableDefs.Count - 1 To 0 Step -1
Set tdf = dbs.TableDefs(I)
If (tdf.Attributes And dbAttachedODBC) Then
dbs.TableDefs.Delete (tdf.Name)
End If
Next I

dbs.Close
Set dbs = Nothing

Exit_DeleteODBCTableNames:
Exit Sub

Err_DeleteODBCTableNames:
MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source
& Chr(13) & Err.Description)
Resume Exit_DeleteODBCTableNames

End Sub

 

[Bob Barrows]

This is the key: dbAttachSavePWD
==================================================================
Also, DAO is fine to use. It is faster and more complete than ADO.

Only when going against a Jet database. It is a very robust model for
working with Jet. ADO is deficient here.

However, I stand by my statement concerning its use with ODBC databases. DAO
did not have the feature set to efficiently connect to server-based rdbms's.
ADO does.

Also, DAO is single-threaded so it should not be used in server-side code
such as ASP (I realize this is irrelevant to the OP's question).

My point about getting help still stands. It took 2 days for a response with
DAO help to be posted. A question about using ADO would have been answered
within hours.

Bob Barrows

 

[McKirahan]

Only when going against a Jet database. It is a very robust model for
working with Jet. ADO is deficient here.

However, I stand by my statement concerning its use with ODBC databases. DAO
did not have the feature set to efficiently connect to server-based rdbms's.
ADO does.

Also, DAO is single-threaded so it should not be used in server-side code
such as ASP (I realize this is irrelevant to the OP's question).

My point about getting help still stands. It took 2 days for a response with
DAO help to be posted. A question about using ADO would have been answered
within hours.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Bob,

I had hoped your statement was accurate:
"A question about using ADO would have been answered within hours.".

However, I posted "ODBC Company, Username, and Password" two days ago on a
similar subject and have had no response yet. Any chance you can advise?
Thanks.

 

[Bob Barrows]

I had hoped your statement was accurate:
"A question about using ADO would have been answered within hours.".

However, I posted "ODBC Company, Username, and Password" two days ago
on a similar subject and have had no response yet. Any chance you
can advise? Thanks.

Sorry - I have no experience with the database you were asking about. I know
of no database system that asks for Company in its connection info.

Bob Barrows

 

[McKirahan]

Sorry - I have no experience with the database you were asking about. I know
of no database system that asks for Company in its connection info.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Would you have a solution if "Company" were not involved?

 

[Bob Barrows]

Would you have a solution if "Company" were not involved?

Let me look again in the original thread. I did not give it much thought at
the time and the details of your question escape me. I will reply tomorrow
in the original thread if I have anything to offer.

Bob Barrows