On 27 Jan 2006 14:08:49 -0800, "Jens" <Je**@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
