473,289 Members | 1,729 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

odbc call failed

i am using vb 6 and postgresql as the back end. when running the function in the vb , it shows the error message of odbc call failed. but when i run the same function in the postgresql then it runs easily. can anyone suggest how can i increase the execution time in the odbc since the database is too huge,
Mar 22 '07 #1
11 9775
michaelb
534 Expert 512MB
There's not enough information here to understand the nature of your problem.
Please post the relevant excerpt from your code and the error message it generates.
Mar 22 '07 #2
coolminded
137 100+
i am using the function
Expand|Select|Wrap|Line Numbers
  1.  
  2. CREATE OR REPLACE FUNCTION fxn_name("varchar", "varchar", "varchar")
  3.   RETURNS "varchar" AS
  4. $BODY$
  5.         DECLARE
  6.           i RECORD;
  7.         bal numeric(12,2);
  8.         BEGIN
  9.           INSERT INTO table1
  10.         VALUES(
  11.           1,
  12.           current_date,
  13.           current_date,
  14.           current_date,
  15.                   current_date,
  16.           $3,
  17.           'narration',
  18.           '',
  19.           current_date,
  20.           $2,
  21.           '',
  22.           'N',
  23.           'J');
  24.         FOR i IN SELECT sth FROM table2  WHERE level_no = 3                             
  25.         LOOP
  26.         IF bal > 0 THEN
  27.         INSERT INTO table3
  28.         VALUES(
  29.           1,
  30.           current_date,
  31.           i.acct_cd,
  32.           'narration',
  33.           $2,
  34.           'D',
  35.           '',
  36.           bal);
  37.                     ELSE
  38.          IF bal <0 THEN
  39.                  INSERT INTO table3
  40.         VALUES(
  41.           1,
  42.           current_date,
  43.           i.acct_cd,
  44.           'narration',
  45.           $2,
  46.           'C',
  47.           '',
  48.           bal*-1);
  49.         ELSE
  50.         END IF;
  51.         END IF;
  52.         END LOOP;
  53.         RETURN 'a';
  54.         END;$BODY$
  55.   LANGUAGE 'plpgsql' VOLATILE;
  56.  
when i call this function in vb it shows the error message of
run time error '3146', odbc call failed since it has huge database
but when i run the same code in the postgres it executes quite easily

is there any suggestions of this problem???
Mar 23 '07 #3
michaelb
534 Expert 512MB
I don't use neither VB or ODBC, but a Google searh on "run time error '3146', odbc call failed"
returned quite a few references.
It appears this problem may be related to your ODBC driver, did you check if a newer version is available?
Some other cases I saw were related to query not returning the number of rows affected, apparently the driver was expecting this.

I would consider these steps:

1. I think you were concerned with performance and possible timeout error due to the long running query.
Create some test function similar to yours, but make sure it does little and runs fast.
See if you can execute it from your app.

2. Modify this function to return a number of affected rows, perhaps some dummy integer.

3. Instead of the function, make your app. to run SQL query performing a similar operation, see what happens.

4. Check for updates on the ODBC driver you are using, or maybe find a different driver altogether.

5. Spend some time searching net with Google

Here are couple links that could be relevant.

http://www.pcreview.co.uk/forums/thread-1865044.php
http://www.vbforums.com/archive/index.php/t-338252.html
Mar 23 '07 #4
coolminded
137 100+
I don't use neither VB or ODBC, but a Google searh on "run time error '3146', odbc call failed"
returned quite a few references.
It appears this problem may be related to your ODBC driver, did you check if a newer version is available?
Some other cases I saw were related to query not returning the number of rows affected, apparently the driver was expecting this.

I would consider these steps:

1. I think you were concerned with performance and possible timeout error due to the long running query.
Create some test function similar to yours, but make sure it does little and runs fast.
See if you can execute it from your app.

2. Modify this function to return a number of affected rows, perhaps some dummy integer.

3. Instead of the function, make your app. to run SQL query performing a similar operation, see what happens.

4. Check for updates on the ODBC driver you are using, or maybe find a different driver altogether.

5. Spend some time searching net with Google

Here are couple links that could be relevant.

http://www.pcreview.co.uk/forums/thread-1865044.php
http://www.vbforums.com/archive/index.php/t-338252.html


thanx for the suggestion
i just run the sql query in the application, but another error (run time error '3669' execution canceled) arises.
if the data base is small, the function runs quite easily,
plz help
thanx in advance
Mar 25 '07 #5
michaelb
534 Expert 512MB
From what you've said it appears that problem is not related to the function, but has to do with a long running query.
I think you need to look at two areas:

1. Increase a connection timeout.
You may be able to set a connection timeout either in the definition of the ODBC
datasource, or in the connection string in your application.

2. Optimize database and/or your code so your queries will run faster.

Do you have an idea how long this query runs when executed directly in the database?
Mar 26 '07 #6
From what you've said it appears that problem is not related to the function, but has to do with a long running query.
I think you need to look at two areas:

1. Increase a connection timeout.
You may be able to set a connection timeout either in the definition of the ODBC
datasource, or in the connection string in your application.

2. Optimize database and/or your code so your queries will run faster.

Do you have an idea how long this query runs when executed directly in the database?

once again thanx for the suggestion
but i still have some problem

i use the following syntax

Public Sub getconnect()
Set ws = CreateWorkspace("", "abc", "", dbUseODBC)
Set dbaccount = ws.OpenDatabase("", , False, "ODBC;dsn=PostgreSQL3;uid=postgres;pwd=''")

End Sub

where should i set the connection timeout in the connection string and i didn't see any place in the odbc data source to increase the connection timeout.

and the time to run the query directly in the data base is nearly 8 minutes

thanx in advance
Mar 26 '07 #7
michaelb
534 Expert 512MB
You need to read the documentation on your ODBC driver to find out how to tune the timeout. You can probably get some help if you post this question on a VB forum, or you can browse Microsoft web-site. Start at http://support.microsoft.com/ and search the Knowledge Base.

All this may be helpful, but considering that your query takes about 8 minutes I would really take a hard look at performance... meaning database schema, indexes, the query itself etc.
This may take some time, because a bad performance can be caused by a number of things.
But you have to start somewhere, so let's do this:

- Print the table definition (along with indexes) on all tables involved in the query.
- If you have triggers, or rules ON INSERT or ON UPDATE this can matter as well.
- Open the psql session and run "explain analyze <query>"
where <query> is the actual SQL in question.

This will give you some helpful statistics on execution plan and time.

What version of Postgres are you using?
What OS is it running on?
Do you ever run "vacuum analyze" on the database?
Mar 26 '07 #8
coolminded
137 100+
You need to read the documentation on your ODBC driver to find out how to tune the timeout. You can probably get some help if you post this question on a VB forum, or you can browse Microsoft web-site. Start at http://support.microsoft.com/ and search the Knowledge Base.

All this may be helpful, but considering that your query takes about 8 minutes I would really take a hard look at performance... meaning database schema, indexes, the query itself etc.
This may take some time, because a bad performance can be caused by a number of things.
But you have to start somewhere, so let's do this:

- Print the table definition (along with indexes) on all tables involved in the query.
- If you have triggers, or rules ON INSERT or ON UPDATE this can matter as well.
- Open the psql session and run "explain analyze <query>"
where <query> is the actual SQL in question.

This will give you some helpful statistics on execution plan and time.

What version of Postgres are you using?
What OS is it running on?
Do you ever run "vacuum analyze" on the database?

thanx for the suggestion once again.
by the way
1. i am using the postgres 8.0.0-rc1
2.it is the Win XP with P III
3. i do use quite frequently the vacuum analyze on the database.
thanx
Mar 28 '07 #9
michaelb
534 Expert 512MB
Perhaps it makes sense to review your configuration settings,
check out this doc
I would also use the Windows Task Manager to check on memory and CPU consumption while this long query is running.

But reviewing the database schema and indexes, and also query optimization,
as I suggested earlier, should be still at the top of your list.
Mar 28 '07 #10
I am new to postgresql, but not so new to Access. I was having the same problem in a different context, and when I started looking at this, it smelled of Microsoft Access stench. Not so sure anymore, but here goes . . .

I don't know all the details I probably ought to know about your particular situation, but it does sound at least similar to what I was facing. error 3146 from Access when linking a postgresql table with an ostensibly large number of records (in my case, ~775,000). No VB is in use in my case, at least not yet; all I was doing was linking in a table from a postgresql database using Linked Table Manager. Opening the table would stall for about 60 seconds, then give me the stinking 3146 error.

The size of this table turned out to be more of a distraction than anything. The real problem was related to the *width* of the table I was taking in. It is probably a problem inside of either Access/Jet or the ODBC driver, I cannot tell which.

Instead of linking the table, try the following:

Create a new *query* in Access. You'll have to edit SQL directly on this one; there's no other way to do this to accomplish what I had to. Creating a query (as opposed to table linking) gets around Access' tendency to be too-smart-by-half when it refreshes table links.

The query should look thus:

SELECT (fields, but not * !!),
FROM [ODBC;DSN=dsnname;DATABASE=dbname;SERVER=pgsqldbser ver;PORT=5432;CA=d;A6=;A7=100;A8=4096;B0=255;B1=81 90;BI=0;C2=dd_;CX=1c502bb;A1=7.4].[table];


The particular ODBC options you embed here are not particularly important, but they should be borrowed from your ODBC driver's defaults, if you can get at them. You might be able to omit them and do just fine, as long as you include the referential essentials.

The really important part here (at least it was for me!) is to only include those columns from the table you absolutely need. Including all of the available columns (or putting a * for the SELECTed columns)
caused the 3146; if I only included up to a certain number/mass of columns, I could stay under some unknown threshold that would prevent the 3146.

Once you verify that the query works, save it under a convenient name and use this query as your "table link".

If you really need ALL of the columns, you may have to include essential key material in multiple queries, each of which obtains a specific set of columns for you, and inner-join them in parts later.

Hope this helps!


D
Dec 28 '10 #11
hansv
1
I had the same issue. Still today in 2019... there's seems to be no real solution.
I had to add big CLOB-files into a Postgress database using an MS Access-interface. And this was only possible by an odbc-connection, right on the table… But once the number of records raised I got the famous ODBC 3146 error. No way of passing by…
And using a pass-through query didn’t work either because the CLOB-field was to big to use in a SQL-string.
This is how I solved the problem (I know: quite dirty, but it worked).
Create a second table on the Postgress database and call it like TABLE2 (if the original table is called TABLE).
Open this TABLE2 in an odbc-connection.
Check with a pass-through query if what you want to add in TABLE is not yet present.
If not, add it to TABLE 2 with your DAO ODBC-connection (as this table contains no records at all you never will have the ODBC 3146 erro.
Then use a pass through query to copy your record from TABLE2 to TABLE. And use a pass through query to delete the record from TABLE2.
Of course, if you can do it in one pass through query it’s better : insert into TABLE values (1,2,3) WHERE NOT EXIST select * from TABLE WHERE KEY = 1). I needed to do it different because my only pass through query was too big to handle by Access…
Jan 30 '19 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Vic Spainhower | last post by:
Hello, This is probably not the correct forum to post this problem but I posted it in the Access group but didn't get a reply and I'd like to move forward on this. I have setup an ODBC...
7
by: tina | last post by:
Hello, Can you hale me to define my mistake please? I am trying to run a SQL Pass – Through Query from Access 2000 and inside the record set I am trying to loop for a LoginID. I think that this...
3
by: Yannick Turgeon | last post by:
Hello all, I'm using: - A97 (front-end) - MySQL drivers 3.51 - MySQL 4.0.18 (back-end) I've got a MySQL table connected to an Access DB throw an ODBC link. When I connect to this table, no...
14
by: Abhi | last post by:
FYI: This message is for the benefit of MS Access Community. I found that this prblem has been encounterd by many but there is hardly any place where a complete solution is posted. So I thought...
1
by: VBSponge | last post by:
Hi all. Need help here as I cant see whats wrong... I have a querydef in Access 2k which execs a stored proc in a SQL Server 2k db. I keep getting the following error, and cant stop it for the...
9
by: mcbill20 | last post by:
Hello all. I just installed Oracle 10g developer tools on a machine running XP Pro and Office XP. Before this I had just the Oracle 9 client installed. I the previous configuration, I was able to...
12
by: mukeshhtrivedi | last post by:
We have 4 MS Access Databases with few tables on each DB. There are some reports which uses ODBC. When I try to use the report and input date range and click ok I get error "ODBC --call failed" it...
4
by: alegria4ever | last post by:
I have an Access 2000 database that links several tables from Oracle 9. For some reason or another, one of our user repeatedly gets the following error when accessing queries within this database:...
11
by: Kirby1 | last post by:
ODBC call failed -------------------------------------------------------------------------------- We have recently upgraded our SQL Server from 7.0 to 2000. Since then my Access database to...
1
by: mikerudy | last post by:
I have an 3rd-party application that uses a SQL back-end, but uses Access 2000 (linked tables all using the same DSN) as an intermediary. We recently upgraded from SQL 7 to SQL 2005, which went...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.