ODBC 3146 Error for Some Users (1 Viewer)

oumahexi · Apr 3, 2008

Hello again

My apologies for another posting so soon after the last. My problem is an inherited database that just does not behave.

The latest error is the ODBC 3146 (Error occurred report could not be checked). This error occurs when a user tries to run a report.

Our system collects data via Access 2002 into fields that are linked to SQL Server 2000, the information is then passed back into Access to run the required reports. The reason for this is that the original database relied on Access information being fed in from an external source. The source changed to SQL while our database was in it's infancy, but management decided that rather than rebuild from scratch it would be best to patch the old system up

I know there are many queries in the database which query a query for no apparent reason - example:

qryThisQuery interrogates qry_tblThisQuery (selecting all fields from this query), qry_tblThisQuery interrogates dbotblThisQuery (selecting all fields from the table).

My first thought about the ODBC errors being experienced is that there are too many scenarios like this and it limits the efficiency of the database. However, my hands are tied, I am only permitted to patch it up.

My second thought is that perhaps the user needs her registry cleaned out? or perhaps there may be too much junk in her temp folder? The big problem is that it works on my pc and another user's pc but not on the third.

any thoughts? Very much appreciated.

Thanks

Ouma

Rabbie · Apr 3, 2008

My first reaction when it works on some PCs but not on others is to check the route to the SQL Server DB from the failing PC. Is it the same as on the working ones.

Good luck

gemma-the-husky · Apr 3, 2008

you may be trapping the error and substituting your OWN message
error 3146 is

"ODBC Call failed"

perhaps the query is too complex etc, or took too long to return values

oumahexi · Apr 4, 2008

Odbc

Rabbie said:
My first reaction when it works on some PCs but not on others is to check the route to the SQL Server DB from the failing PC. Is it the same as on the working ones.

Good luck

That sounds like it Rabbie, it has to be to do with the way the computers are set up. Thanks again. I'll keep you posted if we find a solution.

oumahexi · Apr 4, 2008

Trapped?

gemma-the-husky said:
you may be trapping the error and substituting your OWN message
error 3146 is

"ODBC Call failed"

perhaps the query is too complex etc, or took too long to return values

By trapping an erro and substituting our own do you mean like a public function has been created to display this message when error number 3146 is encountered? Fab, just what I need, more hidden code to find. Can someone please remind me why I applied for this job? It definately wasn't for the money lol.

Thanks for the input Gemma.

jasn_78 · Aug 29, 2008

Did you ever find the solution to this as I am having the exact same issue? Some computers works fine others have this error

Banana · Aug 29, 2008

Two things:

1) We'd need a bit more info on how end users connect to the server; using DSN or not? Has the comuter's network changed recently? Is it within an office LAN? Has the backend been altered recently?

2) If you also have anything in form's Error Event, comment it out to force Access's default error message which will then display the actual error message. If you don't know why, this is why.
OTOH, if the error occurs within a procedure inside the report's module, use DAO.Errors collection to investigate the problem. (In fact, since you seem to know which query is the problem, you can write a quick temporary procedure to call that query and trap it so you can get more information from the backend about exactly what happened.)

HTH.

jasn_78 · Sep 4, 2008

I dont know if this helps but i ran a trace with odbc on the query and these are the lines that get the error message.

Code:

x macroSTARTUP  8c04-8c4c	ENTER SQLExecDirectW 
		HSTMT               07CE3528
		WCHAR *             0x0E2F2790 [      -3] "SELECT "HTRXTBL"."HTRX_TRX_DATE"  FROM "HTRXTBL","ITEMTBL" WHERE ((((("HTRXTBL"."HTRX_REC_TYPE" = 'ITMSALE' ) AND ("HTRXTBL"."HTRX_TRX_DATE" >= {ts '28/08/2008'} ) ) AND ("HTRXTBL"."HTRX_TRX_DATE" <= {ts '1/09/2008'} ) ) AND ("HTRXTBL"."HTRX_AREA_NUMBER" = 3 ) ) AND ("ITEMTBL"."ITEM_NUMBER" = "HTRXTBL"."HTRX_ITEM_NUMBER" ) ) \ 0"
		SDWORD                    -3

x macroSTARTUP  8c04-8c4c	EXIT  SQLExecDirectW  with return code 0 (SQL_SUCCESS)
		HSTMT               07CE3528
		WCHAR *             0x0E2F2790 [      -3] "SELECT "HTRXTBL"."HTRX_TRX_DATE"  FROM "HTRXTBL","ITEMTBL" WHERE ((((("HTRXTBL"."HTRX_REC_TYPE" = 'ITMSALE' ) AND ("HTRXTBL"."HTRX_TRX_DATE" >= {ts '28/08/2008'} ) ) AND ("HTRXTBL"."HTRX_TRX_DATE" <= {ts '1/09/2008'} ) ) AND ("HTRXTBL"."HTRX_AREA_NUMBER" = 3 ) ) AND ("ITEMTBL"."ITEM_NUMBER" = "HTRXTBL"."HTRX_ITEM_NUMBER" ) ) \ 0"
		SDWORD                    -3

x macroSTARTUP  8c04-8c4c	ENTER SQLFetch 
		HSTMT               07CE3528

x macroSTARTUP  8c04-8c4c	EXIT  SQLFetch  with return code -1 (SQL_ERROR)
		HSTMT               07CE3528

		DIAG [HY000] [ODBC Firebird Driver][Firebird]conversion error from string "28/08/2008" (-413) 

x macroSTARTUP  8c04-8c4c	ENTER SQLErrorW 
		HENV                07CE1C18
		HDBC                07CE32E8
		HSTMT               07CE3528
		WCHAR *             0x0013BB68 (NYI) 
 		SDWORD *            0x0013BB34
		WCHAR *             0x05692000 
		SWORD                     4095 
		SWORD *             0x0013BB28

x macroSTARTUP  8c04-8c4c	EXIT  SQLErrorW  with return code 0 (SQL_SUCCESS)
		HENV                07CE1C18
		HDBC                07CE32E8
		HSTMT               07CE3528
		WCHAR *             0x0013BB68 (NYI) 
 		SDWORD *            0x0013BB34 (-413)
		WCHAR *             0x05692000 [      73] "[ODBC Firebird Driver][Firebird]conversion error from string "28/08/2008""
		SWORD                     4095 
		SWORD *             0x0013BB28 (73)

Any ideas?

jasn_78 · Sep 16, 2008

Ok got the actual error message as follows from the query i create
ODBC-CALL FAILED
ODBC Firebird Driver Firebird Conversion Error From string "29/07/2008" #-413

I know what the problem is, its the fact its formatting as dd/mm/yyyy where as firebird and this database need it formatted as mm/dd/yyyy which i have done in my code

now this works fine on my p.c with vista and a couple of others with xp but then not some others i am testing on.

jasn_78 · Sep 16, 2008

Nevermind guys figured it out, it was reading as a string not a date used CDate() in it and works fine now thank u

darnelleF · Feb 10, 2016

now i'm relatively new to this access and vba but i got a spectacular error vba 3146. let me tell you the basics
|
THERE ARE FOUR MACHINES CONNECTED TO THE SERVER VIA FILE DSN

four users enter information via access 2007 to a MYSQL DATABASE

out of these four users, one user cant use one form on that access application
that form job is to generate what has been rejected to the user
(which means it connects to the database to find a rejected record and displays it )
the user then corrects the information and while updating sends this error
(vba ODBC, 3146)

the strange thing is error is particularly only happening to one user the others are fine and we've had this user move her seat and the error follows them. but the other users can connect and save no problem

this is weird to be with only 4 months experience
need help
i have checked to make sure she is entering the right format
no strings in integers etc etc
but strange........ we cant find
it just fails to update but i believe the error should be the type of variable data to the fields

jdraw · Feb 10, 2016

DarnelleF,

You have joined a thread that is over 7 years old. ???????

From what I recall a 3146 error ODBC is only one of a series of errors.

You might want to review the errors collection and see what other errors may have occurred.
Found this related code(untested)

Code:

...other code here
.... the go here if err 3146
MyErrorTrap:
  Dim errX As DAO.Error
  Dim MyError As Error
  If Errors.Count > 1   'This always seems to be 0, so no help
    For Each errX In DAO.Errors  'These are empty even if dont check for Errors.Count
      Debug.Print "ODBC Error"
      Debug.Print errX.Number
      Debug.Print errX.Description
    Next errX
  Else
    Debug.Print "VBA Error"
    Debug.Print Err.Number
    Debug.Print Err.Description
  End If

Good luck

darnelleF · Feb 15, 2016

awesome i was now able to pin point the error!

ODBC Error
1062
[MySQL][ODBC 5.1 Driver][mysqld-5.1.46-community]Duplicate entry '\\******\****\******\****\289\0421B289_526-2015.pdf' for key 'indPath'

the table its trying to update has a column called Path ( not indPath)
i had to censor the information but it contains the location of a file,etc

Sorry for posting on an old post
but any futher help will be greatly appreciated

but yet still im unsure what that means now

ODBC 3146 Error for Some Users (1 Viewer)

oumahexi

Free Range Witch

Rabbie

Super Moderator

gemma-the-husky

Super Moderator

oumahexi

Free Range Witch

oumahexi

Free Range Witch

jasn_78

Registered User.

Banana

split with a cherry atop.

jasn_78

Registered User.

jasn_78

Registered User.

jasn_78

Registered User.

darnelleF

New member

jdraw

Super Moderator

darnelleF

New member

Similar threads

Users who are viewing this thread