Linked Table Manager deletes Primary Keys on tables...

D

DraguVaso

Hi,

I have a problem with Access 2000: After I did a refresh of my Linked Tables
with the Linked Table Manager, all the tables lose their Primary Key.

Does anybody know how I can prevent it from doing this?

Thanks a lot,

Pieter
 
B

Brian

DraguVaso said:
Hi,

I have a problem with Access 2000: After I did a refresh of my Linked Tables
with the Linked Table Manager, all the tables lose their Primary Key.

Does anybody know how I can prevent it from doing this?

Thanks a lot,

Pieter

Annoying, isn't it? The Linked Table manager is a really horrible tool.
And no, I don't know any way of stopping it doing this.

However, a little project I have on at the moment is to develop something
better than the Linked Table Manager for re-linking SQL Server tables. I'll
let you know when I've got it sorted.
 
B

Brian Camire

I don't think you can't prevent pseudo-indexes on linked views from being
dropped when you refresh them, but you can re-create them.

After relinking, you can do this by executing (maybe using the Execute
method of a DAO.Database object or an ADODB.Command object) a CREATE UNIQUE
INDEX statement *in Access* that identifies the field(s) whose value(s)
uniquely identify each record. See the CREATE INDEX topic in the help for
more information.
 
D

DraguVaso

Ok thanks! That did the trick!

Brian Camire said:
I don't think you can't prevent pseudo-indexes on linked views from being
dropped when you refresh them, but you can re-create them.

After relinking, you can do this by executing (maybe using the Execute
method of a DAO.Database object or an ADODB.Command object) a CREATE UNIQUE
INDEX statement *in Access* that identifies the field(s) whose value(s)
uniquely identify each record. See the CREATE INDEX topic in the help for
more information.
 
J

Joe at NC State

Have you completed the project to which you referred earlier in this
thread (i.e., a replacement for the linked table manager that would
retain the unique index created in Access for a linked object)?

I've written a function (variation on microsoft's "changelink" function
described in KB article # 172347 & Dev Ashish's code in The Access Web)
that loops through linked table names found in a control table,
replaces all occurrences of a given text string within the connect
string with another string of our choosing (to specify a different
server name), and refreshes all the links. Only problem is, the
indexes aren't retained, nor is the description of the object changed
to reflect the new connection string. It would be so cool if the
function could somehow capture whatever the indexes were (if any) on
the linked objects and set those same indexes when it refreshes the
links, as well as refresh each description.

Even better would be if the function could somehow loop through every
linked object that uses a pre-determined system DSN; that would
eliminate having to use a control table that lists all the names of the
linked objects. We've got dozens of Access databases with ODBC links
to Sybase objects on two servers that are being retired & replaced with
new servers. A function that does this programmatically that I could
import into each of our databases would be so much more preferrable to
having to relink hundreds of tables manually. Any advice or direction
would be greatly appreciated.
 
J

Joe Fallon

Not sure if this will help but....

I use this procedure to re-create links to Oracle.
There is a local Access table (tblODBCTables) that contains the table names
and primary key fields I want to link to on the Server.
Note: the source table name needs the Schema User prefix which is in the
code. The linked table name usually omits this.

Public Function LinkOracleTables(strDSN As String, strSchema As Variant,
strSchemaPwd As Variant) As Boolean
On Error GoTo Err_LinkOracleTables

Dim db As Database, rs As Recordset, tdfAccess As TableDef, qdf As
QueryDef
Dim dbODBC As Database, strConnect As String, strSQL As String

If strDSN = "" Then
MsgBox "You must supply a DSN in order to link tables."
Exit Sub
Else
strConnect = "ODBC;DSN=" & strDSN & ";UID=xyz" & ";PWD=abc;"
End If

SysCmd acSysCmdSetStatus, "Connecting to Oracle..."

Call DeleteODBCTableNames

Set db = CurrentDb
Set rs = db.OpenRecordset("tblODBCTables")
Set dbODBC = OpenDatabase("", False, False, strConnect)
DoCmd.SetWarnings False

Do While Not rs.EOF
Set tdfAccess = db.CreateTableDef(rs![LinkTablename], dbAttachSavePWD)
tdfAccess.Connect = dbODBC.Connect
tdfAccess.SourceTableName = strSchema & "." & rs![LinkTablename]
db.TableDefs.Append tdfAccess
'run pseudo index queries here. If the table does not exist then this
gets skipped.
strSQL = "CREATE INDEX " & rs![LinkTablename] & "Idx ON " &
rs![LinkTablename] & " (" & rs![IndexFields] & ");"
DoCmd.RunSQL strSQL
TableNotInCollection:
rs.MoveNext
Loop

LinkOracleTables = True

Exit_LinkOracleTables:
On Error Resume Next
DoCmd.SetWarnings True
rs.Close
Set rs = Nothing
Set dbODBC = Nothing
Set db = Nothing
SysCmd acSysCmdClearStatus
Exit Function

Err_LinkOracleTables:
Select Case Err.Number
Case 3151
MsgBox ("There is an ODBC datasource problem." & vbCrLf & "Please
verify the DSN and database are spelled correctly." & vbCrLf & "Note: They
can be case sensitive.")
Case 3265, 3011, 7874 'item not in collection - table does not exist, or
can't find object
Resume TableNotInCollection
Case Else
MsgBox "Error # " & Err.Number & " was generated by " & Err.Source &
vbCrLf & Err.Description, , "LogOnCode - LinkOracleTables"
End Select
LinkOracleTables = False
Resume Exit_LinkOracleTables

End Function

'This procedure deletes all linked ODBC table names in an mdb.
Public Sub DeleteODBCTableNames()
On Error GoTo Err_DeleteODBCTableNames

Dim dbs As Database, tdf As TableDef, I As Integer
Set dbs = CurrentDb
For I = dbs.TableDefs.Count - 1 To 0 Step -1
Set tdf = dbs.TableDefs(I)
If (tdf.Attributes And dbAttachedODBC) Then
dbs.TableDefs.Delete (tdf.Name)
End If
Next I

dbs.Close
Set dbs = Nothing

Exit_DeleteODBCTableNames:
Exit Sub

Err_DeleteODBCTableNames:
MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source
& Chr(13) & Err.Description)
Resume Exit_DeleteODBCTableNames

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top