Relinking Access tables that use MDW security

[Ajb2528]

I am trying to write a VB6 application to relink Access97 linked tables. I have seen various code snippets that will relink using a password secured Access mdb (using ADOX) but none that use a Workgroup security file. Has anyone got any ideas on this???

This is the code that I have found....


Private adoCn As ADODB.Connection
Private adoCat As New ADOX.Catalog
Private adoTable As New ADOX.Table


Private Sub Form_Load()
Dim ConnStr As String
' This is the database where you want the link to be created
ConnStr = "MainDataBase.mdb"
Set adoCn = New ADODB.Connection
With adoCn
.Provider = "Microsoft.JET.OLEDB.4.0;Jet OLEDBatabase Password=admin"
.Open ConnStr
End With

End Sub

and....

Private Sub cmdCreate_Click()
Set adoCat = New ADOX.Catalog
Set adoCat.ActiveConnection = adoCn

Set adoTable.ParentCatalog = adoCat

With adoTable
' The name of the table you want to link
.Name = "TABLENAME"
' The source database where the linked table is held
.Properties("Jet OLEDB:Link Datasource&quot = "SourceDataBase.mdb"
.Properties("Jet OLEDB:Link Provider String&quot = "MS Access;Pwd=admin"
' Again the name of the table you wish to link
.Properties("Jet OLEDB:Remote Table Name&quot = "TABLENAME"
.Properties("Jet OLEDB:Create Link&quot = True

' Add the table to the Linked database
adoCat.Tables.Append adoTable
End With

End Sub

Regards,

Alan

 

[psemianonymous]

You can get the object for workgroup security via the Jet Runtime Objects library and DAO. I don't know how to do this via ADO.

Dim ws as DAO.Workspace
Dim db as DAO.Database
set ws = DBEngine.CreateWorkspace(args, including username & pw)

'part 2
set db = ws.OpenDatabase(args, including dbname)
db.TableDefs("TableNAme").Connect = "New connect string"
db.RefreshLink
db.Close
ws.Close

Obviously you replace the arguments/tablenames/etc with proper data. I have coded the first part (creating a workspace object) to check on users' group membership. I have coded the second part to update my table links when I go from development to production. But I've never done the two together. I'm sure the above code will get you where you want to go.

If you're going to be distributing this for free, reply to this thread. I'm interested, definitely.

 

[psemianonymous]

Blah, editing bugs:

Dim ws as DAO.Workspace
Dim db as DAO.Database
Dim tbl as DAO.TableDef
set ws = DBEngine.CreateWorkspace(args, including username & pw)

'part 2
set db = ws.OpenDatabase(args, including dbname)
foreach tbl in db.TableDefs
  set tbl = db.TableDefs("TableNAme")
  tbl.Connect = "New connect string"
  tbl.RefreshLink
loop

set tbl = nothing  'Not sure if this line is necessary,
                   'I read somewhere it is necessary
                   'after a foreach loop with form controls
db.Close
ws.Close
set db = nothing
set ws = nothing

 

[psemianonymous]

Bah, I'm nutty (more fixes):

Dim ws as DAO.Workspace
Dim db as DAO.Database
Dim tbl as DAO.TableDef
set ws = DBEngine.CreateWorkspace(args, including username & pw)

'part 2
set db = ws.OpenDatabase(args, including dbname)
foreach tbl in db.TableDefs
  'line deleted here
  tbl.Connect = "New connect string"
  tbl.RefreshLink
loop

set tbl = nothing  'Not sure if this line is necessary,
                   'I read somewhere it is necessary
                   'after a foreach loop with form controls
db.Close
ws.Close
set db = nothing
set ws = nothing

And while I'm here, I don't think it's possible to update Access table links using ADO. I think the table object itself is a DAO object and thus you're going to have to use the above to reach it; (to my knowledge) there is no other way.