Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Relinking Access tables that use MDW security

Status
Not open for further replies.
Ajb2528

Ajb2528

Technical User
Feb 22, 2002
270
GB
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 OLEDB:Database 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") = "SourceDataBase.mdb"
.Properties("Jet OLEDB:Link Provider String") = "MS Access;Pwd=admin"
' Again the name of the table you wish to link
.Properties("Jet OLEDB:Remote Table Name") = "TABLENAME"
.Properties("Jet OLEDB:Create Link") = True

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

End Sub

Regards,

Alan
 
Sort by date Sort by votes
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.

Code: 
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.
 
Upvote 0 Downvote
Blah, editing bugs:
Code: 
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
 
Upvote 0 Downvote
Bah, I'm nutty (more fixes):

Code: 
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.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

sal21
  • Locked
  • Question
use api whatsapp 1
Replies
3
Views
566
strongm
strongm
sal21
  • Locked
  • Question
via vb 6.0, please split access table
Replies
1
Views
290
strongm
strongm
2009luca
  • Question
possible to use xml2 or Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1"), instead code in message
Replies
5
Views
357
2009luca
2009luca
2009luca
  • Locked
  • Question
USE find instead to loop
Replies
2
Views
468
Andrzejek
Andrzejek
2009luca
  • Locked
  • Question
graph with access database - im on vb6 2
Replies
10
Views
1K
strongm
strongm

Part and Inventory Search

Sponsor

Back
Top