Re: PostgreSQL and Access - relink - Mailing list pgsql-odbc

Hi,

see codesnippet below.

we use this to relink tables which are partionend on month-base.
Views would be more elegant, but it's an legacy app, where we
sometimes put some effort to use the features of a real DBMS like PG.

The information about the tables(foeignname, localname,..) is in another table.

Public Function create_tbl_defs(foreign_name As String, local_name As String, Optional Aktuell As Boolean = True) As
Boolean

    Dim Monat As String
    Dim vormonat As String
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strConnect As String
    Dim db_inf As db_info
    Dim rtn As Boolean

    'On Error GoTo create_tbl_defs_Error

    ' Prüfen ob es eine Monatstabelle ist die immer wieder neu eingebunden werden soll
    If Aktuell Then


        If InStr(local_name, "_aktuellv") > 0 Then
            ' Wenn der Localname mit aktellv aufhören sollte wird der vormonat genommen
            get_monext Monat, vormonat
            foreign_name = foreign_name & vormonat
        Else
            ' Der aktuelle Monat wird aus der tblmonate geholt
            get_monext Monat
            foreign_name = foreign_name & Monat

        End If
    End If

    ' Tabellen verknüpfen

    db_inf = get_db_info(gl_db_name)
    On Error Resume Next
    strConnect = "ODBC;DRIVER={PostgreSQL}" _
               & ";SERVER=" & db_inf.ip _
               & ";DATABASE=" & db_inf.dbname _
               & ";UID=" & db_inf.uid _
               & ";PWD=" & db_inf.pwd & ";"

    Set db = CurrentDb()
    Set tdf = db.CreateTableDef(local_name)
    tdf.SourceTableName = foreign_name

    tdf.connect = strConnect

    db.TableDefs.Append tdf
    db.TableDefs.Refresh

    'Application.RefreshDatabaseWindow

    If Err.Number = 3011 Then
        If MsgBox("Die Tabelle " & foreign_name & " konnte nicht gefunden werden." & _
                  vbCrLf & "Wahrscheinlich ist sie auf dem Server nicht vorhanden." & _
                  vbCrLf & "Soll das Einbinden der Tabellen abgebrochen werden (Ja) " & _
                  vbCrLf & "oder soll die Tabelle ignoriert werden (Nein)?", vbYesNo, "Tabelle nicht gefunden") = vbYes
_
                  Then
            rtn = False

        Else
            rtn = True
        End If

    Else
        If Err.Number <> 0 Then
            GoTo create_tbl_defs_Error
        Else
            rtn = True
        End If
    End If
    On Error GoTo 0
    Application.RefreshDatabaseWindow
    create_tbl_defs = rtn

    On Error GoTo 0
    Set tdf = Nothing
    Set db = Nothing
    Exit Function

create_tbl_defs_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure create_tbl_defs of VBA Dokument
Form_frmHauptseite"
    Set tdf = Nothing
    Set db = Nothing
    create_tbl_defs = False
End Function

HTH

Hakan Kocaman
Software-Development

digame.de GmbH
Richard-Byrd-Str. 4-8
50829 Köln

Tel.: +49 (0) 221 59 68 88 31
Fax: +49 (0) 221 59 68 88 98
Email: hakan.kocaman@digame.de



> -----Original Message-----
> From: pgsql-odbc-owner@postgresql.org
> [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Jonathan Blitz
> Sent: Tuesday, April 18, 2006 4:57 PM
> To: pgsql-odbc@postgresql.org
> Subject: [ODBC] PostgreSQL and Access - relink
>
>
> I have an Access project linked to a PostgreSQL database
> (linked tables).
>
> I need to be able to switch dynamitically between one
> PostgreSQL database
> and another according to the request from the user.
>
> Anyone have any code that will do this?
>
> I assume it will require me to re-link all the tables.
>
> Jonathan Blitz
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>