Unfortunately, none of these worked. I am attempting to link all tables
at once, then make it possible for the user to switch to another ID and
relink
with those permissions without shutting down Access and restarting.
However, after more testing I have been able to get it to bring up the
MaxDB
Login only once and that will work. Unfortuantely Access still requires a
shutdown to break the ODBC links, but that is an Access issue not with the
ODBC client, and I still can't pass the password to the MaxDB client, but
I can live with that for now.
Just in case anyone is curious this is what the code looks like now.
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.
/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]
