linking to access db with different mdw

[SuryaF]

I need to link my database to another one that uses different permissions.
I need to do this from the code as both the database file and the workgroup file need to be changed at some point.
Any idea on how to specify the mdw file?
Thanks!

 

[ItIsHardToProgram]

Hi SuryaF, do you mean that you want to Edit a MDW by code?

 

[SuryaF]

Oh, no, I wouldn't dare.

So I have my db plus another database with user-lever security set up in a certain workgroup file.
I want to be able to create linked tables to that database.
If I don't join that workgroup it tells me I don't have permissions to open the database, naturally.
But how do I join the workgroup from code?

Maybe if I can specify in the TableDef to use a certain MDW file (in the Connect string?) and then relink the table that will solve the problem.

Any ideas?

 

[JerryKlmns]

Add referrences to
Microsoft ActiveX Data Objects 2.x Library
Microsoft ADO Ext. 2.x for DDL and Security

Call LinkSecTbl ("NewLinkedTableName", "TableNameToLink", "\\ServerName\Path\TheDBase.mdb",  "\\ServerName\Path\TheDBase.mdw", "EgoNte", "ToTipota")

Sub LinkSecTbl (strNewTable As String, strTable As String, MDBFile As String, MDWFile As String, TheUserId As String, ThePassword As String)
Dim App_Cnn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table

Set App_Cnn = New ADODB.Connection
With App_Cnn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Properties("Data Source") = MDBFile
    .Properties("Jet OLEDB:System database") = MDWFile
    .Properties("User ID") = TheUserId
    .Properties("Password") = ThePassword
    .Properties("Mode") = adModeShareDenyNone
    .Properties("Jet OLEDB:Engine Type") = 5
    .Properties("Locale Identifier") = 1033
    .Open
End With

Set cat = New ADOX.Catalog
Set tbl = New ADOX.Table
Set cat.ActiveConnection = App_Cnn
Set tbl.ParentCatalog = cat
With tbl
    .Properties("Jet OLEDB:Create Link") = True
    .Properties("Jet OLEDB:Link Datasource") = MDBFile
    .Properties("Jet OLEDB:Remote Table Name") = strTable
    .Name = strNewTable
End With
cat.Tables.Append tbl
cat.Tables.Refresh
Set tbl = Nothing
Set cat = Nothing
App_Cnn.Close
Set App_Cnn = Nothing

End Sub

The result should be a new linked table named as NewLinkedTableName

 

[SuryaF]

Wow, I have so much more to learn! Thanks a buch Jerry!

The code is pretty tricky though, how do I make it to create the linked table in my current db?
The code as it is right now creates the link in the database I'm linking to, and that doesn't help much. It's a link to itself.
Thanks again!

 

[JerryKlmns]

Set cat.ActiveConnection = CurrentProject.Connection

 

[SuryaF]

Well, that wouldn't work.
It would create the linked table in my database but it would still be using my regular system database (mdw). So when I try to open the link I'll still have no permissions.

Is there any way to specify the system db (workgroup) for a linked table?