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
>
