Is there a mysthical problem with psqlODBC + MSAccess + sequencesIN SCHEMAS ?

Hi,

I've got an Access-App using PG 8.4 in a LAN.
The systems dude said he updated to odbc 8.400 on all clients.
Though the same issue appeared with PG 8.3.7 and odbc 8.2.5 before.

Among others, there are 2 tables sitting in a schema.
Their primary keys are sequences.
I link those tables into access and the keys are shown as autovalue as
expected.

When Access tries to add a record, it complains that the relation
"versandinfos_versandinfo_id_seq" wouldn't exist and throws an error #7.

There are 2 solutions.
1) I move those tables to the public schema and relink within access.

2) I remove the sequence as default ... and reset it again at once.
That makes Access happy without relink.
Like:
ALTER TABLE versand.versandinfos ALTER COLUMN versandinfo_id SET DEFAULT 0;
ALTER TABLE versand.versandinfos ALTER COLUMN versandinfo_id SET DEFAULT
nextval('versand.versandinfos_versandinfo_id_seq'::regclass) ;

I can't point a finger to where this issue roots.
It's obviously no permission problem since I can enter records all the
time with psql or pgAdmin.
It appears when I used ALTER TABLE SET SCHEMA.
The recent "outbreak" might be triggered by a pg_restore since I sat up
a new db-machine yesterday and reimported the whole database.

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: Is there a mysthical problem with psqlODBC + MSAccess + sequences IN SCHEMAS ?

This is a multi-part message in MIME format.

------=_NextPart_000_0004_01CA1C1D.171398B0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

pgsql-odbc-owner@postgresql.org wrote:
> Hi,
>
> I've got an Access-App using PG 8.4 in a LAN.
> The systems dude said he updated to odbc 8.400 on all clients.
> Though the same issue appeared with PG 8.3.7 and odbc 8.2.5 before.
>
> Among others, there are 2 tables sitting in a schema.
> Their primary keys are sequences.
> I link those tables into access and the keys are shown as autovalue
> as expected.
>
> When Access tries to add a record, it complains that the relation
> "versandinfos_versandinfo_id_seq" wouldn't exist and throws an error
> #7.

Hi,

Have you checked if your ODBC user has enough rights on your sequence
"versandinfos_versandinfo_id_seq"? You can use pgAdmin to check that.

Regards,

------------------------------------------------------------ -----------
Philippe Lang Web : www.attiksystem.ch
Attik System Email : philippe.lang@attiksystem.ch
rte de la Fonderie 2 Phone : +41 26 422 13 75
1700 Fribourg Mobile : +41 79 351 49 94
Switzerland Fax : +41 26 422 13 76





------=_NextPart_000_0004_01CA1C1D.171398B0
Content-Type: application/x-pkcs7-signature;
name="smime.p7s"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
filename="smime.p7s"

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEH AQAAoIIPWzCCBDIw
ggMaoAMCAQICAQEwDQYJKoZIhvcNAQEFBQAwezELMAkGA1UEBhMCR0IxGzAZ BgNVBAgMEkdyZWF0
ZXIgTWFuY2hlc3RlcjEQMA4GA1UEBwwHU2FsZm9yZDEaMBgGA1UECgwRQ29t b2RvIENBIExpbWl0
ZWQxITAfBgNVBAMMGEFBQSBDZXJ0aWZpY2F0ZSBTZXJ2aWNlczAeFw0wNDAx MDEwMDAwMDBaFw0y
ODEyMzEyMzU5NTlaMHsxCzAJBgNVBAYTAkdCMRswGQYDVQQIDBJHcmVhdGVy IE1hbmNoZXN0ZXIx
EDAOBgNVBAcMB1NhbGZvcmQxGjAYBgNVBAoMEUNvbW9kbyBDQSBMaW1pdGVk MSEwHwYDVQQDDBhB
QUEgQ2VydGlmaWNhdGUgU2VydmljZXMwggEiMA0GCSqGSIb3DQEBAQUAA4IB DwAwggEKAoIBAQC+
QJ30buHqdoccTUVEjr5GyIMGncEq/hgfjuQC+vOrXVCKFjELmgbQxXAizUkt VGPMtm5oRgtT6stM
JMC8ck7q8RWu9FSaEgrDerIzYOLaiVXzIljz3tzP74OGooyUT59o8piQRoQn x3a/48w1LIteB2Rl
gsBIsKiR+WGfdiBQqJHHZrXreGIDVvCKGhPqMaMeoJn9OPb2JzJYbwf1a7j7 FCuvt6rM1mNfc4za
BZmoOKjLF3g2UazpnvR4Oo3PD9lC4pgMqy+fDgHe75+ZSfEt36x0TRuYtUfF 5SnR+ZAYx2KcvoPH
Jns+iiXHwN2d5jVoECCdj9je0sOEnA1e6C/JAgMBAAGjgcAwgb0wHQYDVR0O BBYEFKARCiM+lvEH
7OKvKe+CpX/QMKS0MA4GA1UdDwEB/wQEAwIBBjAPBgNVHRMBAf8EBTADAQH/ MHsGA1UdHwR0MHIw
OKA2oDSGMmh0dHA6Ly9jcmwuY29tb2RvY2EuY29tL0FBQUNlcnRpZmljYXRl U2VydmljZXMuY3Js
MDagNKAyhjBodHRwOi8vY3JsLmNvbW9kby5uZXQvQUFBQ2VydGlmaWNhdGVT ZXJ2aWNlcy5jcmww
DQYJKoZIhvcNAQEFBQADggEBAAhW/ALwm+j/pPrWe8ZEgM5PxMX2AFjMpra8 FEloBHbo5u5d7AIP
YNaNUBhPJk4B4+awpe6/vHRUQb/9/BK4x09a9IlgBX9gtwVK8/bxwr/EuXSG ti19a8zS80bdL8bg
asPDNAMsfZbdWsIOpwqZwQWLqwwv81w6z2w3VQmH3lNAbFjv/LarZW4E9hvc POBaFcae2fFZSDAh
ZQNs7Okhc+ybA6HgN62gFRiP+roCzqcsqRATLNTlCCarIpdg+JBedNSimlO9 8qlo4KJuwtdssaMP
nr/raOdW8q7y4ys4OgmBtWuF174t7T8at7Jj4vViLILUagBBUPE5g5+V6TaW mG4wggTdMIIDxaAD
AgECAhBxkvvmGV+sTRKFdHE0ohinMA0GCSqGSIb3DQEBBQUAMHsxCzAJBgNV BAYTAkdCMRswGQYD
VQQIDBJHcmVhdGVyIE1hbmNoZXN0ZXIxEDAOBgNVBAcMB1NhbGZvcmQxGjAY BgNVBAoMEUNvbW9k
byBDQSBMaW1pdGVkMSEwHwYDVQQDDBhBQUEgQ2VydGlmaWNhdGUgU2Vydmlj ZXMwHhcNMDQwMTAx
MDAwMDAwWhcNMjgxMjMxMjM1OTU5WjCBrjELMAkGA1UEBhMCVVMxCzAJBgNV BAgTAlVUMRcwFQYD
VQQHEw5TYWx0IExha2UgQ2l0eTEeMBwGA1UEChMVVGhlIFVTRVJUUlVTVCBO ZXR3b3JrMSEwHwYD
VQQLExhodHRwOi8vd3d3LnVzZXJ0cnVzdC5jb20xNjA0BgNVBAMTLVVUTi1V U0VSRmlyc3QtQ2xp
ZW50IEF1dGhlbnRpY2F0aW9uIGFuZCBFbWFpbDCCASIwDQYJKoZIhvcNAQEB BQADggEPADCCAQoC
ggEBALI5haTyfatBO2JGN67NwWB1vDll+UoaR6K5zEjMapjVTTUZuaRC5c5J 4oovHnzSMQfHTrSD
ZJ0uKdWiZMSFvYVRNXmkTmiQexx6pJKoF/KYFfKTzMmkMpW7DE8wvZigC4vl bhuiRvp4vKJvq1le
pS/Pytptqi/rrKGzaqq3Lmc1i3nhHmmI4uZGzaCl6r4LznY6eg6b6vzaJ1s9 cx8i5khhxkzzabGo
Lhu21DEgLLyCio6kDqXXiUP8FlqvHXHXEVnauocNr/rz4cLwpMVnjNbWVDre CqS6A3ezZcj9HtN0
YqoYymiTHqGFfvVHZcv4TVcodNI0/zC27vZiMBSMLOsCAwEAAaOCAScwggEj MB8GA1UdIwQYMBaA
FKARCiM+lvEH7OKvKe+CpX/QMKS0MB0GA1UdDgQWBBSJgmd9xJ0mcABLtFBI fN49rgRufTAOBgNV
HQ8BAf8EBAMCAQYwDwYDVR0TAQH/BAUwAwEB/zAdBgNVHSUEFjAUBggrBgEF BQcDAgYIKwYBBQUH
AwQwEQYDVR0gBAowCDAGBgRVHSAAMHsGA1UdHwR0MHIwOKA2oDSGMmh0dHA6 Ly9jcmwuY29tb2Rv
Y2EuY29tL0FBQUNlcnRpZmljYXRlU2VydmljZXMuY3JsMDagNKAyhjBodHRw Oi8vY3JsLmNvbW9k
by5uZXQvQUFBQ2VydGlmaWNhdGVTZXJ2aWNlcy5jcmwwEQYJYIZIAYb4QgEB BAQDAgEGMA0GCSqG
SIb3DQEBBQUAA4IBAQCdlcs8uH6lCcQevwvCx3aOOTyUxhCqTwzJ4KuEXYlU 4GU7820cfDcsJVRf
liH8N4SRnRXcFE+Bz1Qda2xFYMct+ZdRTPlmyjyggoymyPDi6dRK+ew/Vsnd dozDggFPbADzHhph
dARHA6nGQFeRvGUixSdnT1fbZFrZjR+6hi/0Bq6cae3p9M8pF9jgSp8aIC+X TFG7RgfEijdOIOMJ
MWjHnsSLneh+EbwyaBCWEZhE2CpRYE2I63Q630MGMsg5Vow6EVLTQaRDA/Tt 7zMn2zngFE4mydj1
OeKJuJNdtykmQeqzm66D/Hd1yujKtf7iZUpjPkTE0MNeh3OpmByvfxV/MIIG QDCCBSigAwIBAgIR
AJlNdft0lUPOmrqyZPUmRN4wDQYJKoZIhvcNAQEFBQAwga4xCzAJBgNVBAYT AlVTMQswCQYDVQQI
EwJVVDEXMBUGA1UEBxMOU2FsdCBMYWtlIENpdHkxHjAcBgNVBAoTFVRoZSBV U0VSVFJVU1QgTmV0
d29yazEhMB8GA1UECxMYaHR0cDovL3d3dy51c2VydHJ1c3QuY29tMTYwNAYD VQQDEy1VVE4tVVNF
UkZpcnN0LUNsaWVudCBBdXRoZW50aWNhdGlvbiBhbmQgRW1haWwwHhcNMDkw NzMwMDAwMDAwWhcN
MTAwNzMwMjM1OTU5WjCB5TE1MDMGA1UECxMsQ29tb2RvIFRydXN0IE5ldHdv cmsgLSBQRVJTT05B
IE5PVCBWQUxJREFURUQxRjBEBgNVBAsTPVRlcm1zIGFuZCBDb25kaXRpb25z IG9mIHVzZTogaHR0
cDovL3d3dy5jb21vZG8ubmV0L3JlcG9zaXRvcnkxHzAdBgNVBAsTFihjKTIw MDMgQ29tb2RvIExp
bWl0ZWQxFjAUBgNVBAMTDVBoaWxpcHBlIExhbmcxKzApBgkqhkiG9w0BCQEW HHBoaWxpcHBlLmxh
bmdAYXR0aWtzeXN0ZW0uY2gwggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEK AoIBAQDDUM7mdHaN
zB0dbYhASRVJhFDV3fVPRjS9nIiNcLYd2azvgpYMR5AczR33VGnuy6QxexFO VvQEgRVrnY0WAJ1x
QmBL9MFP57sUoawLWj8qt4hqflPFLcV8USZs4ZVHn0OcclHRZfzgidpUAEWL zlxMJYdwsDckSFWG
DOSByLrl+645kkdgD6yLO2gB5Rvwu74LR4jHsfAkEzAPFJldrseVkiXsyS8a noBjFkBr+GIZ3ZJi
Dj26Mf73m4d6JGdTU5giM+pZEAvlpsbHX1yhhh+gYlu6efPtCUgLygPAh+C8 h+1zSSmYhZuiKKVo
TKP8WYOBRWku9P9g/0TGy0Vstrx7AgMBAAGjggIeMIICGjAfBgNVHSMEGDAW gBSJgmd9xJ0mcABL
tFBIfN49rgRufTAdBgNVHQ4EFgQUflMigiTO4TL/ZjNOkubshdCiGpAwDgYD VR0PAQH/BAQDAgWg
MAwGA1UdEwEB/wQCMAAwIAYDVR0lBBkwFwYIKwYBBQUHAwQGCysGAQQBsjEB AwUCMBEGCWCGSAGG
+EIBAQQEAwIFIDBGBgNVHSAEPzA9MDsGDCsGAQQBsjEBAgEBATArMCkGCCsG AQUFBwIBFh1odHRw
czovL3NlY3VyZS5jb21vZG8ubmV0L0NQUzCBpQYDVR0fBIGdMIGaMEygSqBI hkZodHRwOi8vY3Js
LmNvbW9kb2NhLmNvbS9VVE4tVVNFUkZpcnN0LUNsaWVudEF1dGhlbnRpY2F0 aW9uYW5kRW1haWwu
Y3JsMEqgSKBGhkRodHRwOi8vY3JsLmNvbW9kby5uZXQvVVROLVVTRVJGaXJz dC1DbGllbnRBdXRo
ZW50aWNhdGlvbmFuZEVtYWlsLmNybDBsBggrBgEFBQcBAQRgMF4wNgYIKwYB BQUHMAKGKmh0dHA6
Ly9jcnQuY29tb2RvY2EuY29tL1VUTkFBQUNsaWVudENBLmNydDAkBggrBgEF BQcwAYYYaHR0cDov
L29jc3AuY29tb2RvY2EuY29tMCcGA1UdEQQgMB6BHHBoaWxpcHBlLmxhbmdA YXR0aWtzeXN0ZW0u
Y2gwDQYJKoZIhvcNAQEFBQADggEBAAEAlmz24KSkuDlEs5Py/RO7PgFXuqD8 NdNe0FeFDUL9Mgdz
n/A6HHozwWGA5aKU5jyT8W+nzzhQEf1lvdNHPTIfv/OTlUDqzjf7V/h+ADMi RQMDEH4n1op7HBZU
9m2/hHGBlrwJdqYz6Oug4Os3LcKYmkY9VaWtigWwv2TZwkhV3W6rQ9PdlMgc dzMDs+u/PILJuRqi
bWl1oxHAMHKLO/TRbFcXfSHy82XbB9kUb4NTPRBepQ+u5UZvxsrc89bf9qis EBbKHmlaKtu5H22W
cAqDouWelwFVy6oedRJ+ObauqRHRcMD8Bgs0pDXWRZ/nDeyP2f5nVw/heUaX P5AEGfoxggRoMIIE
ZAIBATCBxDCBrjELMAkGA1UEBhMCVVMxCzAJBgNVBAgTAlVUMRcwFQYDVQQH Ew5TYWx0IExha2Ug
Q2l0eTEeMBwGA1UEChMVVGhlIFVTRVJUUlVTVCBOZXR3b3JrMSEwHwYDVQQL ExhodHRwOi8vd3d3
LnVzZXJ0cnVzdC5jb20xNjA0BgNVBAMTLVVUTi1VU0VSRmlyc3QtQ2xpZW50 IEF1dGhlbnRpY2F0
aW9uIGFuZCBFbWFpbAIRAJlNdft0lUPOmrqyZPUmRN4wCQYFKw4DAhoFAKCC AngwGAYJKoZIhvcN
AQkDMQsGCSqGSIb3DQEHATAcBgkqhkiG9w0BCQUxDxcNMDkwODEzMTE1MTAw WjAjBgkqhkiG9w0B
CQQxFgQUBc3D8rj9XJ+DoO/H+Z+z/IA4Y4gwZwYJKoZIhvcNAQkPMVowWDAK BggqhkiG9w0DBzAO
BggqhkiG9w0DAgICAIAwDQYIKoZIhvcNAwICAUAwBwYFKw4DAgcwDQYIKoZI hvcNAwICASgwBwYF
Kw4DAhowCgYIKoZIhvcNAgUwgdUGCSsGAQQBgjcQBDGBxzCBxDCBrjELMAkG A1UEBhMCVVMxCzAJ
BgNVBAgTAlVUMRcwFQYDVQQHEw5TYWx0IExha2UgQ2l0eTEeMBwGA1UEChMV VGhlIFVTRVJUUlVT
VCBOZXR3b3JrMSEwHwYDVQQLExhodHRwOi8vd3d3LnVzZXJ0cnVzdC5jb20x NjA0BgNVBAMTLVVU
Ti1VU0VSRmlyc3QtQ2xpZW50IEF1dGhlbnRpY2F0aW9uIGFuZCBFbWFpbAIR AJlNdft0lUPOmrqy
ZPUmRN4wgdcGCyqGSIb3DQEJEAILMYHHoIHEMIGuMQswCQYDVQQGEwJVUzEL MAkGA1UECBMCVVQx
FzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5MR4wHAYDVQQKExVUaGUgVVNFUlRS VVNUIE5ldHdvcmsx
ITAfBgNVBAsTGGh0dHA6Ly93d3cudXNlcnRydXN0LmNvbTE2MDQGA1UEAxMt VVROLVVTRVJGaXJz
dC1DbGllbnQgQXV0aGVudGljYXRpb24gYW5kIEVtYWlsAhEAmU11+3SVQ86a urJk9SZE3jANBgkq
hkiG9w0BAQEFAASCAQCawR6kRXdzFGSdnntrr7NRuQ43lLe/U5MqZmgHVtxT WCHfkVR7ziLVbjCN
zdKmktHPtjw3W96MwnCdTchj267P8YMnH+XWgMIAL2hZRfAUiVJdhhYJ5Y1g sARoOI7AnoM/bLzt
EoQikgfeYbAJmLIdQd2fmiophRaPoTRIhyza467uG6mpZqkDgS6rkHBE76pK Hji8Ww7CG9kkK/Uz
hai2zzCaGNgwPDBAIN6iUnQaK8lcOZnoNzgyBWYA5hrpbDLPGVPgsbgNIzEI 4iA4EUf9IGYD00tB
SZisHbEW30t116N2wfqoRSkNl5c84gVeSQ8mrGeOMSt/QMInr2DkO1GxAAAA AAAA

------=_NextPart_000_0004_01CA1C1D.171398B0--

Re: Is there a mysthical problem with psqlODBC + MSAccess+ sequences IN SCHEMAS ?

Philippe Lang schrieb:
> pgsql-odbc-owner@postgresql.org wrote:
>
>> Hi,
>>
>> I've got an Access-App using PG 8.4 in a LAN.
>> The systems dude said he updated to odbc 8.400 on all clients.
>> Though the same issue appeared with PG 8.3.7 and odbc 8.2.5 before.
>>
>> Among others, there are 2 tables sitting in a schema.
>> Their primary keys are sequences.
>> I link those tables into access and the keys are shown as autovalue
>> as expected.
>>
>> When Access tries to add a record, it complains that the relation
>> "versandinfos_versandinfo_id_seq" wouldn't exist and throws an error
>> #7.
>>
>
> Hi,
>
> Have you checked if your ODBC user has enough rights on your sequence
> "versandinfos_versandinfo_id_seq"? You can use pgAdmin to check that
>
By desperation I made GRANT ... TO public.
This can't be the solution since I don't touch the sequence in my
workaround.
I just set the default of the key twice. This affects only the one
collumn in the table but not the seq.

I found this accidently when I wanted to reduce the error sources some
time ago bye cutting the sequence off.
As it worked with manually finding the next key, I reattached the
sequence as before and expected to get the error again but it was gone.
There must be a interference between odbc/access and sequences within
schemas.

As I mentioned. The problem goes away when I move the table (inluding
the seq.) to the public schema and reappeares when I push it in the
schema again.
Obviously some element in the dataaccess chain "forgets" to store or use
the schema to address the seq.




--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc