Hi,
Everytime there is a user switch, I force the user to quit and open Access again. In my case, this an acceptable
option.
But someone sent me code to do that some time ago. I haven't tested that at all. Here it is. Thanks for your feedback.
Philippe
---------------------------
De : Nyle Davis [mailto:davisoft@core.com]
Envoyé : jeudi, 8. septembre 2005 20:11
À : Philippe Lang
Objet : PGSQL Entry - MS Access Linked Table
Philippe,
The reason you are having to shut down Access is you do not close out your connections in Access.
The example you are using:
*****************************************************************************************
'***************************************************************
'The DoesTblExist function validates the existence of a TableDef
'object in the current database. The result determines if an
'object should be appended or its Connect property refreshed.
'***************************************************************
Function DoesTblExist(strTblName As String) As Boolean
On Error Resume Next
Dim db As DAO.Database, tbl As DAO.TableDef
Set db = CurrentDb
Set tbl = db.TableDefs(strTblName)
If Err.Number = 3265 Then ' Item not found.
DoesTblExist = False
Exit Function
End If
DoesTblExist = True
End Function
Function CreateODBCLinkedTables() As Boolean
On Error GoTo CreateODBCLinkedTables_Err
Dim strTblName As String, strConn As String
Dim db As DAO.Database, rs As DAO.Recordset, tbl As DAO.TableDef
Dim strDSN as String
' ---------------------------------------------
' Register ODBC database(s).
' ---------------------------------------------
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * From tblODBCDataSources Order By DSN")
With rs
While Not .EOF
If strDSN <> rs("DSN") Then
DBEngine.RegisterDatabase rs("DSN"), _
"SQL Server", _
True, _
"Description=VSS - " & rs("DataBase") & _
Chr(13) & "Server=" & rs("Server") & _
Chr(13) & "Database=" & rs("DataBase")
End If
strDSN = rs("DSN")
' ---------------------------------------------
' Link table.
' ---------------------------------------------
strTblName = rs("LocalTableName")
strConn = "ODBC;"
strConn = strConn & "DSN=" & rs("DSN") & ";"
strConn = strConn & "APP=Microsoft Access;"
strConn = strConn & "DATABASE=" & rs("DataBase") & ";"
strConn = strConn & "UID=" & rs("UID") & ";"
strConn = strConn & "PWD=" & rs("PWD") & ";"
strConn = strConn & "TABLE=" & rs("ODBCTableName")
If (DoesTblExist(strTblName) = False) Then
Set tbl = db.CreateTableDef(strTblName, _
dbAttachSavePWD, rs("ODBCTableName"), _
strConn)
db.TableDefs.Append tbl
Else
Set tbl = db.TableDefs(strTblName)
tbl.Connect = strConn
tbl.RefreshLink
End If
rs.MoveNext
Wend
End With
CreateODBCLinkedTables = True
MsgBox "Refreshed ODBC Data Sources", vbInformation
CreateODBCLinkedTables_End:
Exit Function
CreateODBCLinkedTables_Err:
MsgBox Err.Description, vbCritical, "MyApp"
Resume CreateODBCLinkedTables_End
End Function
*****************************************************************************************
Needs the following additional actions/code:
Create a "Globals" module with that name under Insert + Module
Add the following statements to it:
Public db As DAO.Database, tbl As DAO.TableDef
Public strConn As String, rs As DAO.Recordset
Public LinkStr As String
Save the Module! Remember name=Globals
Delete those definitions from your existing code blocks
Add the following functions (Preferrably also in the Globals module, but not necessary)
Public Function DropLinked(LinkTab as string) As Boolean
Dim n as integer
For n = 1 to words(LinkTab)
db.TableDefs.Delete word(LinkTab,n)
next n
rs.close
tbl.close
strConn.close
Set rs = Nothing
Set tbl = Nothing
Set strConn = Nothing
End Function
Public Function Words(MyWLine As String) As Integer
' Find the Words in the input string whether
' Blank <" ">, Comma <","> or Blank+Comma <", "> separated
Dim bCount As Integer, cCount As Integer, bcCount As Integer
Words = 0
bCount = UBound(Split(MyWLine, " ")) + 1
cCount = UBound(Split(MyWLine, ",")) + 1
bcCount = UBound(Split(MyWLine, ", ")) + 1
If bcCount > 0 Then
Words = bcCount
ElseIf bCount > 0 Then
Words = bCount
ElseIf cCount > 0 Then
Words = cCount
End If
End Function
Public Function Word(MyWLine As String, MyIdx As Long) As String
' Find the nth Word in the input string
Dim MyArr1() As String, MyArr2() As String, MyArr3() As String
Word = MyWLine
If Len(MyWLine) > 0 Then
MyArr1 = Split(MyWLine, " ")
MyArr2 = Split(MyWLine, ",")
MyArr3 = Split(MyWLine, ", ")
If UBound(MyArr3) < MyIdx And MyArr3 <> "" Then
Word = myarr(MyIdx - 1)
ElseIf UBound(MyArr1) < MyIdx And MyArr1 <> "" Then
Word = myarr(MyIdx - 1)
ElseIf UBound(MyArr2) < MyIdx And MyArr2 <> "" Then
Word = myarr(MyIdx - 1)
End If
End If
End Function
Also add this line to the CreateODBCLinkedTables routine:
LinkStr = LinkStr & " " & tbl
Right after the line
db.TableDefs.Append tbl
When you intend to refresh the links do so with:
Sub Refresh()
Call DropLinked(LinkStr)
Call CreateODBCLinkedTables
End Function
I think this will solve your problem. If it does or you have to make changes please repost so all can have as a
resource.
Thanks!
OldManRiver
PS. Word and Words have just been modified and testing is not complete. Let me know if they cause problems and I'll
updateyou with tested code.
--
CoreComm Webmail.
http://home.core.com
------- End of forwarded message -------
Nyle Davis
Account Manager/Analyst
(972)-252-6657
davisoft@megsinet.net
-----Message d'origine-----
De : pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] De la part de Jone
Envoyé : jeudi, 22. septembre 2005 10:32
À : pgsql-odbc@postgresql.org
Objet : [ODBC] Access end Postgres
Hello,
I am working with Microsoft Access 2003 accessing a postgres database via ODBC.
I would like to know the best way to change the current user logged in the database without closing my application.
I have made a module that create linked table (ODBC without DSN). If i use it with a first user it works fine but then
ifi delete all the links and then link my table with another user, when i access the table i have always the rights
affectedto the first user and in the postgres logs i can see that it is always the first user that is identified. But
ifi execute an ODBC request by program with the second user (without using the linked table), in the logs i can see
thatthe second user has really executed that request.
I have also executed the command "set session authorization" but it dose not seem to work even with a super-user.
Can anyone help me on this issue ?
Thank you by advance.
--
Jone SAUBABER HARAN
HIZKIA Informatique
64100 Bayonne (FRANCE)
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org