Re: PostgreSQL and Access - relink
am 18.04.2006 16:29:43 von Hakan KocamanHi,
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=20
sometimes put some effort to use the features of a real DBMS like PG.
The information about the tables(foeignname, localname,..) is in another ta=
ble.
Public Function create_tbl_defs(foreign_name As String, local_name As Strin=
g, Optional Aktuell As Boolean =3D 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 eingebunde=
n werden soll
If Aktuell Then
=20
If InStr(local_name, "_aktuellv") > 0 Then
' Wenn der Localname mit aktellv aufhören sollte wird der vor=
monat genommen
get_monext Monat, vormonat
foreign_name =3D foreign_name & vormonat
Else
' Der aktuelle Monat wird aus der tblmonate geholt
get_monext Monat
foreign_name =3D foreign_name & Monat
End If
End If
' Tabellen verknüpfen
db_inf =3D get_db_info(gl_db_name)
On Error Resume Next
strConnect =3D "ODBC;DRIVER=3D{PostgreSQL}" _
& ";SERVER=3D" & db_inf.ip _
& ";DATABASE=3D" & db_inf.dbname _
& ";UID=3D" & db_inf.uid _
& ";PWD=3D" & db_inf.pwd & ";"
Set db =3D CurrentDb()
Set tdf =3D db.CreateTableDef(local_name)
tdf.SourceTableName =3D foreign_name
tdf.connect =3D strConnect
db.TableDefs.Append tdf
db.TableDefs.Refresh
'Application.RefreshDatabaseWindow
If Err.Number =3D 3011 Then
If MsgBox("Die Tabelle " & foreign_name & " konnte nicht gefunden w=
erden." & _
vbCrLf & "Wahrscheinlich ist sie auf dem Server nicht vor=
handen." & _
vbCrLf & "Soll das Einbinden der Tabellen abgebrochen wer=
den (Ja) " & _
vbCrLf & "oder soll die Tabelle ignoriert werden (Nein)?"=
, vbYesNo, "Tabelle nicht gefunden") =3D vbYes _
Then
rtn =3D False
Else
rtn =3D True
End If
Else
If Err.Number <> 0 Then
GoTo create_tbl_defs_Error
Else
rtn =3D True
End If
End If
On Error GoTo 0
Application.RefreshDatabaseWindow
create_tbl_defs =3D rtn
On Error GoTo 0
Set tdf =3D Nothing
Set db =3D 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 =3D Nothing
Set db =3D Nothing
create_tbl_defs =3D 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=20
> [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
>=20
>=20
> I have an Access project linked to a PostgreSQL database=20
> (linked tables).
>=20
> I need to be able to switch dynamitically between one=20
> PostgreSQL database
> and another according to the request from the user.
>=20
> Anyone have any code that will do this?
>=20
> I assume it will require me to re-link all the tables.
>=20
> Jonathan Blitz
>=20
>=20
>=20
> ---------------------------(end of=20
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>=20
> http://www.postgresql.org/docs/faq
>=20
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match