database connection in VB.NET COM module works only if Design Environment is present

[amy]

[RESOLVED] database connection in VB.NET COM module works only if Design Environme...

When I run the following code with Visual Studio .NET installed on a PC function dbOpen returns TRUE.

When I run the assembly with just the framework, dbOpen returns FALSE.

I can't figure out why. I install the Visual Studio .NET to step through with the debugger to find out what is wrong, and magically the function works again, no changes.

I HAVE to be able to distribute my Product without the design environment. Does anybody have any ideas of what is going on??

    '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
    Private Declare Function SQLConfigDataSource Lib "odbccp32.dll" _
                  (ByVal hwndParent As Integer, _
                   ByVal fRequest As Integer, _
                   ByVal lpszDriver As String, _
                   ByVal lpszAttributes As String) As Boolean
    Private theDBLocation As String
    Private database As New System.Data.Odbc.OdbcConnection
 
    Public Function dbOpen(ByVal strLocation As String) As Boolean
        On Error GoTo errorhandler
        dbOpen = False
        theDBLocation = strLocation
        Dim strAttributes As String
        Dim fileInfo As IO.FileInfo
        fileInfo = New IO.FileInfo(theDBLocation)
        If (fileInfo.Exists = True) Then
            strAttributes = "DSN=AcromagIPACDefDB" & Chr(0) & _
                             "DBQ=" & theDBLocation & Chr(0)
            SQLConfigDataSource(vbAPINull, ODBC_ADD_DSN, _
                           "Microsoft Access Driver (*.mdb)", _
                           strAttributes)
            strAttributes = "MaxBufferSize=2048;FIL=MS Access;" & _
                            "DSN=AcromagIPACDefDB;" & _
                            "PageTimeout=5;DriverId=25;" & _
                            "DBQ=" & theDBLocation
            database.ConnectionString = strAttributes
            If Not database Is Nothing Then
                database.Open()
                If (database.State = ConnectionState.Open) Then
                    dbOpen = True
                    command.Connection = database
                End If
            End If
        End If
        Exit Function
    errorhandler:
        RaiseEvent ErrorOccurred("dbOpen - " & CStr(Err.Number) & " " & Err.Description)
    End Function

 

[TechGnome]

My thoughts in this order:
1) The DSN doesn't exist.
2) The Access File Doesn't exist
3) WTH is this really trying to do?
4) Seems like a lot of work just to open a simple Access file
5) OLEDBClient would be better than ODBC for using Access databases

-tg

 

[amy]

Thanks for your response...

1) The DSN definitely exists. I checked through Adminstrator Tools->Data Sources (ODBC). The SQLConfigDataSource call is what registers the DSN.

2) The Access file exists. I confirmed that I could open it with MSAccess.

3) I'm trying to pass in the file location of a database. If the file exists, I attempt to add the data source to spare the user the manual steps of Control Panel->Administrator Tools->Data Sources (ODBC), etc. Then I try to open the database.

Other routines in this class fetch data from the database using the database's stored procedures.

4) Possibly I'm doing extra work?? But the database location can change based on which test site a user is operating from. Once I know the database the user needs, I have to verify it exists, and make sure it's DSN exists. A user can run this program from multiple PCs. There's a very good chance it won't be registered as a ODBC data source.

5) ?? I don't know. I'm new to grabbing data from a database. I think I picked ODBC because it was the first thing I could get to work. Don't remember anymore... I have this compiled as a COM class so a VB6 program can use it too. No budget to rewrite the VB6 program in .NET, I want to share the code that accesses the database.

I appreciate your input. If you know a better way to do this, PLEASE enlighten me!


AGAIN, this works fine if the Visual Studio .NET is installed. Doesn't work with just the framework. Seems like I'm missing something that the Install Program is not recognizing as a dependency...

 

[amy]

[resolved]

I needed MDAC_TYP.EXE installed. I thought that it was getting installed with my Setup distibution program with the pluginInstaller.msi I downloaded from Microsoft. Oops, my bad.