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
