Re: Access end Postgres

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.

http://home.core.com

------- 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?

http://archives.postgresql.org