MS Access adds always actual day in time defined postgres column

Uwe Nowag <[email protected]>
Newsgroups gmane.comp.db.postgresql.odbc
Message-ID <[email protected]> 
Hallo,

We are moving BackEnd tables from a large MS Access application to postgresql.

In one table we have a field "Zeit" defined in postgres as

time(0) without time zone DEFAULT ('now'::text)::time(0) without time zone,

The linked table inside Access shows the actual day in front of the time value. Using the 24-hour format for displaying time values in Access looks fine. But by entering the field, the day appears automatically.

If I import the same table via ODBC to excel, I have the same behavior: the day appears as prefix inside the time cell.
In the non Microsoft Application tool "ODBC query tool v1.39" (Jaime de los Hoyos) everything works fine.
The problem probably concerns only Microsoft products.


Cause I can't merge the time and the day field together in one timestamp field, I am looking for a workaround.


we use
MS Access 2010
ODBC: PostgreSQL Unicode 10.00.00 PSQLODBC35W.DLL 13.10.2017
PostgreSQL 10.1 as backend on ubuntu

thanks


Mit freundlichen Grüßen | Best regards | Cordialement

Uwe Nowag
Datenbanken/Software | Software Development

T +49 30 844168-502
F +49 30 844168-503
M +49 175 5821272
[email protected]


[cid:[email protected]]
Mastbau Gärtner GmbH | Naumannpark | Haus 12 | Wilhelm-Kabus-Straße 46 | 10829 Berlin | Germany
Amtsgericht Charlottenburg Nr. 96 HRB 20003 | USt.-Id.-Nr.: DE 136 728 360
Geschäftsführer: Jens Gockel, Karl-Heinz Simon
www.mbg-solutions.com <http://www.mbg-solutions.com>
image001.gif (image/gif, 2.4 KB) - not displayed
mylog_MSACCESS_6964_only_linked_table_view.log (application/octet-stream, 6.7 KB) 
[6664-0.000]   mylog.c[logs_on_off]274: mylog_on=1 qlog_on=1
[6664-0.000]	start_logging:Global.debug&commlog=1&1
[6664-0.000]psqlodbc.c[DllMain]130: exe name=MSACCESS
[6664-0.000]odbcapi30.[SQLAllocHandle]40: Entering
[6664-0.000] environ.c[PGAPI_AllocEnv]69: entering
[6664-0.000] environ.c[PGAPI_AllocEnv]88: leaving phenv=11F35338
[6664-0.000]odbcapi30.[SQLSetEnvAttr]477: Entering att=200,2
[6664-0.000]odbcapi30.[SQLAllocHandle]40: Entering
[6664-0.015]connection[PGAPI_AllocConnect]87: entering...
[6664-0.015]connection[PGAPI_AllocConnect]90: **** henv = 11F35338, conn = 11F84DB0
[6664-0.015] environ.c[EN_add_connection]589: entering self = 11F35338, conn = 11F84DB0
[6664-0.015] environ.c[EN_add_connection]613:        added at 0, conn->henv = 11F35338, conns[0]->henv = 11F35338
[6664-0.015]odbcapiw.c[SQLGetInfoW]417: Entering
[6664-0.015]    info.c[PGAPI_GetInfo]71: entering...fInfoType=77
[6664-0.015]    info.c[PGAPI_GetInfo]1030: p='03.51', len=0, value=0, cbMax=12
[6664-0.015]odbcapi30w[SQLSetConnectAttrW]95: Entering
[6664-0.015] pgapi30.c[PGAPI_SetConnectAttr]1657: entering for 11F84DB0: 115 00000000
[6664-0.015] pgapi30.c[PGAPI_SetConnectAttr]1672: the application is unicode
[6664-0.015]odbcapi30w[SQLSetConnectAttrW]95: Entering
[6664-0.015] pgapi30.c[PGAPI_SetConnectAttr]1657: entering for 11F84DB0: 103 00000014
[6664-0.015] options.c[PGAPI_SetConnectOption]307: entering fOption = 103 vParam = 20
[6664-0.015]odbcapi30w[SQLGetConnectAttrW]76: Entering
[6664-0.015] pgapi30.c[PGAPI_GetConnectAttr]403: entering 30002
[6664-0.015] options.c[PGAPI_GetConnectOption]494: entering...
[6664-0.015]connection[CC_set_error_statements]1222: entering self=11F84DB0
[6664-0.015]connection[CC_log_error]2543: CONN ERROR: func=PGAPI_GetConnectOption, desc='', errnum=205, errmsg='Unknown connect option (Get)'
[6664-0.015]connection[CC_log_error]2543: CONN ERROR: func=PGAPI_GetConnectOption, desc='fOption=30002', errnum=205, errmsg='Unknown connect option (Get)'
[6664-0.015]odbcapi30w[SQLSetConnectAttrW]95: Entering
[6664-0.015] pgapi30.c[PGAPI_SetConnectAttr]1657: entering for 11F84DB0: 30002 13BB2DA8
[6664-0.015] options.c[PGAPI_SetConnectOption]307: entering fOption = 30002 vParam = 331034024
[6664-0.015]connection[CC_set_error_statements]1222: entering self=11F84DB0
[6664-0.015]connection[CC_log_error]2543: CONN ERROR: func=PGAPI_SetConnectOption, desc='', errnum=205, errmsg='Unknown connect option (Set)'
[6664-0.015]win_unicod[ucs2_to_utf8]135: 13BB2DA8 ilen=-3  newlen=13 olen=13 utf8str=Microsoft Jet
[6664-0.015] options.c[PGAPI_SetConnectOption]458: Microsoft Jet !!!!
[6664-0.015]odbcapiw.c[SQLDriverConnectW]140: Entering
[6664-0.015]win_unicod[ucs2_to_utf8]135: 042D7240 ilen=-3  newlen=129 olen=129 utf8str=DSN=mbg_backend_test_10;DATABASE=mbg_test;SERVER=192.168.6.9;PORT=5433;CA=d;A7=100;B0=255;B1=8190;BI=0;C2=;CX=1c381008b;A1=7.4-0;
[6664-0.015] drvconn.c[PGAPI_DriverConnect]119: entering...
[6664-0.015] drvconn.c[PGAPI_DriverConnect]130: **** fDriverCompletion=3, connStrIn='DSN=mbg_backend_test_10;DATABASE=mbg_test;SERVER=192.168.6.9;PORT=5433;CA=d;A7=100;B0=255;B1=8190;BI=0;C2=;CX=1c381008b;A1=7.4-0;'
[6664-0.015]dlg_specif[CC_conninfo_init]1695: entering opt=2
[6664-0.015] drvconn.c[dconn_get_attributes]467: our_connect_string = 'DSN=mbg_backend_test_10;DATABASE=mbg_test;SERVER=192.168.6.9;PORT=5433;CA=d;A7=100;B0=255;B1=8190;BI=0;C2=;CX=1c381008b;A1=7.4-0;'
[6664-0.015]dlg_specif[getDSNinfo]881: entering DSN=mbg_backend_test_10 driver=&(null)
[6664-0.015]dlg_specif[getCiDefaults]776: entering
[6664-0.015]dlg_specif[getDSNinfo]902: drivername=PostgreSQL Unicode
[6664-0.015]dlg_specif[getDriversDefaults]856: 11F855C0 of the driver PostgreSQL Unicode
[6664-0.015]dlg_specif[get_Ci_Drivers]1411: comval=11F855C0 comval->extra_systable_prefixes = ''
[6664-0.015]dlg_specif[getDSNinfo]959: rollback_on_error=0
[6664-0.015]dlg_specif[getDSNinfo]1050: force_abbrev=0 bde=0 cvt_null_date=0
[6664-0.015]dlg_specif[get_Ci_Drivers]1327: setting ODBC.INI position of mbg_backend_test_10(11F855C0)
[6664-0.015]dlg_specif[get_Ci_Drivers]1411: comval=11F855C0 comval->extra_systable_prefixes = ''
[6664-0.015] drvconn.c[dconn_get_attributes]467: our_connect_string = 'DSN=mbg_backend_test_10;DATABASE=mbg_test;SERVER=192.168.6.9;PORT=5433;CA=d;A7=100;B0=255;B1=8190;BI=0;C2=;CX=1c381008b;A1=7.4-0;'
[6664-0.015]dlg_specif[copyConnAttributes]767: key='DSN' value='mbg_backend_test_10'
[6664-0.031]dlg_specif[copyConnAttributes]767: key='DATABASE' value='mbg_test'
[6664-0.031]dlg_specif[copyConnAttributes]767: key='SERVER' value='192.168.6.9'
[6664-0.031]dlg_specif[copyConnAttributes]767: key='PORT' value='5433'
[6664-0.031]dlg_specif[copyConnAttributes]699: key='CA' value='d' set to 'disable'
[6664-0.031]dlg_specif[copyConnAttributes]767: key='A7' value='100'
[6664-0.031]dlg_specif[copyConnAttributes]767: key='B0' value='255'
[6664-0.031]dlg_specif[copyConnAttributes]767: key='B1' value='8190'
[6664-0.031]dlg_specif[copyConnAttributes]767: key='BI' value='0'
[6664-0.031]dlg_specif[copyConnAttributes]767: key='C2' value=''
[6664-0.031]dlg_specif[copyConnAttributes]767: key='CX' value='1c381008b'
[6664-0.031]dlg_specif[copyConnAttributes]628: key='A1' value='7.4' rollback_on_error=0
[6664-66.629]   mylog.c[logs_on_off]274: mylog_on=1 qlog_on=1
[6664-66.629]connection[PGAPI_Disconnect]227: about to CC_cleanup
[6664-66.629]connection[CC_cleanup]661: entering self=11F84DB0
[6664-66.629]connection[CC_cleanup]669: [QLOG]PQfinish: 11F3D1D0
[6664-66.629]connection[CC_cleanup]674: after PQfinish
[6664-66.629]dlg_specif[CC_conninfo_init]1695: entering opt=1
[6664-66.629]connection[CC_cleanup]752: leaving
[6664-66.629]connection[PGAPI_Disconnect]232: done CC_cleanup
[6664-66.629]connection[PGAPI_Disconnect]233: leaving...
[6664-66.629]odbcapi30.[SQLFreeHandle]250: Entering
[6664-66.629]connection[PGAPI_FreeConnect]246: entering...hdbc=11F84DB0
[6664-66.629]connection[CC_Destructor]367: entering self=11F84DB0
[6664-66.629]connection[CC_cleanup]661: entering self=11F84DB0
[6664-66.629]connection[CC_cleanup]674: after PQfinish
[6664-66.629]dlg_specif[CC_conninfo_init]1695: entering opt=1
[6664-66.629]connection[CC_cleanup]752: leaving
[6664-66.629]connection[CC_Destructor]374: after CC_Cleanup
[6664-66.629]connection[CC_Destructor]387: after free statement holders
[6664-66.629]connection[CC_Destructor]398: leaving
[6664-66.629]connection[PGAPI_FreeConnect]264: leaving...
[6664-66.629]odbcapi30.[SQLFreeHandle]250: Entering
[6664-66.629] environ.c[PGAPI_FreeEnv]100: entering env=11F35338
[6664-66.629] environ.c[EN_Destructor]519: entering self=11F35338
[6664-66.629] environ.c[EN_Destructor]545: clearing conns count=128
[6664-66.629] environ.c[EN_Destructor]557: leaving rv=1
[6664-66.629] environ.c[PGAPI_FreeEnv]104:    ok
[6664-66.629]psqlodbc.c[DllMain]137: DETACHING PSQLODBC35W.DLL
screenshot.PNG (image/png, 5.1 KB) - not displayed
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.