Re: PostgreSQL and Access - relink

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
>