Hi Ed,
I will get some code ... meanwhile
1. make a blank database and link to some tables -- save the username
and password
2. put this code into a general module and run it.
this routine changes all the table description properties to the connect
string -- so you can study them.
'~~~~~~~~~~~~~~~~~~~~~
'------------------------------------ ChangeTableDescription_To_Connect
'
' change Table Description to Connect string for linked tables
'
Sub ChangeTableDescription_To_Connect()
'Crystal
'strive4peace2008 at yahoo.com
'July 30, 2008
'NEEDS
' Reference to Microsoft DAO Library
' click HERE
' press F5 to Run
'CALLS
' GetTableDescription
' IsPropertyDefined
On Error GoTo Proc_Err
Dim db As DAO.Database _
, tdf As DAO.TableDef
Dim mDescription As String
Set db = CurrentDb
'loop through all the tables in the current database
For Each tdf In db.TableDefs
'if the table is not linked, skip it
If Not tdf.SourceTableName <> "" Then GoTo NextTdf
mDescription = tdf.Connect
If IsPropertyDefined("Description", tdf) Then
tdf.Properties("Description") = mDescription
Else
tdf.Properties.Append _
tdf.CreateProperty( _
"Description" _
, dbText _
, mDescription)
End If
NextTdf:
Next tdf
'make new table descriptions display immediately
db.TableDefs.Refresh
Application.RefreshDatabaseWindow
MsgBox "Done changing Table Descriptions to Connect String", , "Done"
Proc_Exit:
Set tdf = Nothing
Set db = Nothing
Exit Sub
Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " ChangeTableDescription_To_Connect"
Resume Proc_Exit
'if you want to single-step code to find error, CTRL-Break at MsgBox
'then set this to be the next statement
Resume
End Sub
'------------------------------------ GetTableDescription
'
Function GetTableDescription( _
ByVal pTablname As String _
, dbObj As DAO.Database _
) As String
'Crystal
'strive4peace2008 at yahoo.com
'July 25, 2008
'
'PARAMETERS
' pTablname is the name of the table
' dbObj is a database object
'RETURNS
' Table Description if defined
' an empty string (ZLS) if not
'
On Error GoTo Proc_Err
GetTableDescription = ""
Dim prp As DAO.Property
For Each prp In dbObj.TableDefs(pTablname).Properties
If prp.Name = "Description" Then
GetTableDescription = prp
GoTo Proc_Exit
End If
Next prp
Proc_Exit:
Set prp = Nothing
Exit Function
Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " GetTableDescription"
Resume Proc_Exit
Resume
End Function
'------------------------------------ IsPropertyDefined
'
' this is a generic function to see if
' a property is defined
' passed object can be a Table, Field, Database, ...
'
Function IsPropertyDefined( _
ByVal pPropName As String _
, Optional ByVal Obj As Object _
) As Boolean
'Crystal
'strive4peace2008 at yahoo.com
'June 20, 2008
'
'PARAMETERS
' Obj can be a database, a Tabledef, a Field...
' if it is missing, CurrentDb is used
On Error GoTo Proc_Err
IsPropertyDefined = False
Dim prp As DAO.Property
If Obj Is Nothing Then
Set Obj = CurrentDb
End If
For Each prp In Obj.Properties
If prp.Name = pPropName Then
IsPropertyDefined = True
GoTo Proc_Exit
End If
Next prp
Proc_Exit:
Set prp = Nothing
Exit Function
Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " IsPropertyDefined"
Resume Proc_Exit
'if you want to single-step code to find error, CTRL-Break at MsgBox
'then set this to be the next statement
Resume
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Warm Regards,
Crystal
remote programming and training
Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace
*
have an awesome day
*
