Error Number : 3146,ODBC--call failed.

[kirti_sm25]

Hiee there...

I am downloading data from sql server 2005 to my access database using the following query in Visual Basic 6.0

CompTemp.Execute "Insert INTO tempnav(abc,xyz,pqr,asd,qwe,rty,dsf) " & _
                        "Select Distinct abc,xyz,pqr,rep as repur,rate as sale,0,'11/11/1111' " & _
                        "FROM [ODBC;Driver=SQL Server;SERVER=mysqlserver;DATABASE=mysqldatabase;UID=ServerSQLuserid;PWD=ServerSQLpaswd;].[myTableName] "

-Here TempNav is my Local Access Database
-CompTemp is DAO database connection

This query works perfectly through out, but if once my internet connection is lost then this query continously gives ODBC call Failed error although my pc regains the internet connection back

I am passing Connection string in the query itself still when executed it is nt refreshing the connection inspite of internet being working properly

if i close the program and run again then the problem is solved...bt i want to handle this error and reset the connection again at run time...

Does anyone know how to Reset this connectionstring using DAO?


Thanks in Advance!

[si_the_geek]

Thread moved to Database Development forum (the "VB6" forum is only meant for questions which don't fit in more specific forums)

If the problem is with the ODBC connection, I don't think there is anything you can do - because you are not in control of it at all.

If the problem is somehow with CompTemp, you may be able to get past the error by closing and re-opening it.

[kirti_sm25]

ya i suppose problem will be resolved by re-opening the connection bt i dnt knw the method to re-open it in DAO, can u please show me the code to refresh or re-open the connection using DAO..

[si_the_geek]

I can't remember, it has been far too long since I upgraded to ADO.

As a rough guess, it is likely to be something like this (both lines should already be elsewhere in your project):

CompTemp.Close
CompTemp.Open ....

[kirti_sm25]

I need to do it using DAO and not ADO..neways i got the solution..using FileDSN the problem is resolved..But now ther is one another issue..

When my internet connection is lost and i execute my query then as m using DSN and it is unable to establish connection to the SQL server, It ends up prompting a msgbox Connection failed and consecutively SQL server login msgbox...although i have kept error handler it doesn't enters the handler instead throws error msgbox there itself

My application runs in the background so i need to avoid this msgbox to b displayed and want to handle it runtime...can anyone suggest me a way to do this?

[si_the_geek]

I would recommend checking to see if the DSN allows you to specify whether the user is prompted - it will most likely be part of the login section, but could be elsewhere.

[kirti_sm25]

I have searched in Control Panel\Administrative tools\Data Source(ODBC) for the option bt i cannot find the same...any other way may b?