Re: Access end Postgres - Mailing list 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@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