Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to open a ODBC-connection in vb6.0

Posted on 2005-03-26
5
Medium Priority
?
1,007 Views
Last Modified: 2008-01-09
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.CloseCurrentDatabase
  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
0
Comment
Question by:team2005
  • 3
  • 2
5 Comments
 
LVL 19

Accepted Solution

by:
Shauli earned 2000 total points
ID: 13636210
Check this link, and bookmark it. It shows, with examples, all ADO connections:

http://www.able-consulting.com/ADO_Conn.htm

btw, if the access database is on the LAN then Microsoft Jet connection (OLEDB) will be fine.

S
0
 
LVL 2

Author Comment

by:team2005
ID: 13636287
Hi!

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

0
 
LVL 19

Expert Comment

by:Shauli
ID: 13636324
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
0
 
LVL 2

Author Comment

by:team2005
ID: 13636349
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

0
 
LVL 19

Expert Comment

by:Shauli
ID: 13636683
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
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month12 days, 12 hours left to enroll

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question