Thread: Strange ODBC behavior.
I had a server have a power outage on one of my PostgreSQL servers yesterday and am having a strange ODBC issue now - not sure if they are related occurances though. I have a table called billing_code and one called models in my database. If I connect via psql to the database with my standard db user I can see both of these tables as well as the data contained within. If I attempt to link these tables from MS Access, the billing_code one returns "The Microsoft Jet database engine could not find the object 'public.billing_code'. Make sure the object exists and that you spell its name and the path name correctly" - this error despite the fact that I had to physically select the table from a list it presented me when I went into the linked table manager. If I link the models table, it links fine, but when I attempt to open it, I just get #Deleted in every column of every row for about 54,000 rows (the total number in the table) The same problem happens with some of the other tables in the database - some will get the first problem, some will get the second problem, and some will link and load data without issue. I was running odbc driver 8.02.02, I've upgraded to 8.02.04 - just in case there was a problem with the driver getting corrupted but the problem persists. Can anyone offer any thoughts or suggestions on what the problem might be? Thanks in advance, Paul. -- Paul Lambert Database Administrator AutoLedgers
Paul Lambert wrote: > I had a server have a power outage on one of my PostgreSQL servers > yesterday and am having a strange ODBC issue now - not sure if they are > related occurances though. > > I have a table called billing_code and one called models in my database. > If I connect via psql to the database with my standard db user I can see > both of these tables as well as the data contained within. > > If I attempt to link these tables from MS Access, the billing_code one > returns "The Microsoft Jet database engine could not find the object > 'public.billing_code'. Make sure the object exists and that you spell > its name and the path name correctly" - this error despite the fact that > I had to physically select the table from a list it presented me when I > went into the linked table manager. > > If I link the models table, it links fine, but when I attempt to open > it, I just get #Deleted in every column of every row for about 54,000 > rows (the total number in the table) > > The same problem happens with some of the other tables in the database - > some will get the first problem, some will get the second problem, and > some will link and load data without issue. > > I was running odbc driver 8.02.02, I've upgraded to 8.02.04 - just in > case there was a problem with the driver getting corrupted but the > problem persists. > > Can anyone offer any thoughts or suggestions on what the problem might be? > > Thanks in advance, > Paul. > I've done a full pg_dump, deleted the database and tablespaces, recreated everything, restored the data and the same behavior is being exhibited - at least as far as returning #Deleted in some of the tables. Users with an Access database that already has the table linked don't have a problem accessing the data, but those adding a new link to the table do. Any thoughts on this or is it more of an access issue? -- Paul Lambert Database Administrator AutoLedgers
Paul Lambert wrote: > Paul Lambert wrote: >> I had a server have a power outage on one of my PostgreSQL servers >> yesterday and am having a strange ODBC issue now - not sure if they >> are related occurances though. >> >> I have a table called billing_code and one called models in my >> database. If I connect via psql to the database with my standard db >> user I can see both of these tables as well as the data contained >> within. >> >> If I attempt to link these tables from MS Access, the billing_code >> one returns "The Microsoft Jet database engine could not find the >> object 'public.billing_code'. Make sure the object exists and that >> you spell its name and the path name correctly" - this error despite >> the fact that I had to physically select the table from a list it >> presented me when I went into the linked table manager. >> >> If I link the models table, it links fine, but when I attempt to open >> it, I just get #Deleted in every column of every row for about 54,000 >> rows (the total number in the table) >> >> The same problem happens with some of the other tables in the >> database - some will get the first problem, some will get the second >> problem, and some will link and load data without issue. >> >> I was running odbc driver 8.02.02, I've upgraded to 8.02.04 - just in >> case there was a problem with the driver getting corrupted but the >> problem persists. >> >> Can anyone offer any thoughts or suggestions on what the problem >> might be? >> >> Thanks in advance, >> Paul. >> > > I've done a full pg_dump, deleted the database and tablespaces, > recreated everything, restored the data and the same behavior is being > exhibited - at least as far as returning #Deleted in some of the tables. > > Users with an Access database that already has the table linked don't > have a problem accessing the data, but those adding a new link to the > table do. > > Any thoughts on this or is it more of an access issue? 1) Could you post here CREATE script for one of the tables with problem you descrived? #delete message appears when something happens to the primary keys. 2) What is the version of your MS Access? PostgreSQL? Unicode or ANSI driver version? 3) Please, have a look on the linked table structure as it's recognised by MS Access. And post it here. Andrei.
On Friday 03 August 2007 1:38 am, Paul Lambert wrote: > Paul Lambert wrote: > > I had a server have a power outage on one of my PostgreSQL servers > > yesterday and am having a strange ODBC issue now - not sure if they are > > related occurances though. > > > > I have a table called billing_code and one called models in my database. > > If I connect via psql to the database with my standard db user I can see > > both of these tables as well as the data contained within. > > > > If I attempt to link these tables from MS Access, the billing_code one > > returns "The Microsoft Jet database engine could not find the object > > 'public.billing_code'. Make sure the object exists and that you spell > > its name and the path name correctly" - this error despite the fact that > > I had to physically select the table from a list it presented me when I > > went into the linked table manager. > > > > If I link the models table, it links fine, but when I attempt to open > > it, I just get #Deleted in every column of every row for about 54,000 > > rows (the total number in the table) > > > > The same problem happens with some of the other tables in the database - > > some will get the first problem, some will get the second problem, and > > some will link and load data without issue. > > > > I was running odbc driver 8.02.02, I've upgraded to 8.02.04 - just in > > case there was a problem with the driver getting corrupted but the > > problem persists. > > > > Can anyone offer any thoughts or suggestions on what the problem might > > be? > > > > Thanks in advance, > > Paul. > > I've done a full pg_dump, deleted the database and tablespaces, > recreated everything, restored the data and the same behavior is being > exhibited - at least as far as returning #Deleted in some of the tables. > > Users with an Access database that already has the table linked don't > have a problem accessing the data, but those adding a new link to the > table do. > > Any thoughts on this or is it more of an access issue? I believe this is more of an Access/ODBC issue. I recently had a similar problem with a Foxpro ODBC setup and Access. In that case I had to renter the path information to get Access to see the data in the tables. I have also found it necessary to reboot the machine with Access on it for changes to be seen. -- Adrian Klaver aklaver@comcast.net
Andrei Kovalevski wrote: >> Any thoughts on this or is it more of an access issue? > 1) Could you post here CREATE script for one of the tables with problem > you descrived? #delete message appears when something happens to the > primary keys. > 2) What is the version of your MS Access? PostgreSQL? Unicode or ANSI > driver version? > 3) Please, have a look on the linked table structure as it's recognised > by MS Access. And post it here. > > > Andrei. > > > I believe I have found the problem. The primary key fields are defined in PG as type text, but MS access is bringing them across as type "memo" - whatever that is. It seems to be bringing most text type fields across like this. I changed them to varchar on one of the tables I'm having a problem with, relinked and they are coming across with a type of text on the fields now and I can see the data without issue. Strange... has anyone see that before? -- Paul Lambert Database Administrator AutoLedgers
On Sunday 05 August 2007 3:26 pm, Paul Lambert wrote: > Andrei Kovalevski wrote: > >> Any thoughts on this or is it more of an access issue? > > > > 1) Could you post here CREATE script for one of the tables with problem > > you descrived? #delete message appears when something happens to the > > primary keys. > > 2) What is the version of your MS Access? PostgreSQL? Unicode or ANSI > > driver version? > > 3) Please, have a look on the linked table structure as it's recognised > > by MS Access. And post it here. > > > > > > Andrei. > > I believe I have found the problem. > > The primary key fields are defined in PG as type text, but MS access is > bringing them across as type "memo" - whatever that is. > > It seems to be bringing most text type fields across like this. > > I changed them to varchar on one of the tables I'm having a problem > with, relinked and they are coming across with a type of text on the > fields now and I can see the data without issue. > > Strange... has anyone see that before? Actually this has been noted before. Search Google or psql-odbc archives for references. There are some changes you can make to the ODBC settings to help. Unfortunately, I cannot remember them off the top of my head. I only bring this up because I saw your post on the general list about changing the type of 600 text columns. You might find it easier to change the behavior of the ODBC driver. -- Adrian Klaver aklaver@comcast.net
Adrian Klaver wrote: > Actually this has been noted before. Search Google or psql-odbc archives for > references. There are some changes you can make to the ODBC settings to help. > Unfortunately, I cannot remember them off the top of my head. I only bring > this up because I saw your post on the general list about changing the type > of 600 text columns. You might find it easier to change the behavior of the > ODBC driver. I should have thought about checking the ODBC settings before. There is a setting called "Text as LongVarChar" - I've unticked that and now text fields are coming through as text. Thanks for the pointers. -- Paul Lambert Database Administrator AutoLedgers
Paul Lambert wrote: > Paul Lambert wrote: >> I had a server have a power outage on one of my PostgreSQL servers >> yesterday and am having a strange ODBC issue now - not sure if they >> are related occurances though. >> >> I have a table called billing_code and one called models in my >> database. If I connect via psql to the database with my standard db >> user I can see both of these tables as well as the data contained within. >> >> If I attempt to link these tables from MS Access, the billing_code one >> returns "The Microsoft Jet database engine could not find the object >> 'public.billing_code'. Make sure the object exists and that you spell >> its name and the path name correctly" - this error despite the fact >> that I had to physically select the table from a list it presented me >> when I went into the linked table manager. >> >> If I link the models table, it links fine, but when I attempt to open >> it, I just get #Deleted in every column of every row for about 54,000 >> rows (the total number in the table) >> >> The same problem happens with some of the other tables in the database >> - some will get the first problem, some will get the second problem, >> and some will link and load data without issue. >> >> I was running odbc driver 8.02.02, I've upgraded to 8.02.04 - just in >> case there was a problem with the driver getting corrupted but the >> problem persists. >> >> Can anyone offer any thoughts or suggestions on what the problem might >> be? >> >> Thanks in advance, >> Paul. >> > > I've done a full pg_dump, deleted the database and tablespaces, > recreated everything, restored the data and the same behavior is being > exhibited - at least as far as returning #Deleted in some of the tables. > > Users with an Access database that already has the table linked don't > have a problem accessing the data, but those adding a new link to the > table do. > > Any thoughts on this or is it more of an access issue? > > I'm still having the problem with a few of the tables reporting: "The Microsoft Jet database engine could not find the object public.billing_code'. Make sure the object exists and that you spell its name and the path name correctly" When I attempt to link them in MS access. It seems that any tables in my database that contain an underscore in the table name are giving this problem. Can't see any settings in the DSN that might fix this though. The psqlodbc_<pid>.log file has the following: select n.nspname, c.relname, a.attname, a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull, c.relhasrules, c.relkind, c.oid, d.adsrc from (((pg_catalog.pg_class c inner join pg_catalog.pg_namespace n on n.oid = c.relnamespace and c.relname like 'billing\\_code' and n.nspname like 'public') inner join pg_catalog.pg_attribute a on (not a.attisdropped) and a.attnum > 0 and a.attrelid = c.oid) inner join pg_catalog.pg_type t on t.oid = a.atttypid) left outer join pg_attrdef d on a.atthasdef and d.adrelid = a.attrelid and d.adnum = a.attnum order by n.nspname, c.relname, attnum' Note the 'billing\\_code' in there. If I run this query in psql I get no rows. If I change it to 'billing\_code' or 'billing_code' (taking out one or both of the backslashes) I get correct results. Seems to me that the odbc driver is not handling underscores in the table name correctly - perhaps a conflict with it and me having turned on standard_conforming_strings? Any thoughts? -- Paul Lambert Database Administrator AutoLedgers