Prompt 4 SQLS7 Password

[figler]

I have an Access databases with tables linked (through a system DSN) to SQLS7 database tables. I am trying to automate some tasks, but run into trouble when I am prompted for my user name and password for the sqls db. Is there a way to get around this? Can the DSN store my password too? Thanks. -Brad

 

[Sawatzky]

dear brad,

you could perhaps use the Windows-Authentification of the SQL-Server.


regards Astrid

 

[Guest_imported]

Je réponds en français si 'ai bien compris le mot de passe apparaît à chaque connection. Pour éviter cela il faut quant on lie les tables pour la première fois avec ODBC cocher la case enregistrer le mot de passe et ensuite il n'y a plus de problème sql ne demande plus le mot de passe.Attention j'ai bien dit la première fois!!!
Sinon il faut supprimer les tables liées et les recharger de SQL

J'espère avoir pu vous aider

 

[petersdaniel]

Please identify the version of Access you are working on. Depending on the version, you can set up the DSN progrmatically to pass the value. For example using the ADO Open method...


Dim cnn As New ADODB.Connection

cnn.Provider = "Microsoft.Jet.OLEDB.4.0;"
cnn.Properties("Jet OLEDB:System database&quot = _
"C:\Program Files\Microsoft Office\Office\SYSTEM.MDW"

cnn.Open "Data Source=.\NorthWind.mdb;User Id=Victoria;Password=Secret;"
cnn.Close

 

[rbowes]

I'm not sure if this helps but what I do is have a local table that holds the names of the SQL Server table Names (as they appear in Access) and their SourceName (table names as the appear in SQL Server) usually they look like this:

tableName: dbo_LookupCodes; SourceName: LookupCodes

On my application AutoExec, I delete all links then run a recordset on the local table and loop through it to reestablish each linked table's ODBC connection string.

Do until rec.EOF
strTableName = rec.Fields(LinkedTableName).Value
Set tdfNew = Currentdb.CreateTableDef(strTableName)
tdfNew.SourceTableName = rec.Fields(SourceName).Value
tdfNew.Connect = cODBCPATH
Currentdb.TableDefs.Append tdfNew
rec.MoveNext
Loop

My cODBCPath is a constant:
Public Const cODBC as String = "ODBC;DSN=MyDSNName;Description=MyDB;UID=MyUserID;PWD=PassWord;DATABASE=MyDB"
(You can copy it directly from a pass-through query's connection string.)

This works for views as well.

One thing to remember is that the connection string for your queries and tables is different from an ADO connection object's connection string. If you use the "ODBC" connection on an ADO Connection object, it will not open.

Hope that helps.

Bob