team2005
asked on
How to open a ODBC-connection in vb6.0
Hi!
Have a project in vb6.0 that use this connection to the db.
Code:
Public Sub start_access()
Dim Msg As String
Dim application As String
Dim dbs As String
Dim workgroup As String
Dim user As String
Dim password As String
Dim cTries As Integer
Dim x
Dim xy As Boolean
Dim var2 As Integer
Dim clsImport As New Access.AllModules
Dim int2 As Variant
Dim fso As New FileSystemObject
accstartet = False
' This is the default location of Access
application = "J:\Program Files\Microsoft Office\Office\MSACCESS.EXE
' Use the path and name of a secured MDB on your system
dbs = App.Path & "\Lyse111.mdb"
' This is the default workgroup
workgroup = App.Path & "\PB2000.mdw "
user = "JHDEv"
' Use a valid username
password = "caesar.vicit" ' and correct password
' x = Shell(application & " " & Chr(34) & dbs & Chr(34) & " /nostartup /user " & user & " /pwd " & password & " /wrkgrp " & Chr(34) & workgroup & Chr(34), vbHide)
x = Shell(application & " " & Chr(34) & dbs & Chr(34) & " /nostartup /user " & user & " /pwd " & password & " /wrkgrp " & Chr(34) & workgroup & Chr(34), vbMaximizedFocus)
On Error GoTo WAITFORACCESS
Set accObj = GetObject(, "Access.Application") ' Turn off error handling
On Error GoTo 0 ' You can now use the accObj reference to automate Access
accstartet = True
accObj.CloseCurrentDatabas
accObj.Quit
Set accObj = Nothing
Exit Sub
WAITFORACCESS: ' <--- This line must be left-aligned. ' Access isn't registered in the Running Object Table yet, so call
' SetFocus to take focus from Access, wait half a second, and try ' again. If you try five times and fail, then something has probably
' gone wrong, so warn the user and exit. SetFocus
If cTries < 5 Then
cTries = cTries + 1
Sleep 500 ' wait 1/2 seconds Resume
Else
MsgBox "Access is taking too long. Process ended.", vbMsgBoxSetForeground
Screen.MousePointer = vbDefault
End If
End Sub
Have different forms in my project that uses a adodbc-object control. This control
is set up with a odbc-connection.
What i want is to use a odbc connection insted of the code, that start access.
Is there a way to do this ?
Onother thing is that i must use accObj.Run...
I use a library in the access database, to start different functions.
Please help...
Thanks
Tor
Have a project in vb6.0 that use this connection to the db.
Code:
Public Sub start_access()
Dim Msg As String
Dim application As String
Dim dbs As String
Dim workgroup As String
Dim user As String
Dim password As String
Dim cTries As Integer
Dim x
Dim xy As Boolean
Dim var2 As Integer
Dim clsImport As New Access.AllModules
Dim int2 As Variant
Dim fso As New FileSystemObject
accstartet = False
' This is the default location of Access
application = "J:\Program Files\Microsoft Office\Office\MSACCESS.EXE
' Use the path and name of a secured MDB on your system
dbs = App.Path & "\Lyse111.mdb"
' This is the default workgroup
workgroup = App.Path & "\PB2000.mdw "
user = "JHDEv"
' Use a valid username
password = "caesar.vicit" ' and correct password
' x = Shell(application & " " & Chr(34) & dbs & Chr(34) & " /nostartup /user " & user & " /pwd " & password & " /wrkgrp " & Chr(34) & workgroup & Chr(34), vbHide)
x = Shell(application & " " & Chr(34) & dbs & Chr(34) & " /nostartup /user " & user & " /pwd " & password & " /wrkgrp " & Chr(34) & workgroup & Chr(34), vbMaximizedFocus)
On Error GoTo WAITFORACCESS
Set accObj = GetObject(, "Access.Application") ' Turn off error handling
On Error GoTo 0 ' You can now use the accObj reference to automate Access
accstartet = True
accObj.CloseCurrentDatabas
accObj.Quit
Set accObj = Nothing
Exit Sub
WAITFORACCESS: ' <--- This line must be left-aligned. ' Access isn't registered in the Running Object Table yet, so call
' SetFocus to take focus from Access, wait half a second, and try ' again. If you try five times and fail, then something has probably
' gone wrong, so warn the user and exit. SetFocus
If cTries < 5 Then
cTries = cTries + 1
Sleep 500 ' wait 1/2 seconds Resume
Else
MsgBox "Access is taking too long. Process ended.", vbMsgBoxSetForeground
Screen.MousePointer = vbDefault
End If
End Sub
Have different forms in my project that uses a adodbc-object control. This control
is set up with a odbc-connection.
What i want is to use a odbc connection insted of the code, that start access.
Is there a way to do this ?
Onother thing is that i must use accObj.Run...
I use a library in the access database, to start different functions.
Please help...
Thanks
Tor
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Once ADO connection is open, you can use:
oConn.Execute "your sql string here"
to run any stored views or to access any table or query to save update or retrieve data. See, for example, this sql site http://www.w3schools.com/sql/default.asp.
S
oConn.Execute "your sql string here"
to run any stored views or to access any table or query to save update or retrieve data. See, for example, this sql site http://www.w3schools.com/sql/default.asp.
S
ASKER
Hi!
What i must have, is a way to call a Function in the MDB-file.
Or is there a way to connect this objects ?
Global accObj As Access.application
Global oConn As ADODB.Connection
Today i use this code to execute a funtion:
accObj.Run "FileStem", tabb ---> Execute the function FileStem(tabb) in access
But how do i do this using my ODBC-connection ?
Tor
What i must have, is a way to call a Function in the MDB-file.
Or is there a way to connect this objects ?
Global accObj As Access.application
Global oConn As ADODB.Connection
Today i use this code to execute a funtion:
accObj.Run "FileStem", tabb ---> Execute the function FileStem(tabb) in access
But how do i do this using my ODBC-connection ?
Tor
odbc is a way to connect to database (any database: be it access, mysql, sql server, oracle) not to access application (msaccess.exe, or an object in vb that is an access.application). Access macros and their functions are part of access application, isn't it? therefore I am not aware of a way to odbc to a macro function. But I must add that I'm much better with sql serevr than access.
S
S
ASKER
Thanks for BIG help, try the Connection ODBC true file.
And that one works very good.
But there are just 50% of the solution to my problem.
One problem remains:
How to Use .Run with this ODBC-connection.
Use this connection that works fine:
oConn.Open "FILEDSN=j:\xxx.dsn;" & _
"Uid=xxxxx;" & _
"Pwd=xxxxxxx"
Use this as the code i used before:
Global accObj As Access.application
The accObj use the accObj.Run command that i must have.
Becource i start different functions in the access db.
But oConn dosent have this option ?
If you can help me with this to, you get all the 500 points.
Thanks for helping me out.
Tor