Hi all
I wanna know how to create a connection globally in VB? I want to create a global function and use it in all oer application (same project) to connect to the database. Database would be Oracle. Can anyone please give me the idea please.
Thanks in advance.
Comments
: I wanna know how to create a connection globally in VB? I want to create a global function and use it in all oer application (same project) to connect to the database. Database would be Oracle. Can anyone please give me the idea please.
:
: Thanks in advance.
Well, you can either put a public function in a module, or you could create a class that encapsulates the connection object, or you could just have a public variable of type ADODB.Connection in a module and use the same connection object everywhere instead of a function that creates new connections all the time.
[size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]
[code]
$ select * from users where clue > 0
no rows returned
[/code]
: : Hi all
: : I wanna know how to create a connection globally in VB? I want to create a global function and use it in all oer application (same project) to connect to the database. Database would be Oracle. Can anyone please give me the idea please.
: :
: : Thanks in advance.
:
: Well, you can either put a public function in a module, or you could create a class that encapsulates the connection object, or you could just have a public variable of type ADODB.Connection in a module and use the same connection object everywhere instead of a function that creates new connections all the time.
:
:
: [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]
:
: [code]
: $ select * from users where clue > 0
: no rows returned
: [/code]
:
:
Thank you so much Infedel
I created the Module and it's corresponding functions and Global connections but was doing a silly mistake while calling the global function.It can't open the database.Shows an erorr " Cand find C:mydb.mdb"
Please help me in finding out the reason of this error.
Thank you so much for your quick reply as always :-)
Here is my code :
*****************
Option Explicit
Public GCon As New ADODB.Connection
Public Function ConnectString()
ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:mydb.mdb;User Id=admin;Password=;"
End Function
Public Function Open_Connection(oConn As Connection) As Boolean
On Error GoTo open_error_handler
oConn.CursorLocation = adUseClient
oConn.ConnectionString = ConnectString()
oConn.Mode = adModeReadWrite
oConn.Open ConnectString
Open_Connection = True ' Connection successfully open
Exit Function
' Exception handling
open_error_handler:
Call ErrorHandler(GCon)
Open_Connection = False
Exit Function
End Function
************************
I am calling these as :
Private Sub cmd_show_Click()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim SQL_String As String
SQL_String = "select * from customer"
rs.Open SQL_String, Open_Connection(GCon), adOpenDynamic, adLockOptimistic, adCmdText
End Sub
: : : Hi all
: : : I wanna know how to create a connection globally in VB? I want to create a global function and use it in all oer application (same project) to connect to the database. Database would be Oracle. Can anyone please give me the idea please.
: : :
: : : Thanks in advance.
: :
: : Well, you can either put a public function in a module, or you could create a class that encapsulates the connection object, or you could just have a public variable of type ADODB.Connection in a module and use the same connection object everywhere instead of a function that creates new connections all the time.
: :
: :
: : [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]
: :
: : [code]
: : $ select * from users where clue > 0
: : no rows returned
: : [/code]
: :
: :
: Thank you so much Infedel
: I created the Module and it's corresponding functions and Global connections but was doing a silly mistake while calling the global function.It can't open the database.Shows an erorr " Cand find C:mydb.mdb"
:
: Please help me in finding out the reason of this error.
:
: Thank you so much for your quick reply as always :-)
:
:
: Here is my code :
: *****************
: Option Explicit
: Public GCon As New ADODB.Connection
:
:
: Public Function ConnectString()
:
: ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:mydb.mdb;User Id=admin;Password=;"
: End Function
:
:
:
: Public Function Open_Connection(oConn As Connection) As Boolean
: On Error GoTo open_error_handler
:
:
: oConn.CursorLocation = adUseClient
: oConn.ConnectionString = ConnectString()
:
: oConn.Mode = adModeReadWrite
: oConn.Open ConnectString
:
: Open_Connection = True ' Connection successfully open
: Exit Function
:
: ' Exception handling
: open_error_handler:
: Call ErrorHandler(GCon)
: Open_Connection = False
: Exit Function
:
: End Function
:
: ************************
: I am calling these as :
:
:
: Private Sub cmd_show_Click()
: Dim rs As ADODB.Recordset
: Set rs = New ADODB.Recordset
: Dim SQL_String As String
:
: SQL_String = "select * from customer"
:
: rs.Open SQL_String, Open_Connection(GCon), adOpenDynamic, adLockOptimistic, adCmdText
:
:
: End Sub
:
:
:
:
Please help me :-(
:
[blue]ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:mydb.mdb;User Id=admin;Password=;" [/blue]
First of all, check the Path. Seearch your machine for mydb.mdb file. You probably saved it somewhere else like C:MyDocumentsmydb.mdb
You can also try this
[code]
ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" [blue]& App.Path & [/blue]"myDb.mdb;" & _
"User Id=admin;" & _
"Password="
[/code]
Where [blue]App.Path[/blue] is path where you saved your application
: I created the Module and it's corresponding functions and Global connections but was doing a silly mistake while calling the global function.It can't open the database.Shows an erorr " Cand find C:mydb.mdb"
:
: Please help me in finding out the reason of this error.
:
: Thank you so much for your quick reply as always :-)
:
:
: Here is my code :
: *****************
: Option Explicit
: Public GCon As New ADODB.Connection
:
:
: Public Function ConnectString()
:
: ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:mydb.mdb;User Id=admin;Password=;"
: End Function
:
:
:
: Public Function Open_Connection(oConn As Connection) As Boolean
: On Error GoTo open_error_handler
:
:
: oConn.CursorLocation = adUseClient
: oConn.ConnectionString = ConnectString()
:
: oConn.Mode = adModeReadWrite
: oConn.Open ConnectString
:
: Open_Connection = True ' Connection successfully open
: Exit Function
:
: ' Exception handling
: open_error_handler:
: Call ErrorHandler(GCon)
: Open_Connection = False
: Exit Function
:
: End Function
:
: ************************
: I am calling these as :
:
:
: Private Sub cmd_show_Click()
: Dim rs As ADODB.Recordset
: Set rs = New ADODB.Recordset
: Dim SQL_String As String
:
: SQL_String = "select * from customer"
:
: rs.Open SQL_String, Open_Connection(GCon), adOpenDynamic, adLockOptimistic, adCmdText
:
:
: End Sub
Well I can't help you with the location of your database file, but I can point out a couple of other problems you have. First, Open_Connection returns a Boolean, which is no good to a Recordset object.
Another thing is that if you're going to have a global connection object, it doesn't make much sense to open it every time you're going to use it. A) it will generate some kind of error, I am sure, and
[size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]
[code]
$ select * from users where clue > 0
no rows returned
[/code]
Thanks for the informative reply. I was trying to use global connection so that I can change the table as well as database on same form.
My databse path name is correct.I tried to use this code to connect with oracle (except access I used ODBC connection for oracle) And getting the errors that can't connect to the ODBC. When I am using Data bound control for both Access and Oracle, It shows data.And ODBC and JET Engine connection seems correc.But it gives error when I try to connect programatically.
One question infedel... Why boolean is not good for record set?
I am totally new in VB and have no idea how it works.Got a chance to do a project in VB so just trying that :-(
Once again Thanks alot for your reply.Everytime I learn so much from you.
Regards
Lucky
:
: Well I can't help you with the location of your database file, but I can point out a couple of other problems you have. First, Open_Connection returns a Boolean, which is no good to a Recordset object.
:
: Another thing is that if you're going to have a global connection object, it doesn't make much sense to open it every time you're going to use it. A) it will generate some kind of error, I am sure, and
:
:
: [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]
:
: [code]
: $ select * from users where clue > 0
: no rows returned
: [/code]
:
:
My database path is correct. It gives error with ODBC connection for Oracle also.y DSN is working but still getting error :-(
Regards
Lucky
:
: First of all, check the Path. Seearch your machine for mydb.mdb file. You probably saved it somewhere else like C:MyDocumentsmydb.mdb
:
: You can also try this
: [code]
: ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
: "Data Source=" [blue]& App.Path & [/blue]"myDb.mdb;" & _
: "User Id=admin;" & _
: "Password="
: [/code]
: Where [blue]App.Path[/blue] is path where you saved your application
:
: My database path is correct. It gives error with ODBC connection for Oracle also.y DSN is working but still getting error :-(
:
: Regards
: Lucky
:
I think I found the problem
Yours code
[code]
oConn.CursorLocation = adUseClient
oConn.ConnectionString = ConnectString()[blue] You do not need that [/blue]
oConn.Mode = adModeReadWrite
oConn.Open ConnectString
[/code]
Change it to this way
[code]
oConn.Open ConnectString ()
oConn.CursorLocation = adUseClient
oConn.Mode = adModeReadWrite
Public Function ConnectString() as String
ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Dataource=C:mydb.mdb;User Id=admin;Password=;"
End Function
[/code]
The recordset object needs a connection object passed to it, but your function returns a boolean.
[size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]
[code]
$ select * from users where clue > 0
no rows returned
[/code]
: : My database path is correct. It gives error with ODBC connection for Oracle also.y DSN is working but still getting error :-(
: :
: : Regards
: : Lucky
: :
: I think I found the problem
: Yours code
: [code]
: oConn.CursorLocation = adUseClient
: oConn.ConnectionString = ConnectString()[blue] You do not need that [/blue]
:
: oConn.Mode = adModeReadWrite
: oConn.Open ConnectString
: [/code]
: Change it to this way
: [code]
: oConn.Open ConnectString ()
: oConn.CursorLocation = adUseClient
: oConn.Mode = adModeReadWrite
:
: Public Function ConnectString() as String
: ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Dataource=C:mydb.mdb;User Id=admin;Password=;"
: End Function
: [/code]
Hi Lion,
It was not working.Finally I upgraded my Windows XP OS and now all my function is working fine.Withought any changing in code. Tha's really wiered to me
But yes I got one error while terminating function. It was giving Run time error : 3001
Thanks alot man!
I didn't reallize that, I know it's my neglegence. Sorry to bother you guys and thanks alot for helping me and pointing it out where I am wrong.
Hats off to you.
Regards
Lucky
: : One question infedel... Why boolean is not good for record set?
:
: The recordset object needs a connection object passed to it, but your function returns a boolean.
:
:
: [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]
:
: [code]
: $ select * from users where clue > 0
: no rows returned
: [/code]
:
:
: It was not working.Finally I upgraded my Windows XP OS and now all my function is working fine.Withought any changing in code. Tha's really wiered to me
:
[blue]Welcome to Microsoft World .... deep sigh :-( [/blue]
: But yes I got one error while terminating function. It was giving Run time error : 3001
:
What did exactly message say? Could you post a code?
: : It was not working.Finally I upgraded my Windows XP OS and now all my function is working fine.Withought any changing in code. Tha's really wiered to me
: :
: [blue]Welcome to Microsoft World .... deep sigh :-( [/blue]
: : But yes I got one error while terminating function. It was giving Run time error : 3001
: :
: What did exactly message say? Could you post a code?
Yea Welcome to Microsoft world. :-)
Well the exact message was...
"Run Time Error :3001"
Argument are of wrong type, are out of acceptable range, or are in
conflict with one another
: Well the exact message was...
:
: "Run Time Error :3001"
: Argument are of wrong type, are out of acceptable range, or are in
: conflict with one another
:
I think your problem is here
[code]
rs.Open SQL_String,[blue][b][italic]Open_Connection(GCon)[/b][/italic][/blue], adOpenDynamic, adLockOptimistic, adCmdText
[/code]
See infidel's message about recordset/connection