Dashboard

[JC3]

I have created an Access Dashboard for a SQL Database. This is secured user Access User Level Security and works fine.

I am now looking to add some data from other Secured Access Databases but these are secured using different Workgroup Files.

Is there a way to do this?

 

[JC3]

Any ideas?

 

[JC3]

I have managed to create an ADODB connection to the Secured Database using the following code.

Public Function test()

Dim sd As New ADODB.Connection
Dim strSource As String

strSource = "Driver={Microsoft Access Driver (*.mdb)};" & _
        "Dbq=P:\Job List\27. KPI Schedule Reports Database\Test\KPI_Schedule_FE.mdb;" & _
        "SystemDB=P:\Job List\27. KPI Schedule Reports Database\Test\Secured.mdw;" & _
        "Uid=USER;Pwd=PWD"

sd.Open strSource

MsgBox sd.ConnectionString

End Function

This seems to confirm that there is a connection to the DB but I have no idea what to do now to transfer a file from the connected DB to this DB.

Can anyone help.

 

[JC3]

From looking at various places I have compiled this code which Connects to the Secured DB and uses Recordsets to copy the data to the Current DB. Seems to work ok but if anyone can see any problems with it give me a shout.

Function Test()
On Error GoTo Err_Test

' Dim Current Connection
Dim curDB As New ADODB.Connection
Dim curRS As New ADODB.Recordset

'Dim Link Database
Dim lnkDB As New ADODB.Connection
Dim lnkRS As New ADODB.Recordset
Dim lnkSTR As String
Dim lnkCNT As Long
Dim n As Long


' Set Current Recordset
Set curDB = CurrentProject.Connection
curRS.Open "tblTest", curDB, adOpenStatic, adLockPessimistic

'Set Link Recordset
lnkSTR = "Driver={Microsoft Access Driver (*.mdb)};" & _
        "Dbq=SecuredDB.mdb;" & _
        "SystemDB=Workgroup.mdw;" & _
        "Uid=USER;Pwd=PASSWORD"

lnkDB.Open lnkSTR
lnkRS.Open "tblUserLogin", lnkDB, adOpenStatic, adLockPessimistic
n = 0
lnkCNT = lnkRS.RecordCount

If curRS.RecordCount = 0 Then curRS.AddNew Else curRS.MoveLast
lnkRS.MoveFirst

Do While n < lnkCNT
    curRS.Fields("DatabaseUserName") = lnkRS.Fields("DatabaseUserName")
    curRS.Fields("NetworkUserName") = lnkRS.Fields("NetworkUserName")
    curRS.Fields("LogInDate") = lnkRS.Fields("LogInDate")
    curRS.Fields("LogInTime") = lnkRS.Fields("LogInTime")
    curRS.Fields("LogOutTime") = lnkRS.Fields("LogOutTime")
    curRS.Fields("ID") = lnkRS.Fields("ID")
    curRS.Update
    curRS.AddNew
    lnkRS.MoveNext
    n = n + 1
Loop

MsgBox n

lnkRS.Close
lnkDB.Close
curRS.Close
curDB.Close

Exit_Test:
    Exit Function

Err_Test:
    lnkRS.Close
    lnkDB.Close
    curRS.Close
    curDB.Close
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_Test

End Function