Posted by Bill Ramsey on 10/01/92 11:38

On 27 Jan 2006 14:08:49 -0800, "Jens" <Jens@sqlserver2005.de> wrote:

>How did you link that table to Access ? I you used the ODBC
>administrator for Creating a DSN and using this Access, make sure that
>the password is setup. If so that should be no problem connecting to
>the database, unless you use the right password. So try retyping in the
>ODBC Administrator the *RIGHT* password.
>
>HTH, jens Suessmeyer,

There are ways to re-connect to your tables in VBA code.

One option is to re-link in code to tables one by one, and call a
function with a list of table names. (Note - requires hard coding the
user name and password - see below on security concerns.)

Function LinkTable(tblName As String) As Boolean
'links or re-links 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()
'if the link exists, delete it
Set tdf = db.TableDefs(tblName)
If Err.number = 0 Then
'table link exists
db.TableDefs.Delete tblName
db.TableDefs.Refresh
Else
Err.Clear
End If

'create the link
Set tdf = db.CreateTableDef(tblName)
tdf.Connect = "ODBC;Database=" & strSQLDB & ";DSN=" & strDSN &
";uid=username;pwd=password;"
tdf.SourceTableName = tblName
db.TableDefs.Append tdf
If Err.number = 0 Then
LinkTable = True
Debug.Print "Table " & tblName & " linked"
Else
LinkTable = False
End If

db.Close
Set db = Nothing
End Function

Note: You are hard coding the user name and password into the connect
string. This option actually can be fairly secure **provided** that
you have the modules locked down via permissions.

Also note - MS-Access is known for it's lack of real security. You can
enhance it by proper network security. Another option is creating a
**MDE file** -- where the modules will be in a binary format and thus
more secure.

Another solution is to connect as a specific user when the database
starts up (Note - this is not secure because you have to code the
password into a connect string . . . use this only in situations where
you can control access to the database through network security). You
can create a specific MS-Access "user" for the connection string with
just enough permissions to accomplish what you need.

Example:
In a form which opens upon startup include a call to a function like
this:
Call PreConnectWithODBCParameters(strSQLDB, strDSN, "MSAccess_Name",
"password")

In a module:
Sub PreConnectWithODBCParameters(sSQLDatabase As String, sDSN As
String, sUserName As String, sPassword As String)
Dim dbLocal As Database
Dim strConnectInfo As String
Set dbLocal = DBEngine.Workspaces(0).Databases(0)
strConnectInfo = "ODBC;database=" & sSQLDatabase & ";dsn=" & sDSN
& ";" & _
"uid=" & sUserName & ";" & _
"pwd=" & sPassword & ";"
Set dbLocal = OpenDatabase("", False, False, strConnectInfo)
Set dbLocal = Nothing
End Sub


Bill

[Back to original message]