linking / mdw file / ADODB.Connection

[iancampbellian]

help please

i'm trying to give users temporary admin security permissions so they can relink (via a button) to different back end databases.

i've created a connection but can't get it to work if i try and refer to the access workgroup security file.

can this be done?

if so what is the syntax?

help me whilst i still have hair !

 

[iancampbellian]

linking / mdw file / adodb.connection

Sub OpenConnection()

Dim cnn As New ADODB.Connection
Dim cat As New ADOX.Catalog
Dim sMdw As String 'workgroup file
Dim sFile As String 'database file
Dim sPdr As String 'provider
Dim sDS As String 'data source
Dim sCnn As String 'connection
Dim sUserID As String
Dim sPass As String

sPdr = "Provider=Microsoft.Jet.OLEDB.4.0;"


sMdw = " / WRKGRP C:\Documents and Settings\bbqn585\My Documents\system.mdw;"

'use with and without default workgroup
sFile = "C:\Documents and Settings\bbqn585\My Documents\db1.mdb" & sMdw
'sFile = "C:\Documents and Settings\bbqn585\My Documents\db1.mdb;"

sDS = "Data Source=" & sFile
sUserID = "Admin;"
sPass = " "

sCnn = sPdr & sDS & sUserID & sPass
MsgBox sCnn

cnn.Open sCnn

Set cat.ActiveConnection = cnn

MsgBox "Connected as user " & sUserID & " to " & sFile

Debug.Print cat.Tables(0).Type

Set cnn = Nothing

End Sub