Hi James
Could you elaborate a bit on this please?
> Unfortunately, none of these worked. I am attempting to link all tables
> at once, ..
Does "at once" mean at launch of your app? Would that be with the
current User ID or, perhaps (if you store this), the last successfully
logged in User ID?
> .. then make it possible for the user to switch to another ID and
> relink with those permissions without shutting down Access and
> restarting.
Are you using Access security? If so I think that is right; if not, it
should not be needed to relaunch the app - a relink should be possible
whenever you wish.
> However, after more testing I have been able to get it to bring up the
> MaxDB Login only once and that will work. ..
Good.
> Unfortuantely Access still requires a shutdown to break the ODBC
> links, but that is an Access issue not with the ODBC client, ..
What ODBC links are you referring to? Do you mean that if a relink
with a new User ID is carried out, the connection with the old User ID
still exists?
> and I still can't pass the password to the MaxDB client, but
> I can live with that for now.
I don't have a MaxDB running currently so others may chime in here.
But what do you get if you do a Debug.Print of the Connect property of
your connection or of a table?
> Just in case anyone is curious this is what the code looks like now.
OK. One comment on this: why should a TableDef not exist in your app?
How can it be lost? Are you allowing users direct access to Tabledefs
with permission to delete a TableDef?
/gustav
> Option Explicit
> '***************************************************************
> 'The DoesTblExist function validates the existence of a TableDef
> 'object in the current database. The result determines if an
> 'object should be appended or its Connect property refreshed.
> '***************************************************************
> Function DoesTblExist(strTblName As String) As Boolean
> On Error Resume Next
> Dim db As DAO.Database, tbl As DAO.TableDef
> Set db = CurrentDb
> Set tbl = db.TableDefs(strTblName)
> If Err.Number = 3265 Then ' Item not found.
> DoesTblExist = False
> Exit Function
> End If
> DoesTblExist = True
> End Function
> Function CreateODBCLinkedTables() As Boolean
> On Error GoTo CreateODBCLinkedTables_Err
> Dim strTblName As String, strConn As String
> Dim db As DAO.Database, rs As DAO.Recordset, tbl As DAO.TableDef
> Dim strDSN As String
> Dim PWD As Variant, UID As String
> ' ---------------------------------------------
> ' Register ODBC database(s).
> ' ---------------------------------------------
> Set db = CurrentDb
> Set rs = db.OpenRecordset("Select * From tblODBCDataSources Order By DSN")
> With rs
> While Not .EOF
> If strDSN <> rs("DSN") Then
> DBEngine.RegisterDatabase rs("DSN"), _
> "MaxDB", _
> True, _
> "Description=VSS - " & rs("DataBase") & _
> Chr(13) & "Server=" & rs("Server") & _
> Chr(13) & "Database=" & rs("DataBase")
> End If
> strDSN = rs("DSN")
> ' ---------------------------------------------
> ' Link table.
> ' ---------------------------------------------
> UID = UCase(Forms!Login.UserID)
> 'PWD = Forms!Login.Password
> PWD = ""
> strTblName = rs("LocalTableName")
> strConn = "ODBC;"
> strConn = strConn & "DSN=" & rs("DSN") & ";"
> 'strConn = strConn & "APP=Microsoft Access;"
> 'strConn = strConn & "DATABASE=" & rs("DataBase") & ";"
> strConn = strConn & "UID=" & UID & ";"
> strConn = strConn & "PWD=" & PWD & ";"
> strConn = strConn & "SERVERDB=" & rs("DataBase") & ";"
> strConn = strConn & "SERVERNODE=" & rs("Server") & ";"
> 'strConn = strConn & "TABLE=" & rs("ODBCTableName")
> If (DoesTblExist(strTblName) = False) Then
> 'Set tbl = db.CreateTableDef(strTblName, , rs("ODBCTableName"), strConn)
> 'db.TableDefs.Append tbl
> Set tbl = db.CreateTableDef(strTblName)
> tbl.Connect = strConn
> tbl.SourceTableName = rs("ODBCTableName")
> db.TableDefs.Append tbl
> Else
> Set tbl = db.TableDefs(strTblName)
> tbl.Connect = strConn
> tbl.RefreshLink
> End If
> rs.MoveNext
> Wend
> End With
> CreateODBCLinkedTables = True
> MsgBox "Refreshed ODBC Data Sources", vbInformation
> CreateODBCLinkedTables_End:
> Exit Function
> CreateODBCLinkedTables_Err:
> Dim errMsg As String
> errMsg = Err.Number & Err.Description
> MsgBox errMsg, vbCritical, "BSCM"
> Resume CreateODBCLinkedTables_End
> End Function
> This allows Access to Create the ODBC connection and link the tables if
> they don't exist, then if they do just relink the tables.
> Thanks for the suggestions though. And meanwhile I'll go searching Access
> sites to see if there is a way to break the ODBC connection without
> closing Access, and see if there is any info on how to get rid of the "".
> Gustav Brock <[EMAIL PROTECTED]>
> 06/04/2004 01:51 PM
> Please respond to Gustav Brock
> To: [EMAIL PROTECTED]
> cc:
> Subject: Re: MaxDB, ODBC, and ADO question
> Hi James
> First, this isn't ADO but DAO.
> Next, though I don't think it makes much of a difference, PWD is
> dimmed as a Variant:
> Dim PWD, UID As String
> You need:
> Dim PWD As String, UID As String
> Also, doublecheck that the DSN keyword for password is PWD. It is so
> for SQL Server but it is PASSWORD for MySQL. I don't know for MaxDB.
> Then, your concept is somewhat off track.
> RegisterDatabase is mainly for creating an ODBC source that does not
> exist on the machine. And if so, you onlyŽneed - and should - create
> it once. If it does exist, you can use OpenConnection.
> For a relink of the TableDefs that do exist in your Access frontend,
> all you need is - one by one - to set the Connect property of these
> and do a RefreshLink. Example code should be in the on-line help of
> Access.
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
