?
Solved

How to open a ODBC-connection in vb6.0

Posted on 2005-03-26
5
Medium Priority
?
943 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month12 days, 20 hours left to enroll

777 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