How to (in vba code) identify if a linked ODBC table has a primary

[Lisab]

I am working with Access 2007 with linked Sybase tables

According to our new policy change, each user connected to the Sybase server
must be using their own assigned ID. Which means the application distributed
to the users must have a procedure to relink the tables using the users
provided id and password.

That code is working, however, some of the Sybase tables do not have a
primary key. When they were originally linked, the primary key(s) were
selected using the Access interface. Now, using this new code to relink the
tables, when i look at those tables in design view, for the newly relinked
table, I see no primary key.

1. what is the VBA code to identify if a table has a primary key

Here is my code -- Thank you

If Left$(.Connect, 4) = "ODBC" Then
strConnect = Left(.Connect, InStr(1, .Connect, "UID=") - 1)
& "UID=" & strUID & ";PWD=" & strPWD
Set tdf = DB.TableDefs(.Name)
tdf.Connect = strConnect
tdf.RefreshLink
'--------------------------------
'if table.name = one that need PrimaryKey reset then
'see if the table kept its primerykey, if so drop it
'run code to create new primerykey
'CREATE INDEX NewIndex ON <table_name>(<field_one>,
<field_two>) With PRIMARY
'end if
'--------------------------------
DB.QueryDefs.Delete ("qry_Test_ODBC_Links")
Set qdfNew = DB.CreateQueryDef("qry_Test_ODBC_Links",
"SELECT TOP 1 * FROM " & .Name)
If qdfNew.ReturnsRecords = False Then GoTo cErr
End If

 

[Clifford Bass]

Hi Lisa,

Why do you have to relink them? I cannot address Sybase, but with
Oracle, SQL Server, SQLBase and probably others; they all prompt the user to
enter their credentials regardless of who initially linked the tables. That
is, as long as the tables were not linked with the password being stored. If
that was the case, simply delete them and then link them anew without storing
the password. It may be a good idea to make a backup before you do that.

Hope this helps,

Clifford Bass

 

[Lisab]

thanks for your reply.

It is because the front-end is entirely automated for the users. There is a
lot of VBA code. When the users run the application, they won't get that
popup to enter there userID and password until they happen to click a button
or open a form that needs to run a query or create a recorset using one of
the linked tables. Since the linked tables come from 6 different SYBase
databases, they may get that popup as many as 6 times during one session.
They are spoiled and don't want to do that. And will start complaining that
they can't properly do their job because the application is too complicated
.... and not working properly because why is it asking them so many times to
enter their userID and password. blah blah blah.

 

[Clifford Bass]

Hi Lisa,

That makes sense to me. To check if a table has a primary key index
use something like this:

Dim boolHasPrimary As Boolean
Dim db As DAO.Database
Dim idx As DAO.Index

Set db = CurrentDb
boolHasPrimary = False
For Each idx In db.TableDefs("tblElection Results").Indexes
If idx.Primary Then
boolHasPrimary = True
MsgBox "Primary index is " & idx.Name & "."
Exit For
End If
Next idx
If Not boolHasPrimary Then
' Create a primary index
End If

Hope that helps,

Clifford Bass

 

[Lisab]

Clifford, thank you very very much.

Hi Lisa,

That makes sense to me. To check if a table has a primary key index
use something like this:

Dim boolHasPrimary As Boolean
Dim db As DAO.Database
Dim idx As DAO.Index

Set db = CurrentDb
boolHasPrimary = False
For Each idx In db.TableDefs("tblElection Results").Indexes
If idx.Primary Then
boolHasPrimary = True
MsgBox "Primary index is " & idx.Name & "."
Exit For
End If
Next idx
If Not boolHasPrimary Then
' Create a primary index
End If

Hope that helps,

Clifford Bass

 

[Clifford Bass]

Hi Lisa,

You are welcome!

Clifford Bass

 

[Lisab]

Here is the final code if any one is interested.
The tbl_Login_keys has list all the tables that are views and therefore need
to have their primary keys reset after relinking (DatabaseName,
SourceTableName, LocalTableName, PKFieldName
---------------------------------------------------------------------------------
Public Sub CheckPrimaryKey(TblName As String, DBName As String)
'reset the PrimaryKey(s) if the table is in tbl_Login_Keys
Dim strSQL As String
Dim idxFlds As String
Dim TheDB As Database
Dim SourceRS As DAO.Recordset
Dim FirstTimeThrough As Boolean

FirstTimeThrough = True

strSQL = "SELECT tbl_Login_Keys.* FROM tbl_Login_Keys " _
& "WHERE (((tbl_Login_Keys.DatabaseName)=" & """" & DBName & """" & ") " _
& "AND ((tbl_Login_Keys.LocalTableName)=" & """" & TblName & """" & "));"

Set TheDB = CurrentDb
Set SourceRS = TheDB.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

If SourceRS.RecordCount Then
'see if the table kept its primerykey, if so drop it
DropPrimaryKey TblName

'get fields for the index
SourceRS.MoveFirst
Do Until SourceRS.EOF
If FirstTimeThrough = False Then
idxFlds = idxFlds & ", " & SourceRS!PKFieldName
Else
idxFlds = SourceRS!PKFieldName
End If
FirstTimeThrough = False
SourceRS.MoveNext
Loop

'create new PrimaryKey
TheDB.Execute "CREATE INDEX NewIndex ON " & TblName & "(" & idxFlds & ")
with PRIMARY"

End If

SourceRS.Close
Set SourceRS = Nothing
Set TheDB = Nothing

End Sub
----------------------------------------------------------------
Public Sub DropPrimaryKey(TblName As String)

Dim db As DAO.Database
Dim idx As DAO.index

Set db = CurrentDb

For Each idx In db.TableDefs(TblName).Indexes
If idx.Primary Then
'MsgBox "Primary index is " & idx.Fields & "."
'drop Primary Key
db.Execute "DROP INDEX " & idx.Name & " ON " & TblName
End If
Next idx

Set db = Nothing

End Sub