Re: problem after upgrade --- - Mailing list pgsql-odbc

From Michael Calabrese
Subject Re: problem after upgrade ---
Date
Msg-id 35298.192.168.1.6.1049222264.squirrel@192.168.0.55
Whole thread Raw
In response to Re: problem after upgrade ---  (Jeff Silberberg <jsilberberg@mindspring.com>)
List pgsql-odbc
> that  when we did the upgrade to PostgreSQL 7.3 recently and now when we
> link to  the DB/Tables they all come back as public_nnnnnnnnn  and
> therefore, none  of the existing references work.
>
Here is the Access VBA Code that I use to relink tables from 7.3.x. Please
note in the code that I filter out some tables.

I hope that this h
'*********************************************************************************
Private Sub RelinkTables()
'*********************************************************************************
 '-----------------------------------------------
 'Make sure all tables and forms are closed!!!!!!
 '-----------------------------------------------
    'DB_DNS As String = "ODBC;DSN=BFData;"

    Static wsp As Workspace
    Static con As Connection
    Static init As Integer
    Dim stable As String
    Dim rst As Recordset
    Dim sSQL As String

    If init = 0 Then 'Detect if this is the first time we have run this
function
       init = 1 'Mark we have been through here
       'Initialize the ODBC Connection
       Set wsp = CreateWorkspace("ODBC Workspace", "", "", dbUseODBC)
       Set con = wsp.OpenConnection(DB_DNS, , , DB_DNS)
    End If
    sSQL = "SELECT tablename as Name " & _
              "FROM pg_tables " & _
             "WHERE schemaname = 'public' " & _
          "ORDER BY 1; "
    Set rst = con.OpenRecordset(sSQL, dbOpenSnapshot)
    'con.Execute sSQL, dbRunAsync
    'While con.StillExecuting
    '  DoEvents
    'Wend
    On Error Resume Next
    Do While Not rst.EOF
       stable = rst![Name]
       rst.MoveNext
       If (stable <> "mkt_contactdata") And (stable <> "msysconf") And
(stable <> "partcost_changes") And (Left(stable, 2) <> "zz") Then
          On Error Resume Next
          DoCmd.DeleteObject acTable, stable
          On Error GoTo 0
          DoCmd.TransferDatabase acLink, "ODBC Database", DB_DNS, acTable,
stable, stable
       End If
    Loop
    sSQL = "SELECT viewname as Name " & _
              "FROM pg_views " & _
             "WHERE schemaname = 'public' " & _
          "ORDER BY 1; "
    Set rst = con.OpenRecordset(sSQL, dbOpenSnapshot)
    'con.Execute sSQL, dbRunAsync
    'While con.StillExecuting
    '  DoEvents
    'Wend
    On Error Resume Next
    Do While Not rst.EOF
       stable = rst![Name]
       rst.MoveNext
       If (stable <> "mkt_contactdata") And (stable <> "msysconf") And
(stable <> "partcost_changes") And (Left(stable, 2) <> "zz") Then
          On Error Resume Next
          DoCmd.DeleteObject acTable, stable
          On Error GoTo 0
          DoCmd.TransferDatabase acLink, "ODBC Database", DB_DNS, acTable,
stable, stable
       End If
    Loop

End Sub


pgsql-odbc by date:

Previous
From: Chris Gamache
Date:
Subject: Re: problem after upgrade --- (U/D)
Next
From: "Dave Page"
Date:
Subject: SQLSetPos