From: | "Philippe Lang" <philippe(dot)lang(at)attiksystem(dot)ch> |
---|---|
To: | <j(dot)haran(at)hizkia(dot)fr>, <pgsql-odbc(at)postgresql(dot)org> |
Subject: | Re: Access end Postgres |
Date: | 2005-09-22 08:44:28 |
Message-ID: | 6C0CF58A187DA5479245E0830AF84F4218CBDA@poweredge.attiksystem.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-odbc |
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(at)core(dot)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 update you with tested code.
--
CoreComm Webmail.
------- End of forwarded message -------
Nyle Davis
Account Manager/Analyst
(972)-252-6657
davisoft(at)megsinet(dot)net
-----Message d'origine-----
De : pgsql-odbc-owner(at)postgresql(dot)org [mailto:pgsql-odbc-owner(at)postgresql(dot)org] De la part de Jone
Envoyé : jeudi, 22. septembre 2005 10:32
À : pgsql-odbc(at)postgresql(dot)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 if i delete all the links and then link my table with another user, when i access the table i have always the rights affected to the first user and in the postgres logs i can see that it is always the first user that is identified. But if i execute an ODBC request by program with the second user (without using the linked table), in the logs i can see that the 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?
From | Date | Subject | |
---|---|---|---|
Next Message | Philippe Lang | 2005-09-22 09:00:05 | ODBC driver types |
Previous Message | Jone | 2005-09-22 08:31:49 | Access end Postgres |