Solved

Configure Access Database ODBC using VB6 Code

Posted on 2003-12-07
12
60,117 Views
Last Modified: 2013-12-25
Right now I have an application that uses an Access database and an ODBC connection using VB6.

I currently have to set up the ODBC configuration using Control Panel/ Administrative Tools/Data Sources (ODBC)/User DSN etc in Windows 2000.

This means that when I distribute the application I will have to get other users to go into Control Panel, and configure the ODBC and set the DSN for the proper database.

Has anyone figured out a way to do this from within VB6. It sounds like an API call(?) will be required...

I would like to have the user select the database and then have VB configure the DSN name and driver using code...
0
Comment
Question by:DrD
12 Comments
 
LVL 8

Expert Comment

by:MYLim
ID: 9894817
Try This:

Option Explicit


Private Const ODBC_ADD_DSN = 1        ' Add data source
Private Const ODBC_CONFIG_DSN = 2     ' Configure (edit) data source
Private Const ODBC_REMOVE_DSN = 3     ' Remove data source
Private Const ODBC_ADD_SYS_DSN = 4    ' Add a new system data source.

'ODBC_CONFIG_SYS_DSN: Modify an existing system data source.

'ODBC_REMOVE_SYS_DSN: Remove an existing system data source

Private Const vbAPINull As Long = 0&  ' NULL Pointer
       
     
       
Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" (ByVal hwndParent As Long, ByVal fRequest As Long, ByVal lpszDriver As String, ByVal lpszAttributes As String) As Long



Public Function AddODBCDSN()

    Dim strDriver As String
    Dim strAttributes As String
    Dim intRet As Long
     
   'since i do not have SQL Server installed....
    strDriver = "Microsoft Access Driver (*.mdb)"      'Set the attributes delimited by null.
    'See driver documentation for a complete
    'list of supported attributes.
     
   'You should use this
    'strDriver = "SQL Server"
    strAttributes = "SERVER=SomeServer" & Chr$(0)
    strAttributes = strAttributes & "DESCRIPTION=Test DSN" & Chr$(0)
    strAttributes = strAttributes & "DSN=DSN_TEMP" & Chr$(0)
    strAttributes = strAttributes & "DATABASE=c:\test.mdb" & Chr$(0)
    'strAttributes = strAttributes & "UID=" & Chr$(0)
    'strAttributes = strAttributes & "PWD=" & Chr$(0)
       
   'To show dialog, use Form1.Hwnd instead of vbAPINull.
    intRet = SQLConfigDataSource(vbAPINull, ODBC_ADD_SYS_DSN, strDriver, strAttributes)
    If intRet Then
        MsgBox "DSN Created"
    Else
        MsgBox "Create Failed"
     
   End If
End Function

0
 
LVL 8

Expert Comment

by:MYLim
ID: 9894822
0
 
LVL 8

Expert Comment

by:MYLim
ID: 9894827
0
 
LVL 8

Accepted Solution

by:
MYLim earned 500 total points
ID: 9894837
Step-by-Step Example
=====================
1.Start a New Project.
2.In the Advanced tab of the Options dialog box under the Tools menu, set a Conditional Compilation Argument named WIN32 equal to 1 if using Visual Basic 4.0 32-bit, or 0 if using Visual Basic 4.0 16-bit.
3.Add two CommandButtons to the default form.
4.Add the following code to the General Declarations:      Option Explicit

      'Constant Declaration
      Private Const ODBC_ADD_DSN = 1        ' Add data source
      Private Const ODBC_CONFIG_DSN = 2     ' Configure (edit) data source
      Private Const ODBC_REMOVE_DSN = 3     ' Remove data source
      Private Const vbAPINull As Long = 0&  ' NULL Pointer

      'Function Declare
      #If WIN32 Then

          Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" _
          (ByVal hwndParent As Long, ByVal fRequest As Long, _
          ByVal lpszDriver As String, ByVal lpszAttributes As String) _
          As Long
      #Else
          Private Declare Function SQLConfigDataSource Lib "ODBCINST.DLL" _
          (ByVal hwndParent As Integer, ByVal fRequest As Integer, ByVal _
          lpszDriver As String, ByVal lpszAttributes As String) As Integer
      #End If
                              
5.Add the following code into the Click event of Command1:      #If WIN32 Then
          Dim intRet As Long
      #Else
          Dim intRet As Integer
      #End If
      Dim strDriver As String
      Dim strAttributes As String

      'Set the driver to SQL Server because it is most common.
      strDriver = "SQL Server"
      'Set the attributes delimited by null.
      'See driver documentation for a complete
      'list of supported attributes.
      strAttributes = "SERVER=SomeServer" & Chr$(0)
      strAttributes = strAttributes & "DESCRIPTION=Temp DSN" & Chr$(0)
      strAttributes = strAttributes & "DSN=DSN_TEMP" & Chr$(0)
      strAttributes = strAttributes & "DATABASE=pubs" & Chr$(0)
      'To show dialog, use Form1.Hwnd instead of vbAPINull.
      intRet = SQLConfigDataSource(vbAPINull, ODBC_ADD_DSN, _
      strDriver, strAttributes)
      If intRet Then
          MsgBox "DSN Created"
      Else
          MsgBox "Create Failed"
      End If
                              
6.Add the following code into the Click event of Command2:      #If WIN32 Then
          Dim intRet As Long
      #Else
          Dim intRet As Integer
      #End If
      Dim strDriver As String
      Dim strAttributes As String

      'Set the driver to SQL Server because most common.
      strDriver = "SQL Server"
      'Set the attributes delimited by null.
      'See driver documentation for a complete list of attributes.
      strAttributes = "DSN=DSN_TEMP" & Chr$(0)
      'To show dialog, use Form1.Hwnd instead of vbAPINull.
      intRet = SQLConfigDataSource(vbAPINull, ODBC_REMOVE_DSN, _
      strDriver, strAttributes)
      If intRet Then
          MsgBox "DSN Deleted"
      Else
          MsgBox "Delete Failed"
      End If
                              
7.Run the project.
8.Click Command1 to add a DSN named DSN_TEMP.
9.Click Command2 to remove the DSN named DSN_TEMP.

'that all
0
 
LVL 8

Expert Comment

by:MYLim
ID: 9894934
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9894938
Hi DrD,

Have you considered using a dsnless connection?

Invoke browse for file dialog, then build the connect string and open the connection
http://www.able-consulting.com/MDAC/ADO/Connection/ODBC_DSNLess.htm#ODBCDriverForAccess

Alan :)
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 8

Expert Comment

by:spongie
ID: 9898123
Hi DrD. I think you only need to create the dsn once and that's during installation. So, try creating this with your setup program. I suggest using Inno Setup. It's free.

http://www.jrsoftware.org/isinfo.php

Visit the vb tutorial here:

http://www.jrsoftware.org/iskb.php?vb

or just run this software to create the packaging script:

http://www.randem.com/innoscript/innoscript.htm

and use this tool to create the script for your DSN:

http://www.istool.org/

It might not be the quick and easy way but when you start getting PDW problems, you'll end up using other installation softwares like the ones above. But then you could always find out for yourself...

Good Luck! :0)
0
 

Author Comment

by:DrD
ID: 9898683
Wow!

I sure got some responses to this one.. I'll get back as soon as I can. It will take me a day or so to work through all of them...

Thanks!

David (drD)

0
 

Expert Comment

by:Sycon1
ID: 10313142
I need to know ehat lines of code i need to connect to a microsoft access databse, then how to add records to the databse.thank you
0
 

Expert Comment

by:stromberg45
ID: 11725065
this code works, well it puts up the DSN entry, but it will not select the database at all. that is, it puts up a DSN that points to nothing

my parameters are as follows --

      strDriver = "Microsoft Access Driver (*.mdb)"
      strAttributes = strAttributes & "DESCRIPTION=Temp DSN" & Chr$(0)
      strAttributes = strAttributes & "DSN=RM TEMP" & Chr$(0)
      strAttributes = strAttributes & "DATABASE=" & App.Path & "RM.mdb" & Chr$(0)

      intRet = SQLConfigDataSource(vbAPINull, ODBC_ADD_DSN, strDriver, strAttributes)

anyone help?
0
 

Expert Comment

by:mrjdesign
ID: 13745767
is there one thing missing here???

strAttributes = strAttributes & "DATABASE=" & App.Path & "RM.mdb" & Chr$(0)

shouldnt that be...

strAttributes = strAttributes & "DATABASE=" & App.Path & "\RM.mdb" & Chr$(0)

The backslash isn't added by default to the app.path
0
 

Expert Comment

by:amitmahajan
ID: 14185977
This works -

strAttributes = strAttributes & "Dbq=" & App.Path & "\RM.mdb" & Chr$(0)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

943 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now