Thread: Problems with ODBC and Access
Hi All, We have a MS Access 2000 database connecting to psgl 7.4.5 database over the Internet using ODBC 7.03.02.00 drivers. The connection works sometimes and then fails sporadically and unpredictably. When it fails, it could be just opening a table or it could be in a bound form. When it fails, we sometimes get an ODBC connection error. Sometimes, the table opens up with each cell filled with #delete#. I'm looking for some general troubleshooting guidelines. What makes this frustrating is that we have other experience using essentially the same techniques that have much greater stability. We are reasonably proficient with both pg and Access in general, and have naive to moderate experience, but until now good luck with the odbc drivers. I could say more about what we have checked or have suspected. Bandwidth? Table relations? Presence of text/memo fields? nature of ID fields? For initial post, I think I will just see what pops up. Very frustrating. Thanks for any tips, experience, suggestions. Michelle Michelle Murrain mmurrain at dbdes.com 617-248-0007 952-674-7253 fax Page: pageme@murrain.net AIM:pearlbear0 ICQ:129250575 Yahoo:michelle_murrain MSN:michelle@murrain.net Jabber: pearlbear@transactim.net In separateness lies the world's great misery; in compassion lies the world's true strength. -- Buddha
Hi Michelle, Did you look at the log files that the ODBC drivers generates (if turned on) and/or the PostgresQL log files? Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: J.Kraaijeveld@Askesis.nl web: www.askesis.nl
Joost,
I'm going to jump in here. I work with Michelle, but am having trouble
posting to the pg list. When we look at the log files, some times we see
strange things. I don't have them handy to cut and past here, but for
example, I can request all rows from a table (no "where" clause"). Then in
the log, I may see three queries. The first is correct. Then, there are two
that on their own have added a where clause that restricts the data by
saying record_id = {some random big integer having nothing to do with
nothing}. Then the table opens with every dell filled with #delete#.
--steve backman
At 2/15/2005 01:33 PM, you wrote:
>Hi Michelle,
>
>Did you look at the log files that the ODBC drivers generates (if turned
>on) and/or the PostgresQL log files?
>
>Groeten,
>
>Joost Kraaijeveld
>Askesis B.V.
>Molukkenstraat 14
>6524NB Nijmegen
>tel: 024-3888063 / 06-51855277
>fax: 024-3608416
>e-mail: J.Kraaijeveld@Askesis.nl
>web: www.askesis.nl
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
>
>
>
>--
>No virus found in this incoming message.
>Checked by AVG Anti-Virus.
>Version: 7.0.306 / Virus Database: 265.8.8 - Release Date: 2/14/2005
Hi Michelle,
Sorry to hear about your woes with Postgres and Access. I am
currently using Access 2K3 (previously Access 2K) with PG 7.4. It
sounds like you've covered these bases, but let's just run down the
checklist:
1) Fields have primary keys, and your linking process in Access is
recognizing those PKs.
2) In your ODBC DSN Row Versioning is checked (page 2 of Datasource
options)
3) The connecting user has permission to select, update, etc.
relevant sequences?
4) All clients are using Access 2K? I had a problem that took me a
while to diagnose - Access 2K3 will not properly handle memo fields if
the table links were made with Access 2000 - perhaps your problem could
be related?
5) If you turn on query logging, either on the client side or server
side or both, the SQL preparation and execution statements are being
properly formed?
Hope some of that helps.
Cheers,
Eric
Michelle Murrain wrote:
> Hi All,
>
> We have a MS Access 2000 database connecting to psgl 7.4.5 database
> over the Internet using ODBC 7.03.02.00 drivers. The connection works
> sometimes and then fails sporadically and unpredictably. When it
> fails, it could be just opening a table or it could be in a bound
> form. When it fails, we sometimes get an ODBC connection error.
> Sometimes, the table opens up with each cell filled with #delete#. I'm
> looking for some general troubleshooting guidelines.
>
> What makes this frustrating is that we have other experience using
> essentially the same techniques that have much greater stability. We
> are reasonably proficient with both pg and Access in general, and have
> naive to moderate experience, but until now good luck with the odbc
> drivers.
>
> I could say more about what we have checked or have suspected.
> Bandwidth? Table relations? Presence of text/memo fields? nature of ID
> fields? For initial post, I think I will just see what pops up. Very
> frustrating.
>
> Thanks for any tips, experience, suggestions.
>
> Michelle
>
> Michelle Murrain
> mmurrain at dbdes.com
> 617-248-0007
> 952-674-7253 fax
> Page: pageme@murrain.net
> AIM:pearlbear0 ICQ:129250575
> Yahoo:michelle_murrain MSN:michelle@murrain.net
> Jabber: pearlbear@transactim.net
>
> In separateness lies the world's great misery; in compassion lies the
> world's true strength. -- Buddha
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
Steve,
I don't really understand the internals, but is the integer being
searched in the where clause by chance "xmin"? I believe this is how
the ODBC driver figures out the appropriate row to return to Access - at
the very least it's what appears in linked tables when you enable Row
Versioning...
Also, does the log stuff vary when you use different versions of the driver?
EE
Steve News wrote:
> Joost,
>
> I'm going to jump in here. I work with Michelle, but am having trouble
> posting to the pg list. When we look at the log files, some times we
> see strange things. I don't have them handy to cut and past here, but
> for example, I can request all rows from a table (no "where" clause").
> Then in the log, I may see three queries. The first is correct. Then,
> there are two that on their own have added a where clause that
> restricts the data by saying record_id = {some random big integer
> having nothing to do with nothing}. Then the table opens with every
> dell filled with #delete#.
>
> --steve backman
>
> At 2/15/2005 01:33 PM, you wrote:
>
>> Hi Michelle,
>>
>> Did you look at the log files that the ODBC drivers generates (if
>> turned on) and/or the PostgresQL log files?
>>
>> Groeten,
>>
>> Joost Kraaijeveld
>> Askesis B.V.
>> Molukkenstraat 14
>> 6524NB Nijmegen
>> tel: 024-3888063 / 06-51855277
>> fax: 024-3608416
>> e-mail: J.Kraaijeveld@Askesis.nl
>> web: www.askesis.nl
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 8: explain analyze is your friend
>>
>>
>>
>> --
>> No virus found in this incoming message.
>> Checked by AVG Anti-Virus.
>> Version: 7.0.306 / Virus Database: 265.8.8 - Release Date: 2/14/2005
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
Hi, 1) I suggest you try another version of the driver, such as: ODBC 7.03.02.08 or ODBC 7.03.02.09 I use version 08, and never had any kind of problem with it. You can also try the 8.XX.XX.XX version, I'm sure. 2) Update the MDAC of your computer. http://msdn.microsoft.com/data/Default.aspx MDAC 2.8 works fine for me, from NT 4 to XP... 3) Check also your ODBC Timeout. I set it to 0 all the time: ----- Windows Registry Editor Version 5.00 [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ODBC] "ConnectionTimeout"=dword:00000000 ----- or ----- REGEDIT4 [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ODBC] "ConnectionTimeout"=dword:00000000 ----- 4) Finally, make sure that you refresh your linked tables when you make a change in the database. If you do not do it, resultsare sometimes unpredictable, but I noticed in practice that it depends on a number of factors, such as the OS used... Your results are welcome! Hope it will help. Philippe Lang Attik System -----Message d'origine----- De : pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] De la part de Michelle Murrain Envoyé : mardi, 15. février 2005 19:14 À : pgsql-odbc@postgresql.org Cc : Steve Backman Objet : [ODBC] Problems with ODBC and Access Hi All, We have a MS Access 2000 database connecting to psgl 7.4.5 database over the Internet using ODBC 7.03.02.00 drivers. Theconnection works sometimes and then fails sporadically and unpredictably. When it fails, it could be just opening a tableor it could be in a bound form. When it fails, we sometimes get an ODBC connection error. Sometimes, the table opensup with each cell filled with #delete#. I'm looking for some general troubleshooting guidelines. What makes this frustrating is that we have other experience using essentially the same techniques that have much greaterstability. We are reasonably proficient with both pg and Access in general, and have naive to moderate experience,but until now good luck with the odbc drivers. I could say more about what we have checked or have suspected. Bandwidth? Table relations? Presence of text/memo fields? nature of ID fields? For initial post, I think I will just seewhat pops up. Very frustrating. Thanks for any tips, experience, suggestions. Michelle Michelle Murrain mmurrain at dbdes.com 617-248-0007 952-674-7253 fax Page: pageme@murrain.net AIM:pearlbear0 ICQ:129250575 Yahoo:michelle_murrain MSN:michelle@murrain.net Jabber: pearlbear@transactim.net In separateness lies the world's great misery; in compassion lies the world's true strength. -- Buddha ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
As Michelle and Steve noted earlier, there are some of us experiencing
this anomaly with MS-Access, PostgresqlODBC + Postgres.
My configuration...
Gentoo Linux 2.4-something...
PostgreSQL 7.4.6
ODBC (8.00.00.04, but have tried many others)
I'm basically having the same problem. It doesn't seem to occur on all
tables. I am turning on query logging on my database tonight and will
report results based on my findings.
./Peter
Peter T. Bense - Teradata Certified Professional
(ptbense@gwm.sc.edu) - 803-777-9476
Database Administrator/Webmaster
Prevention Research Center
University of South Carolina
>>> Steve News <earlysteve@earthlink.net> 2/15/2005 2:32:19 PM
Joost,
I'm going to jump in here. I work with Michelle, but am having trouble
posting to the pg list. When we look at the log files, some times we
see
strange things. I don't have them handy to cut and past here, but for
example, I can request all rows from a table (no "where" clause"). Then
in
the log, I may see three queries. The first is correct. Then, there are
two
that on their own have added a where clause that restricts the data by
saying record_id = {some random big integer having nothing to do with
nothing}. Then the table opens with every dell filled with #delete#.
--steve backman
>>> Michelle Murrain <tech@murrain.net> 2/15/2005 1:13:39 PM >>>
We have a MS Access 2000 database connecting to psgl 7.4.5 database
over the Internet using ODBC 7.03.02.00 drivers. The connection works
sometimes and then fails sporadically and unpredictably. When it fails,
it could be just opening a table or it could be in a bound form. When
it fails, we sometimes get an ODBC connection error. Sometimes, the
table opens up with each cell filled with #delete#. I'm looking for
some general troubleshooting guidelines.