Skip site navigation (1) Skip section navigation (2)

Re: PostgreSQL and Access - relink

From: "Hakan Kocaman" <Hakan(dot)Kocaman(at)digame(dot)de>
To: "Jonathan Blitz" <jb(at)anykey(dot)co(dot)il>, <pgsql-odbc(at)postgresql(dot)org>
Subject: Re: PostgreSQL and Access - relink
Date: 2006-04-18 14:29:43
Message-ID: 84AAD313D71B1D4F9EE20E739CC3B6EDE96F20@ATLANTIK-CL.intern.digame.de (view raw or flat)
Thread:
Lists: 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(dot)kocaman(at)digame(dot)de



> -----Original Message-----
> From: pgsql-odbc-owner(at)postgresql(dot)org 
> [mailto:pgsql-odbc-owner(at)postgresql(dot)org] On Behalf Of Jonathan Blitz
> Sent: Tuesday, April 18, 2006 4:57 PM
> To: pgsql-odbc(at)postgresql(dot)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
> 

pgsql-odbc by date

Next:From: Jonathan BlitzDate: 2006-04-18 14:57:03
Subject: PostgreSQL and Access - relink
Previous:From: noreplyDate: 2006-04-18 03:25:32
Subject: [ psqlodbc-Bugs-1000611 ] Empty exception thrown when no password supplied

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group