Redirect Table Target By Login Credentials

[shuff]

I have an application that works with our SQL-based collections package. In the SQL tables is a personal history table for each individual user that logs each account looked up w/date and time, etc. In my Access app, I want each user to be linked to his own personal table so that the app can fetch the most recently looked up account when a button is clicked.

The problem is that there are dozens of individual, personal history tables. I don't want to create links in my Access app to all of them, just the one appropriate for each user. NEED: I need a way, based on a variable associated with a person's login credentials to my app, to connect a user to his own table and no others. If the SQL Table is named dbo_history_Huff for example, the "Huff" part would be a value in my login credentials and would be used to uniquely identify my own history table apart from all others. I tried editing the referent to the SQL table in the MSysObjects table, but it was read-only.

Any ideas on how to pull this off, or alternately, how to open the MSysObjects table in an editable mode? That would sure be a handy way to correct the table referent. If there is a way to do this in objects rather than code, that would be my preference, but whatever!

Thanks,
Scott Huff,
Evanston (Chicago) IL USA

 

[JACKSON]

Scott,

Did you ever find a way to edit the msysobjects table? I am having the same issue now
jackson

 

[shuff]

RE: Redirect Table Target/Editing MSSysObject

No, no ideas from the board, and I had to move on to other projects but will be revisiting this sometime soon. If I learn the answer, I'll post it for you and others. -SLH

 

[JACKSON]

Just to save you some time. I've already tried:

- Written queries to edit the "Database" field in the MSYSObjects table, where there is a "foreign" value. This was no good do to a read only status of the table.

- Written code using ADO to open the try to modify the table data without actually opening the access database. It works fine with other tables, but when I try it on the the msysobjects table, same read only problem. I have a field called "path" on the current form to enter the new directory path in.

Here is that code in case it gives you any ideas. :

Private Sub Command2_Click()
Dim CNN As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strsql As String
Dim lngUpdated As Long

Set CNN = New ADODB.Connection
Set rst = New ADODB.Recordset
strsql = "C:\Program Files\Polaris Medical Chart Ver3.3\BACKEND\POLARIS BACKEND.mdb"
'establish the connection, cursor type, lock type, and open recordseset =
CNN.Open "provider=Microsoft.jet.oledb.4.0;" & "data source=" & CurrentProject.Path & "\frontend.mdb"
rst.ActiveConnection = CNN
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
'rst.Open ("SELECT * FROM MSYSOBJECTS")
rst.Open ("SELECT * FROM MSYSOBJECTS" & "WHERE DATABASE = STRSQL")

lngUpdated = 0

'loop through recordset, loacating rows which meet criteria, and updating database links

Do Until rst.EOF

lngUpdated = lngUpdated + 1
rst("DATABASE") = Me.Path
rst.Update
rst.MoveNext
Loop

rst.Close
Set rst = Nothing
CNN.Close
Set CNN = Nothing
MsgBox "OK"
Exit Sub
End Sub