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.
/gustav
> Date: 2004-06-04 19:05
> I have created a database in MaxDB 7.5.00.14 and have several tables and
> views, and everything seems to be going well.
> However, I have a question about the ODBC driver, as I am attempting to
> create an interface using Access that uses linked
> tables. I can attach to my DB just fine no issues at all, but I wanted to
> be able to have a login form that the user would put in
> their MaxDB userID and password and then based upon that re-link the
> tables using their permissions. I have the code working
> but it requires that they log into each table seperately. So I added the
> necessary connection info to pull in user ID but it put "" around
> the userID. So, I added a statement that would convert the userID to
> upper case and that solved that issue. However, if I add the password
> into the equation it fails again. If I put a statement in to show the
> connection string prior to connecting, the string is just fine. I think
> what is happening
> is that the MaxDB client is adding "" around the password as well, and I
> tried to add LCase to the password (since the account I was testing with
> had all lc in its passwod), but that didn't help.
> Here is a sample of the VB code:
> 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, 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 = Trim(Forms!Login.Password)
> 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, _
> dbAttachSavePWD, rs("ODBCTableName"), _
> strConn)
> 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:
> MsgBox Err.Description, vbCritical, "BSCM"
> Resume CreateODBCLinkedTables_End
> End Function
> As you see I added a Trim statement in case that would solve the issue,
> but it didn't help. So I was wondering what am I doing wrong, or is there
> problem connecting this way. I'd hate to have to connect to each table
> individually, but it does work if I leave the password field on my login
> form blank; it just prompts me for each and every table.
> James
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
